Trigger capture of modifications to a table
-
I need to write only the changes made to a table row to a transactions log file. I have found many examples using triggers to send a message a need to capture the information on a table.
-
You could use the COLUMNS_UPDATED in an after update trigger
Peter Evans (__PETER Peter_)
E. Use COLUMNS_UPDATED
This example creates two tables: an employeeData table and an auditEmployeeData table. The employeeData table, which holds sensitive employee payroll information, can be modified by members of the human resources department. If the employee's social security number (SSN), yearly salary, or bank account number is changed, an audit record is generated and inserted into the auditEmployeeData audit table.
By using the COLUMNS_UPDATED() function, it is possible to test quickly for any changes to these columns that contain sensitive employee information. This use of COLUMNS_UPDATED() only works if you are trying to detect changes to the first 8 columns in the table.
USE pubsIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'employeeData')DROP TABLE employeeDataIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'auditEmployeeData')DROP TABLE auditEmployeeDataGOCREATE TABLE employeeData (emp_id int NOT NULL,emp_bankAccountNumber char (10) NOT NULL,emp_salary int NOT NULL,emp_SSN char (11) NOT NULL,emp_lname nchar (32) NOT NULL,emp_fname nchar (32) NOT NULL,emp_manager int NOT NULL)GOCREATE TABLE auditEmployeeData (audit_log_id uniqueidentifier DEFAULT NEWID(),audit_log_type char (3) NOT NULL,audit_emp_id int NOT NULL,audit_emp_bankAccountNumber char (10) NULL,audit_emp_salary int NULL,audit_emp_SSN char (11) NULL,audit_user sysname DEFAULT SUSER_SNAME(),audit_changed datetime DEFAULT GETDATE())GOCREATE TRIGGER updEmployeeDataON employeeDataFOR update AS/*Check whether columns 2, 3 or 4 has been updated. If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/IF (COLUMNS_UPDATED() & 14) > 0/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/BEGIN-- Audit OLD record.INSERT INTO auditEmployeeData(audit_log_type,audit_emp_id,audit_emp_bankAccountNumber,audit_emp_salary,audit_emp_SSN)SELECT 'OLD',del.emp_id,del.emp_bankAccountNumber,del.emp_salary,del.emp_SSNFROM deleted del-- Audit NEW record.INSERT INTO auditEmployeeData(audit_log_type,audit_emp_id,audit_emp_bankAccountNumber,audit_emp_salary,audit_emp_SSN)SELECT 'NEW',ins.emp_id,ins.emp_bankAccountNumber,ins.emp_salary,ins.emp_SSNFROM inserted insENDGO/*Inserting a new employee does not cause the UPDATE trigger to fire.*/INSERT INTO employeeDataVALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)GO/*Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced.*/UPDATE employeeDataSET emp_salary = 51000WHERE emp_id = 101GOSELECT * FROM auditEmployeeDataGO/*Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.*/UPDATE employeeDataSET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'WHERE emp_id = 101GOSELECT * FROM auditEmployeeDataGO -
This took me a while to do the first time but it works.
Too bad SQL doesn't do this for us. I am not into all the bitmatching and hexing data.
Anyway hope it helps. I usually paint screens that fill in the current user stuff in the fields before the data update, hence before the trigger fires.
The limit I imposed is only one row changed at a time. You can lift that restriction.
Good luck, if you have some feedback for improvement I am all ears.
----------------------------------------------------
--- TABLE THAT WILL BE MONITORED
----------------------------------------------------
CREATE TABLE [T_OFFICE] (
[OFC_ID] [tinyint] NOT NULL ,
[OFC_SHTNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OFC_LNGNAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OFC_GLPREFIX] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LAST_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__T_OFFICE__LAST_U__2077C861] DEFAULT (host_name() COLLATE SQL_Latin1_General_CP1_CI_AS),
[LAST_DATE] [datetime] NOT NULL CONSTRAINT [DF__T_OFFICE__LAST_D__216BEC9A] DEFAULT (getdate()),
[LAST_ACTION] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__T_OFFICE__LAST_A__226010D3] DEFAULT ('INSERT NEW' COLLATE SQL_Latin1_General_CP1_CI_AS),
CONSTRAINT [PK_T_OFFICE] PRIMARY KEY CLUSTERED
(
[OFC_ID]
 
) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]
GO
----------------------------------------------------
--- TABLE THAT TRACK CHANGES OF ONLY T_OFFICE
----------------------------------------------------
CREATE TABLE [L_OFFICE] (
[LOG_ID] [int] IDENTITY (1, 1) NOT NULL ,
[LOG_ITEM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LOG_STAMP] [datetime] NULL CONSTRAINT [DF__L_OFFICE__LOG_ST__24485945] DEFAULT (getdate()),
[LOG_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__L_OFFICE__LOG_US__253C7D7E] DEFAULT (user_name() COLLATE SQL_Latin1_General_CP1_CI_AS),
[LOG_APPNAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__L_OFFICE__LOG_AP__2630A1B7] DEFAULT (app_name() COLLATE SQL_Latin1_General_CP1_CI_AS),
[LOG_HOSTID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__L_OFFICE__LOG_HO__2724C5F0] DEFAULT (host_id() COLLATE SQL_Latin1_General_CP1_CI_AS),
[LOG_HOSTNAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__L_OFFICE__LOG_HO__2818EA29] DEFAULT (host_name() COLLATE SQL_Latin1_General_CP1_CI_AS),
[OFC_ID] [tinyint] NOT NULL ,
[OFC_SHTNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OFC_LNGNAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OFC_GLPREFIX] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LAST_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LAST_DATE] [datetime] NULL ,
[LAST_ACTION] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_L_OFFICE] PRIMARY KEY CLUSTERED
(
[LOG_ID]
 
) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]
GO
----------------------------------------------------
--- TRIGGER THAT LAYS DOWN GOURGEOUS INFORMATION
----------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
------------------------------------------------------------------------------
-- TRIGGERS FOR AUDITING ALL INSERT UPDATE DELETE ON TABLE
------------------------------------------------------------------------------
CREATE TRIGGER TRIG_T_OFFICE_AUDIT
ON T_OFFICE
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @TRIG_ROWCT INT, @TRIG_INSERTED INT, @TRIG_DELETED INT, @TRIG_ACTION VARCHAR(20), @VAR_SQL VARCHAR(1000)
SET @TRIG_ROWCT = @@ROWCOUNT
SET NOCOUNT ON
IF (@TRIG_ROWCT = 0)
BEGIN
RETURN --NOTHING TO DO SINCE NO ROWS CHANGED
END
IF (@TRIG_ROWCT > 1)
BEGIN --STOP THE PROCESSING AND RETURN TO CALLER
PRINT 'TOO MANY ROWS INSERTED/REMOVED'
RAISERROR('ONLY ALLOWED TO ADD ONE ROW AT A TIME',16,10)
ROLLBACK TRAN
RETURN
END
IF (EXISTS(SELECT * FROM DELETED))
BEGIN
SELECT * INTO #TEMP_DELETED FROM DELETED
SET @TRIG_DELETED = @@ROWCOUNT
END
IF (EXISTS(SELECT * FROM INSERTED))
BEGIN
SELECT * INTO #TEMP_INSERTED FROM INSERTED
SET @TRIG_INSERTED = @@ROWCOUNT
END
IF (@TRIG_INSERTED=0 AND @TRIG_DELETED=0)
BEGIN
SET @TRIG_ACTION = 'ACTION_NOTHING'
PRINT 'NO ROWS IN INSERTED AND OR DELETED'
RAISERROR('MUST HAVE INSERTED OR DELETED ROWS TO FINISH',16,10)
ROLLBACK TRAN
RETURN
END
SET @TRIG_INSERTED = COALESCE(@TRIG_INSERTED,0)
SET @TRIG_DELETED = COALESCE(@TRIG_DELETED,0)
IF (@TRIG_INSERTED>0 AND @TRIG_DELETED>0)
BEGIN
SET @TRIG_ACTION = 'ACTION_UPDATE'
END
IF (@TRIG_INSERTED>0 AND @TRIG_DELETED=0)
BEGIN
SET @TRIG_ACTION = 'INSERTED'
END
IF (@TRIG_INSERTED=0 AND @TRIG_DELETED>0)
BEGIN
SET @TRIG_ACTION = 'DELETED'
END
IF (@TRIG_INSERTED>0 AND @TRIG_DELETED>0)
BEGIN
SET @TRIG_ACTION = 'UPDATED'
END
--DECLARE @TEMP_INS VARCHAR(100) SET @TEMP_INS=COALESCE(CONVERT(VARCHAR(10),@TRIG_INSERTED),'NOTHING')
--DECLARE @TEMP_DEL VARCHAR(100) SET @TEMP_DEL=COALESCE(CONVERT(VARCHAR(10),@TRIG_DELETED),'NOTHING')
--DECLARE @TEMP_ACT VARCHAR(100) SET @TEMP_ACT=COALESCE(CONVERT(VARCHAR(10),@TRIG_ACTION),'NOTHING')
--PRINT '@TRIG_INSERTED is ' + @TEMP_INS
--PRINT '@TRIG_DELETED is ' + @TEMP_DEL
--PRINT '@TRIG_ACTION is ' + @TEMP_ACT
--RETURN
IF (@TRIG_ACTION = 'INSERTED' OR @TRIG_ACTION = 'DELETED')
BEGIN
SET @VAR_SQL ='INSERT INTO L_OFFICE ' +
'(LOG_ITEM, ' +
' OFC_ID, ' +
' OFC_SHTNAME, ' +
' OFC_LNGNAME, ' +
' OFC_GLPREFIX, ' +
' LAST_USER, ' +
' LAST_DATE, ' +
' LAST_ACTION) ' +
' SELECT ''' + @TRIG_ACTION + ''', ' +
' OFC_ID, ' +
' OFC_SHTNAME, ' +
' OFC_LNGNAME, ' +
' OFC_GLPREFIX, ' +
' LAST_USER, ' +
' LAST_DATE, ' +
' LAST_ACTION ' +
'FROM #TEMP_' + @TRIG_ACTION
EXECUTE(@VAR_SQL)
--PRINT '@VAR_SQL is ' + @VAR_SQL
END
IF (@TRIG_ACTION = 'UPDATED')
BEGIN
DECLARE @VAR_SQL_0_1 VARCHAR(1000), @VAR_SQL_0_2 VARCHAR(1000), @VAR_SQL_0_3 VARCHAR(1000), @VAR_SQL_0_4 VARCHAR(1000)
DECLARE @VAR_SQL_1_1 VARCHAR(1000), @VAR_SQL_1_2 VARCHAR(1000), @VAR_SQL_1_3 VARCHAR(1000), @VAR_SQL_1_4 VARCHAR(1000)
DECLARE @VAR_SQL_2_1 VARCHAR(1000), @VAR_SQL_2_2 VARCHAR(1000)
SET @VAR_SQL_0_1 = 'INSERT INTO L_OFFICE ('
SET @VAR_SQL_0_2 = 'LOG_ITEM'
SET @VAR_SQL_0_3 = ''
SET @VAR_SQL_0_4 = ') '
SET @VAR_SQL_1_1 = ' SELECT '
SET @VAR_SQL_1_2 = '''UPDATE-BEFORE'''
SET @VAR_SQL_1_3 = '''UPDATE-AFTER'''
SET @VAR_SQL_1_4 = ''
SET @VAR_SQL_2_1 = ' FROM #TEMP_DELETED '
SET @VAR_SQL_2_2 = ' FROM #TEMP_INSERTED '
DECLARE @VAR_FIELDCT TINYINT
DECLARE @VAR_FIELDCTR TINYINT
DECLARE @VAR_FIELD VARCHAR(100)
DECLARE @VAR_VALUE VARCHAR(100)
DECLARE @VAR_SQL_COMMA VARCHAR(100)
DECLARE @VAR_INCLUDE BIT
SELECT @VAR_FIELDCTR = 8, @VAR_FIELDCT = 8, @VAR_INCLUDE=0
WHILE (@VAR_FIELDCTR > 0)
BEGIN
SET @VAR_INCLUDE=0
-----------------------------------------------------------------------------------------------------
--// ALWAYS INCLUDE PRIMARY KEYS TO THE TABLE
-----------------------------------------------------------------------------------------------------
IF (@VAR_FIELDCTR = 7) BEGIN
SELECT @VAR_FIELD = 'OFC_ID', @VAR_VALUE = 'OFC_ID', @VAR_INCLUDE=1
END
-----------------------------------------------------------------------------------------------------
--// FIELDS THAT MUST BE LOGGED
-----------------------------------------------------------------------------------------------------
IF (@VAR_FIELDCTR = 6) BEGIN
SELECT @VAR_FIELD = 'LAST_USER', @VAR_VALUE = 'LAST_USER', @VAR_INCLUDE=1
END
IF (@VAR_FIELDCTR = 5) BEGIN
SELECT @VAR_FIELD = 'LAST_DATE', @VAR_VALUE = 'LAST_DATE', @VAR_INCLUDE=1
END
IF (@VAR_FIELDCTR = 4) BEGIN
SELECT @VAR_FIELD = 'LAST_ACTION', @VAR_VALUE = 'LAST_ACTION', @VAR_INCLUDE=1
END
-----------------------------------------------------------------------------------------------------
--// FIELDS THAT MIGHT HAVE CHANGED
-----------------------------------------------------------------------------------------------------
IF (@VAR_FIELDCTR = 6 AND UPDATE(OFC_SHTNAME)) BEGIN
SELECT @VAR_FIELD = 'OFC_SHTNAME', @VAR_VALUE = 'OFC_SHTNAME', @VAR_INCLUDE=1
END
IF (@VAR_FIELDCTR = 5 AND UPDATE(OFC_LNGNAME)) BEGIN
SELECT @VAR_FIELD = 'OFC_LNGNAME', @VAR_VALUE = 'OFC_LNGNAME', @VAR_INCLUDE=1
END
IF (@VAR_FIELDCTR = 4 AND UPDATE(OFC_GLPREFIX)) BEGIN
SELECT @VAR_FIELD = 'OFC_GLPREFIX', @VAR_VALUE = 'OFC_GLPREFIX', @VAR_INCLUDE=1
END
IF (@VAR_FIELDCTR <> @VAR_FIELDCT AND @VAR_INCLUDE=1)
BEGIN
SET @VAR_SQL_COMMA = ', '
SET @VAR_SQL_0_3 = @VAR_SQL_0_3 + @VAR_SQL_COMMA + @VAR_FIELD
SET @VAR_SQL_1_4 = @VAR_SQL_1_4 + @VAR_SQL_COMMA + @VAR_FIELD
END
ELSE
BEGIN
SET @VAR_SQL_COMMA = ' '
END
SET @VAR_FIELDCTR = @VAR_FIELDCTR - 1
END
--PRINT @VAR_SQL_0_1 + @VAR_SQL_0_2 + @VAR_SQL_0_3 + @VAR_SQL_0_4 + @VAR_SQL_1_1 + @VAR_SQL_1_2 + @VAR_SQL_1_4 + @VAR_SQL_2_1
--PRINT @VAR_SQL_0_1 + @VAR_SQL_0_2 + @VAR_SQL_0_3 + @VAR_SQL_0_4 + @VAR_SQL_1_1 + @VAR_SQL_1_3 + @VAR_SQL_1_4 + @VAR_SQL_2_2
EXECUTE( @VAR_SQL_0_1 + @VAR_SQL_0_2 + @VAR_SQL_0_3 + @VAR_SQL_0_4 + @VAR_SQL_1_1 + @VAR_SQL_1_2 + @VAR_SQL_1_4 + @VAR_SQL_2_1 )
EXECUTE( @VAR_SQL_0_1 + @VAR_SQL_0_2 + @VAR_SQL_0_3 + @VAR_SQL_0_4 + @VAR_SQL_1_1 + @VAR_SQL_1_3 + @VAR_SQL_1_4 + @VAR_SQL_2_2)
END
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply