Posted by Rayne on 03/01/07 15:05

On Jan 3, 4:38 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Rico (y...@me.com) writes:
> > I have a table that has a UserUpdatedfieldwhich has adefaultvalueof
> > SUSER_SNAME(). This works great when the record is created, but I'd
> > like to alsoupdatethisvaluewhen the record is changed. If I
> > understand things correctly, I'll need a trigger to do this, but I've
> > never created a trigger before. Is this an easy thing to accomplish?
>
> CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATEASUPDATEtbl
> SET moduser = SYSTEM_USER
> FROM tbl t
> JOIN inserted i ON t.pkcol = i.pkcol
>
> SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)
>
> The table "inserted" is a virtual tables that holds the rows that
> were inserted or the after-image of the updated rows. There is also
> a table "deleted" which holds deleted rows and the before-image of
> updated rows.
>
> A trigger fires once per statement, so there can be many rows in these
> tables.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I've come across this same issue...so I basically have to create a
trigger for each table in my database to update the username/modified
fields on each update. Is there a way to create a global update
trigger that will fire when any table with those fields is updated?

Thanks.
Rayne

[Back to original message]