Newbie: Updating record with User information

#1

Hello,

I have a table that has a UserUpdated field which has a default value of
SUSER_SNAME(). This works great when the record is created, but I'd like to
also update this value when 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?

Thanks!

Rick

#2

Rico (you (AT) me (DOT) com) writes:

Quote:

I have a table that has a UserUpdated field which has a default value of
SUSER_SNAME(). This works great when the record is created, but I'd
like to also update this value when 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,UPDATE AS
UPDATE tbl
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, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#3

That's excellent! Thank you very much for your help Erland, that was
exactly what I needed! (that will make coding in VB much easier.

Rick

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Quote:

Rico (you (AT) me (DOT) com) writes:
I have a table that has a UserUpdated field which has a default value of
SUSER_SNAME(). This works great when the record is created, but I'd
like to also update this value when 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,UPDATE AS
UPDATE tbl
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, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#4

On Jan 3, 4:38 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Rico (y... (AT) me (DOT) 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... (AT) sommarskog (DOT) 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

#5

Rayne (wifetalks (AT) gmail (DOT) com) writes:

Quote:

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?

No, but you could write a program to generate them.

You could also consider third-party tools ApexSql (www.apexsql.com)
has SQL Audit, for instance. I have not tried it myself.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Newbie: Updating record with User information

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Newbie: Updating record with User information - 01-03-2007 , 04:16 PM

Re: Newbie: Updating record with User information - 01-03-2007 , 04:38 PM

Re: Newbie: Updating record with User information - 01-03-2007 , 05:25 PM

Re: Newbie: Updating record with User information - 03-01-2007 , 09:05 AM

Re: Newbie: Updating record with User information - 03-01-2007 , 04:08 PM