What type of operation is triggered by a trigger in SQL Server 2008?

Question

I am considering a single SQL trigger to handle INSERT, UPDATE, and DELETE operations as part of the audit process.

Is there any instruction, function, or variable @@ that I can interrogate to find out what type of operation the trigger fired?

I saw the following pattern:

declare @type char(1) if exists (select * from inserted) if exists (select * from deleted) select @Type = 'U' else select @Type = 'I' else select @Type = 'D' 

but is there anything more direct or explicit?

Thanks,
Nile.

Answer 1

Oracle has INSERTING , DELETING and UPDATING conditional predicates. There is no equivalent in SQL Server. (Although you can do IF UPDATE (column) )

Now, with the MERGE you can perform all three operations ( Change , although it seems that SQL Server will divide it into 3 operations )

Answer 2

For me this audit trigger works fine for me. This requires an audit table as follows:

 CREATE TABLE [dbo].[Audit]( [Type] [char](1) NULL, [TableName] [varchar](128) NULL, [PK] [varchar](1000) NULL, [FieldName] [varchar](128) NULL, [OldValue] [varchar](max) NULL, [NewValue] [varchar](max) NULL, [UpdateDate] [datetime] NULL, [UserName] [varchar](128) NULL ) ON [PRIMARY] GO 

The audit trigger saves the type of transaction, the connected user, and old and new values. The only limitation is that the table to be checked must have a primary key.

I have to say that I found this on a blog that I read, but for life I don’t remember who it was - so I apologize for that.

You need to make sure that you change the table name at the start of the run.

 CREATE TRIGGER [dbo].[TR_TableName_Audit] ON [dbo].[TableName] FOR INSERT, UPDATE, DELETE AS DECLARE @bit INT, @field INT, @maxfield INT, @char INT, @fieldname VARCHAR(128), @TableName VARCHAR(128), @PKCols VARCHAR(1000), @sql VARCHAR(2000), @UpdateDate VARCHAR(21), @UserName VARCHAR(128), @Type CHAR(1), @PKSelect VARCHAR(1000) --You will need to change @TableName to match the table to be audited SELECT @TableName = 'TableName' -- date and user SELECT @UserName = SYSTEM_USER , @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) -- Action IF EXISTS (SELECT * FROM inserted) IF EXISTS (SELECT * FROM deleted) SELECT @Type = 'U' ELSE SELECT @Type = 'I' ELSE SELECT @Type = 'D' -- get list of columns SELECT * INTO #ins FROM inserted SELECT * INTO #del FROM deleted -- Get primary key columns for full outer join SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c WHERE pk.TABLE_NAME = @TableName AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND c.TABLE_NAME = pk.TABLE_NAME AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME -- Get primary key select for insert SELECT @PKSelect = COALESCE(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>''' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c WHERE pk.TABLE_NAME = @TableName AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND c.TABLE_NAME = pk.TABLE_NAME AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME IF @PKCols IS NULL BEGIN RAISERROR('no PK on table %s', 16, -1, @TableName) RETURN END SELECT @field = 0, @maxfield = MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName WHILE @field < @maxfield BEGIN SELECT @field = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field SELECT @bit = (@field - 1 )% 8 + 1 SELECT @bit = POWER(2,@bit - 1) SELECT @char = ((@field - 1) / 8) + 1 IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0 OR @Type IN ('I','D') BEGIN SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @field SELECT @sql = ' insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName) select ''' + @Type + ''',''' + @TableName + ''',' + @PKSelect + ',''' + @fieldname + '''' + ',convert(varchar(max),d.' + @fieldname + ')' + ',convert(varchar(max),i.' + @fieldname + ')' + ',''' + @UpdateDate + '''' + ',''' + @UserName + '''' + ' from #ins i full outer join #del d' + @PKCols + ' where i.' + @fieldname + ' <> d.' + @fieldname + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)' + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)' EXEC (@sql) END END