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.
Hendrick
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
>
|||Great. Thanks for the prompt reply.
Hendrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 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...
want[vbcol=seagreen]
row.[vbcol=seagreen]
be
>
|||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...
> want
> row.
> be
>
|||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...
>
|||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/techinf...000/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...
>