SQL Server Trigger On Delete To Single Table

Question

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

Answer 1

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