SQL Server Performance

create trigger on all tables for insert,update,delete

Discussion in 'SQL Server 2005 General DBA Questions' started by avipenina, Jul 13, 2008.

  1. avipenina New Member

    Hi,
    can i create a trigger on all tables for insert,update,delete that will write to an audit table what is the values in the column before the update and after the update?
    if so how it can be done?
    THX
  2. moh_hassan20 New Member

    you can create trigger per table for insert,update,delete events and save information to audit table(s) you create.
    for details:
    http://msdn.microsoft.com/en-us/library/ms189799.aspx
    http://www.sql-server-performance.com/articles/dev/triggers_2000_p1.aspx
  3. avipenina New Member

    Thx.
    but what about the insert into the audit table the old value and the new updated or deleted or inserted values?
  4. moh_hassan20 New Member

    [quote user="avipenina"]
    Thx.
    but what about the insert into the audit table the old value and the new updated or deleted or inserted values?
    [/quote]
    There are special tables for trigger that can be accessed from within triggers:
    deleted : store old values
    inserted : store new values

    here is a simple example for update trigger


    create audit table

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[audit_contact](
    [fname_old] [nchar](50) NULL,
    [fname_new] [nchar](50) NULL,
    [userName] [nchar](10) NULL,
    [modified_date] [datetime] NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
    CONSTRAINT [PK_audit_contact] 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]



    create update trigger
    CREATE TRIGGER trig_update_contact
    ON Person.Contact
    FOR UPDATE
    AS

    DECLARE @oldFname VARCHAR(50)
    DECLARE @NewFname VARCHAR(50)

    IF UPDATE(FirstName)
    begin
    SELECT @oldFName = (SELECT FirstName FROM Deleted)
    SELECT @newFName = (SELECT FirstName FROM Inserted)

    -- store your values in your audit table

    insert into dbo.audit_contact (fname_old , fname_new,userName, modified_date ) values (@oldFName ,@newFName,User_Name(),GetDate())
    end


    You need to track every field using update() function ,if you care its value
    Also, Columns_updated() returns a varbinary bitmask showing which columns were updated. (but need bitmask processing)

Share This Page