Post reply

Default constraints OR INSERT trigger for Audit columns (create date time/user)

  • March 8, 2016 at 5:30 pm

    #308917

    Which is the best practice to implement in audit columns for create datetime and create user, INSERT trigger or default constraints?

    Users are still able to insert an invalid date/user in the Audit columns with default constraints. What is the best way to avoid this.

    Thanks!

  • March 8, 2016 at 10:29 pm

    #1863911

    SQL!$@w$0ME (3/8/2016)

    Which is the best practice to implement in audit columns for create datetime and create user, INSERT trigger or default constraints?

    Thanks!

    The default constraint will only mark the date/time of the initial insert, a trigger can update the value whenever it is altered. Suggest you use both.

    😎

  • March 9, 2016 at 1:50 am

    #1863926

    Defaults. When you're creating a row and a column should go to a particular value, use defaults. If you need to set a column to a particular value when the row is updated, use triggers.

    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

  • March 9, 2016 at 5:43 am

    #1863966

    Thanks a lot Gail.

  • March 9, 2016 at 8:46 am

    #1864016

    Users are still able to insert an invalid date/user in the Audit columns with default constraints. What is the best way to avoid this.

    Thanks!

  • March 9, 2016 at 8:59 am

    #1864023

    SQL!$@w$0ME (3/9/2016)

    Users are still able to insert an invalid date/user in the Audit columns with default constraints. What is the best way to avoid this.

    Thanks!

    Trigger

    😎

  • March 9, 2016 at 9:28 am

    #1864042

    When I have an insert and update trigger on the same table, the Insert trigger is triggering the Update trigger to execute since I use UPDATE statement in the INSERT trigger. So both insert and update columns are updating one after the other.

    How to overcome this.

    Insert Trigger:

    ......

    UPDATE dbo.table1 SET CREATE_TIME =GETDATE(), CREATE_USER=SUSER_NAME()

    .....

  • March 9, 2016 at 9:36 am

    #1864048

    SQL!$@w$0ME (3/9/2016)

    When I have an insert and update trigger on the same table, the Insert trigger is triggering the Update trigger to execute since I use UPDATE statement in the INSERT trigger. So both insert and update columns are updating one after the other.

    How to overcome this.

    Insert Trigger:

    ......

    UPDATE dbo.table1 SET CREATE_TIME =GETDATE(), CREATE_USER=SUSER_NAME()

    .....

    Can you post the DDL for the table?

    😎

  • March 9, 2016 at 10:49 am

    #1864069

    Thanks. Here's the DDL. I would also like to deny future updates on the [CreateDate] and [CreateUser] fields. How is it possible.

    /****** Object: Table [dbo].[Test1] Script Date: 3/9/2016 12:11:39 PM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Test1](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Name] [varchar](35) NULL,

    [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Test1_CreateDate] DEFAULT (getdate()),

    [CreateUser] [varchar](128) NOT NULL CONSTRAINT [DF_Test1_CreateUser] DEFAULT (suser_sname()),

    [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Test1_UpdateDate] DEFAULT (getdate()),

    [UpdateUser] [varchar](128) NOT NULL CONSTRAINT [DF_Test1_UpdateUser] DEFAULT (suser_sname()),

    CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED

    (

    [ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    /****** Object: Trigger [dbo].[TR_Test1_Update] Script Date: 3/9/2016 12:06:40 PM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [dbo].[TR_Test1_Update]

    ON [dbo].[Test1]

    AFTER UPDATE

    AS

    BEGIN

    UPDATE [dbo].[Test1]

    SET

    UpdateDate=getdate(),

    UpdateUser=suser_sname()

    FROM dbo.Test1 a

    INNER JOIN Inserted i

    ON

    a.ID = i.ID

    END

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /****** Object: Trigger [dbo].[TR_Test1_Insert] Script Date: 3/9/2016 12:06:40 PM ******/

    CREATE TRIGGER [dbo].[TR_Test1_Insert]

    ON [dbo].[Test1]

    AFTER INSERT

    AS

    BEGIN

    UPDATE [dbo].[Test1]

    SET

    CreateDate=getdate(),

    CreateUser=suser_sname()

    FROM dbo.Test1 a

    INNER JOIN Inserted i

    ON

    a.ID = i.ID

    END

    GO

  • March 9, 2016 at 12:35 pm

    #1864089

    For denying updates on column, you can use the DENY statement to set column-level permissions. Check Books Online for the syntax. This might even remove the need for an INSERT trigger (but test it, I never tried this).

    To understand the triggers issue, you need to know about two options:

    * The server-level configuration option "nested triggers" specifies whether a modification from one trigger can fire another trigger. Default is on.

    * The database-level option "RECURSIVE_TRIGGERS" specifies whether a trigger that modifies its own table fires itself. It only prevents direct recursion, not indirect recursion. So when this option is off (the default) an AFTER UPDATE trigger on table Tab1 will not fire if the trigger itself updates Tab1, but it is still possible that the trigger updates Tab2, which invokes another trigger that updates Tab1 again, and then the trigger will fire recusively.

    In your case, the insert trigger and update triggers are the same trigger. The update trigger does not invoke itself because of the database option RECURSIVE_TRIGGERS. But the nested triggers have not been disables, so the insert trigger will invoke that other trigger when it updates the table.

    One possible solution (if you still need the after insert trigger after revoking permissions) is to have a single trigger for both inserts and updates, though I don't really like that.

    Another possible solution is to add a check on UPDATED(column_name) in the Update trigger to prevent it from doing anything at all if the only columns updated are the ones that the insert trigger sets.

    However, if it is so important to keep the audit information safe, why not go the extra mile and create a separate table for the audit information. That allows you to store even more information (old and new data, for instance), to keep a history of all updates instead of only the last, and to track deletes as well. And having the information in a separate table, potentially even a separate database, makes it much easier to prevent tampering.

    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/

  • March 9, 2016 at 1:29 pm

    #1864103

    Thanks Hugo.

    Can I add deny update on the audit create columns in the existing UPDATE trigger.

    I cant do any inserts with the INSERT trigger in place and the UPDATE trigger mentioned below.

    Can you please verify the code and advise for any changes.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER TRIGGER [dbo].[TR_Test1_Update]

    ON [dbo].[Test1]

    AFTER UPDATE

    AS

    BEGIN

    UPDATE [dbo].[Test1]

    SET

    UpdateDate=getdate(),

    UpdateUser=suser_sname()

    FROM dbo.Test1 a

    INNER JOIN Inserted i

    ON

    a.ID = i.ID

    END

    IF UPDATE(CreateUser) OR UPDATE(CreateDate)

    BEGIN

    --select * from inserted;

    --select * from deleted;

    ROLLBACK TRANSACTION

    RAISERROR('Changes to Audit columns(createuser,createdate) are not allowed', 16, 1);

    END

  • March 9, 2016 at 1:35 pm

    #1864104

    Why are your users even seeing the audit columns? They should not have the ability to insert or update these values. They should be hidden from the users. And I am not talking about denying them permissions to insert or update the columns. The insert and update statements coming from the user should not even include these columns. A default constraint can ensure the entry of the correct information during an insert and an update trigger can do the same for an update.

  • March 9, 2016 at 2:00 pm

    #1864109

    SQL!$@w$0ME (3/9/2016)

    Thanks Hugo.

    Can I add deny update on the audit create columns in the existing UPDATE trigger.

    Yes, you can. But that will also prevent updating those columns from the insert trigger.

    I cant do any inserts with the INSERT trigger in place and the UPDATE trigger mentioned below.

    Exactly.

    Can you please verify the code and advise for any changes.

    From a quick viausl inspection it appears to do what it should do, except that the check for changes to the create audit columns is too aggressive.

    Obviously you should do your own testing.

    As for my suggestions, please re-read my previous post.

    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/

  • March 9, 2016 at 6:44 pm

    #1864162

    Thanks Lynn!

  • March 9, 2016 at 6:45 pm

    #1864163

    Thanks Hugo!

Viewing 15 posts - 1 through 15 (of 29 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy