Newbie : How to make Trigger for inserting/updating fields?

Hi All,
I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update from
application, however since I new to SQLServer (used to use Access) I want to
insert/update these fields via trigger, the ModifyID should contain the user
name logged on to the SQLServer and the time he modify or insert the row.
Could this two insert/update containing in one trigger? Any help would be
highly appreciated. TIA.
HendrickCREATE TRIGGER YourTriggerName
ON YourTable
FOR INSERT, UPDATE
AS
BEGIN
UPDATE YourTable
SET
UpdateTime = GETDATE(),
UpdateID = USER_ID()
WHERE YourTable.PK IN
(
SELECT PK
FROM INSERTED
)
END
... Replace 'PK' with the primary key column(s) from your table.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> from
> application, however since I new to SQLServer (used to use Access) I want
> to
> insert/update these fields via trigger, the ModifyID should contain the
> user
> name logged on to the SQLServer and the time he modify or insert the row.
> Could this two insert/update containing in one trigger? Any help would be
> highly appreciated. TIA.
> Hendrick
>|||Great. Thanks for the prompt reply.
Hendrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> CREATE TRIGGER YourTriggerName
> ON YourTable
> FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE YourTable
> SET
> UpdateTime = GETDATE(),
> UpdateID = USER_ID()
> WHERE YourTable.PK IN
> (
> SELECT PK
> FROM INSERTED
> )
> END
>
> ... Replace 'PK' with the primary key column(s) from your table.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> > Hi All,
> >
> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> > from
> > application, however since I new to SQLServer (used to use Access) I
want
> > to
> > insert/update these fields via trigger, the ModifyID should contain the
> > user
> > name logged on to the SQLServer and the time he modify or insert the
row.
> > Could this two insert/update containing in one trigger? Any help would
be
> > highly appreciated. TIA.
> >
> > Hendrick
> >
> >
>|||Hendrick,
Also see 'Multirow Considerations' in the SQL Server Books Online.
HTH
Jerry
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Great. Thanks for the prompt reply.
> Hendrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
> row.
>> > Could this two insert/update containing in one trigger? Any help would
> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>|||Hey Jerry,
Did a search on Multirow Considerations in my book online, didn't get
anything back.
(SQL SERVER 2000 sp4). Do you have a more specific indication of what we
should be aware of here? Always wanna learn more ;-)
Thanks,
Bob
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Hendrick,
> Also see 'Multirow Considerations' in the SQL Server Books Online.
> HTH
> Jerry
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>> Great. Thanks for the prompt reply.
>> Hendrick
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I
>> > update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
>> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain
>> > the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
>> row.
>> > Could this two insert/update containing in one trigger? Any help would
>> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>>
>|||Bob,
Here is the 2005 link http://msdn2.microsoft.com/en-us/library/ms250511.
Are you using the latest SQL Server Books Online? Here is the link to
upgrade if not:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
HTH
Jerry
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:eWEpoEezFHA.3588@.tk2msftngp13.phx.gbl...
> Hey Jerry,
> Did a search on Multirow Considerations in my book online, didn't get
> anything back.
> (SQL SERVER 2000 sp4). Do you have a more specific indication of what we
> should be aware of here? Always wanna learn more ;-)
> Thanks,
> Bob
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
>> Hendrick,
>> Also see 'Multirow Considerations' in the SQL Server Books Online.
>> HTH
>> Jerry
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>> Great. Thanks for the prompt reply.
>> Hendrick
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I
>> > update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
>> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain
>> > the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
>> row.
>> > Could this two insert/update containing in one trigger? Any help
>> > would
>> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>>
>>
>