CODEWITHSUNDEEP
sql-servertriggersaudit
DaRoGa
DaRoGa

Reputation: 2404

SQL Server Trigger On Delete To Single Table

I want to create a trigger on each table in my database that will log a copy of deleted data into a single 'Audit' table that i can then reference.

I have had a look around but cannot seem to find a way to insert it all into a single table due to differing column definitions.

Any help is greatly appreciated.

Thanks

Upvotes: 0

Views: 102

Answers (2)

cloudsafe
cloudsafe

Reputation: 2504

Create an Audit table:

CREATE TABLE [dbo].[MyAudit](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [TableName] [nvarchar](100) NOT NULL,
    [ColumnName] [nvarchar](100) NOT NULL,
    [DeletedValue] [sql_variant] NULL,
    [ChangeDateTime] [datetime] NOT NULL,
)
GO
ALTER TABLE [dbo].[MyAudit] ADD  CONSTRAINT [DF_MyAudit_ChangeDateTime]  DEFAULT (getdate()) FOR [ChangeDateTime]

Add Trigger to each table you want to log deletions on:

CREATE TRIGGER [MyTrigger] 
   ON  [MyTable]
   AFTER DELETE
AS 
BEGIN
    SET NOCOUNT ON;
    Insert into [MyAudit] ([Tablename], [ColumnName], [DeletedValue])
        Select 'MyTable', 'Column1', deleted.Column1 from deleted
    Insert into [MyAudit] ([Tablename], [ColumnName], [DeletedValue])
        Select 'MyTable', 'Column2', deleted.Column2 from deleted
--Do all columns until column x
    Insert into [MyAudit] ([Tablename], [ColumnName], [DeletedValue])
        Select 'MyTable', 'ColumnX', deleted.ColumnX from deleted
END

Upvotes: 1

Yashveer Singh
Yashveer Singh

Reputation: 1977

This is a sample code . what i am doing here is on very update which a user does on table I am getting the new and old values from Deleted and Inserted table which sql maintain when we update the table .For you case of delete I think you can get it from deleted . 

  ALTER TRIGGER [SIR].[TESTTABLE_UPDATE]
        ON [DBO].[TESTTABLE]
  AFTER UPDATE
  AS
  BEGIN
         IF (@@ROWCOUNT  = 0)  return SET NOCOUNT ON; DECLARE @USER VARCHAR(1000)  
         -- this is a function which can gives u current loggined user in case if someone update the data from backend we can get that user name and log it.
         SET @USER='' SET @USER= (select dbo.GetCurrentUserName())  

         --this is a filter for bulk update which systemm does and you dont care about it so return 
         if( PATINDEX('%workerprocess%',@USER)>0) return 

         DECLARE  @ID INT , @LOGS VARCHAR(MAX)  , @FINALLOGS VARCHAR(MAX)      DECLARE @ACTION VARCHAR(MAX)       
         SET @ACTION =''       SET @LOGS=''      SET @FINALLOGS =''  
         SELECT  @LOGS = DELETED.CHANGELOG FROM DELETED
    -- individual column that a table contain 
    IF UPDATE([STATECODE])
        BEGIN
            SET @Action += '|  StateCode Changed from '+convert(varchar, ( select isnull( [STATECODE],'') FROM deleted   )) +'  to  '+ convert(varchar, ( select isnull( [STATECODE],'') FROM inserted  ))  +' | '
        END
   -- individual column that a table contain 
    IF UPDATE([FACILITYCODE])
        BEGIN
            SET @Action += '|  FacilityCode Changed from '+convert(varchar, ( select isnull( [FACILITYCODE],'') FROM deleted   )) +'  to  '+ convert(varchar, ( select isnull( [FACILITYCODE],'') FROM inserted  ))  +' | '
        END

    IF UPDATE([INSTALLATIONCODE])
        BEGIN
            SET @Action += '|  InstallationCode Changed from '+convert(varchar, ( select isnull( [INSTALLATIONCODE],'') FROM deleted   )) +'  to  '+ convert(varchar, ( select isnull( [INSTALLATIONCODE],'') FROM inserted  ))  +' | '
        END


             set @FINALLOGS =' [SIR.INSTALLATIONS Updated  On ' +  CONVERT(varchar(25), GETDATE()) + @ACTION + ISNULL( @LOGS,'') +' ] '



             -- this is my audit table where I am logging everything.
             INSERT INTO SIR.UserChangeLog (UpdatedBy,ChangeLog)  VALUES(@user, @FINALLOGS)
  END
 ---- End  tirgger   ---------

Upvotes: 1

Related Questions