Modified User

Question

Post reply

Modified User

Paul_Harvey

Hall of Fame

Points: 3822
More actions
January 20, 2010 at 6:35 am

#140719

I want to track whenever a change is made to a record, or a new record inserted.
I have the following trigger for the Created record:
ALTER TRIGGER [dbo].[trAddressCreateDate] ON [dbo].[Address]
FOR INSERT
AS
UPDATE Address SET Created=getdate()
FROM Address INNER JOIN Inserted ON Address.id= Inserted.id
UPDATE Address SET Created_by=user
FROM Address INNER JOIN Inserted ON Address.id= Inserted.id
and more or less the same for an updated record.
The problem I have is that the Modified and Created user is always shown as "dbo". I would prefer it to show the SQL user being used to make the change.
Can anyone help with this?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

Well, just to answer your query, one of the way could be this...

Select SYSTEM_USER.

But I dont think trigger is the right way of handling it? You can just set the default values for these two columns in the table?

---------------------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Nabha (1/20/2010)
But I dont think trigger is the right way of handling it? You can just set the default values for these two columns in the table?

Defaults only apply on inserts. They do not get changed when a row is updated. For that, you need a trigger.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

No need for two updates or two triggers. That's just doubling the work SQL has to do.

ALTER TRIGGER [dbo].[trAddressCreateDate] ON [dbo].[Address]

FOR INSERT, UPDATE

AS

UPDATE Address

SET Created=getdate(),

Created_by= Original_Login()

FROM Address INNER JOIN Inserted ON Address.id= Inserted.id

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

Thanks Gail, I just looked at the trigger code and it was just there for 'insert' and thought default would do. Your code above should do well for OP in that case.

---------------------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Nabha (1/20/2010)
Thanks Gail, I just looked at the trigger code and it was just there for 'insert' and thought default would do.

If it was just insert, it would, but the OP said "I want to track whenever a change is made to a record, or a new record inserted."

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 6

Thanks once again for the helpful responses. I will test this tomorrow.

I think I need two triggers (please correct me if I am wrong) becuase I would like the Created_By field and the Modified_By field to be maintained.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Paul_Harvey (1/20/2010)
I think I need two triggers (please correct me if I am wrong) becuase I would like the Created_By field and the Modified_By field to be maintained.

No, in that case I'd go for a default on the created by and created date columns, that will handle insert, and then just an update trigger to set modified by and modified date

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 8

Hi all,

Apologies for the delay in my feedback.

I revisited this today, and using the default value for the "create", and trigger for the "modified" this is now working perfectly.

Thanks again!

:-P:-P

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Glad to hear.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass