January 20, 2016 at 4:48 pm
Hi Guys,
I would like to create triggers for the following items in a table for auditing purposes. Could you please provide script for this.
Table name: [Users]
Audit Column names:
=============
Aud_Create_User (Use SYSTEM USER)
Aud_Create_DateTime (Use create datetime)
Aud_Update_User (Use SYSTEM USER)
Aud_Update_DateTime (Use update datetime)
Do I need to use trigger for [Aud_Create_DateTime] field or can we use constraint to use getdate() ?
Many thanks in advance!
January 20, 2016 at 9:40 pm
set quoted_identifier off
go
select "here is how you write code" + name + "
go"
from sys.objects
where type = 'U'
--I use quoted identifier off so I can use single ticks in my code-writing-code to do things like dates, strings, etc. without having to futz with how many ticks I need! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 21, 2016 at 1:34 pm
For the timestamp and user of creation, you can use default constraints.
For the timestamp and user of last update, you have to use an ON UPDATE trigger. Use the inserted and/or deleted pseudo-table to find affected rows, and remember that a trigger fires once per execution regardless of the number of affected rows. An update on the table from the inside of the trigger will not recursively fire the same trigger (under default settings).
January 21, 2016 at 2:22 pm
Thanks!
January 22, 2016 at 8:36 am
When I tried to log [system_user/suser_sname() - current user] to audit column(datatype - sysname) using trigger, I get the error 'String or Binary data would be truncated'. I used other datatypes for this column like nvarchar(MAX). Still getting the same error. Any thoughts.
January 22, 2016 at 8:41 am
Somewhere you're trying to insert into a column that's too small.
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
January 22, 2016 at 8:52 am
I used nvarchar(MAX) / sysname datatypes to update audit column [user_updated] - which uses suser_sname() values. Still get this error.
[User_Updated] Column - [nvarchar](max) NULL CONSTRAINT [DF_Employee_Audit_ModifyUser] DEFAULT (suser_sname()),
CREATE TRIGGER [dbo].[TR_Modify_Date]
ON [dbo].[Employee]
FOR UPDATE
AS
BEGIN
Update Employee
Set User_Updated=SUSER_SNAME()
Where ID In (Select ID from inserted)
END
January 22, 2016 at 8:55 am
Look at the length of each of the character columns, and the length of each field you're inserting into. One is too short, you need to investigate and see which one.
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
January 22, 2016 at 9:42 am
Thanks Gail. I used VARCHAR(n) and it works.
January 29, 2016 at 6:29 pm
Can I create trigger & Constraints like this? Please verify the script.
Table name: [Users]
Audit Column names:
-----------------------
Aud_Create_User (Use SYSTEM USER)
Aud_Create_DateTime (Use create datetime)
Aud_Update_User (Use SYSTEM USER)
Aud_Update_DateTime (Use update datetime)
--Trigger
CREATE TRIGGER [dbo].[TR_Aud_UpdateDatetime_UpdateUser]
ON [dbo].[Users]
FOR UPDATE
AS
BEGIN
UPDATE Users
SET Aud_Update_Datetime=GETDATE(), Aud_Update_User=SUSER_SNAME()
WHERE User_ID IN (SELECT User_ID FROM INSERTED)
END
GO
--Default Constraints
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Claim_Aud_Create_User] DEFAULT (suser_sname()) FOR [Aud_Create_User]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Claim_Aud_Create_Datetime] DEFAULT (getdate()) FOR [Aud_Create_Datetime]
GO
January 30, 2016 at 3:43 am
If the column User_ID is constrained to be unique and not nullable, then the trigger should work. You should still test, of course.
There are some modifications I would make if it were my code. First, I always start every trigger with the following lines of code (directly after AS):
-- Do not put anything before these lines!
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM inserted) RETURN; -- Change to deleted for ON / AFTER DELETE trigger
-- Rest of the code follows after this line
Second, I never use IN (or NOT IN) with a subquery. Every [NOT] IN with subquery can always be rewritten as a [NOT] EXISTS with subquery, and EXISTS has a few benefits over IN: it also works when working with composite keys, and it does not have the (for many) unexpected treatment of NULL values that [NOT] IN have.
In this specific case IN and EXISTS are equivalent so you could leave it at that, but I want my code to be consistent - hence the choice to never use IN with subquery. My WHERE clause would be:
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.User_ID = Users.User_ID);
And the final remarks are that you should always schema-qualify all object names. So use dbo.Users instead of just Users in the UPDATE statement, and that you should use the ANSI-compliant CURRENT_TIMESTAMP instead of the proprieatary GETDATE() function.
January 30, 2016 at 6:40 am
Thanks a lot Hugo. Could you please provide a full script for the trigger with your recommended changes?
Sorry, I'm not good with scripting!
January 30, 2016 at 6:50 am
Sure, if you post your current version of the script I'll help you make the changes.
January 30, 2016 at 7:15 am
SQL!$@w$0ME (1/30/2016)
Sorry, I'm not good with scripting!
Why don't you take this as an opportunity to get better? Hugo explained what's needed, and I'm sure he'll fix errors if you try and can't get it right.
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
January 30, 2016 at 7:42 am
Please validate the updated script.
--User_ID is PK Identity
CREATE TRIGGER [dbo].[TR_Aud_Update_Datetime_Update_User]
ON [dbo].[Users]
FOR UPDATE
AS
IF @@ROWCOUNT = 0
RETURN;
IF NOT EXISTS (SELECT * FROM inserted) RETURN;
SET NOCOUNT ON;
BEGIN
UPDATE dbo.Users
SET Aud_Update_Datetime=CURRENT_TIMESTAMP, Aud_Update_User=SUSER_SNAME()
WHERE User_ID EXISTS (SELECT User_ID FROM INSERTED)
END
GO
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply