Script to create Triggers

  • 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!

  • 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

  • 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).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Thanks!

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail. I used VARCHAR(n) and it works.

  • 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

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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!

  • Sure, if you post your current version of the script I'll help you make the changes.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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