What operation type invoked a trigger in SQL Server 2008?

Question

I'm contemplating a single SQL trigger to handle INSERT, UPDATE and DELETE operations as part of an auditing process.

Is there any statement, function or @@ variable I can interrogate to find out which operation type launched the trigger?

I've seen 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 else a little more direct or explicit?

Thanks,
Neil.

Answer 1

I have this Audit trigger working fine for me. It requires an 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 stores the type of transaction, the connected user and the Old & New Values. The only constraint is that the table being audited must have a primary key.

I have to say, that I did find this on a blog that I was reading but for the life of me I can't remember who it was - so apologies for that.

You need to ensure that you change the table name at the start of the trigger.

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

Answer 2

Oracle has INSERTING, DELETING, and UPDATING conditional predicates. There is no equivalent in SQL Server. (Though it is possible to do IF UPDATE (column))

With the MERGE statement now it is possible to do all three in the same operation (Edit though it seems SQL Server will divide it up into 3 operations)