代码之家 › 专栏 › 技术社区 › Neil Moss

什么操作类型在SQL Server 2008中调用了触发器?

triggers sql-server-2008 tsql sql

Neil Moss · 技术社区 · 14 年前

我正在考虑一个SQL触发器,作为审计过程的一部分来处理插入、更新和删除操作。

是否有任何语句、函数或@@variable我可以查询以找出启动触发器的操作类型?

我看到了以下模式:

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'

但是还有什么更直接或更明确的吗?

谢谢,
尼尔。

2 回复 | 直到 12 年前

Martin Smith 14 年前

甲骨文有 INSERTING , DELETING 和 UPDATING 条件谓词。SQL Server中没有等效项。(尽管有可能 IF UPDATE (column) )

与 MERGE 语句现在可以在同一个操作中执行这三个操作( 编辑尽管看起来SQL Server会 divide it up into 3 operations )

codingbadger 14 年前

我让这个审计触发器为我工作得很好。它需要一个审计表,如下所示:

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

审核触发器存储事务类型、连接的用户和旧值和新值。唯一的约束是要审计的表必须有一个主键。

我不得不说,我确实在我读的博客上找到了这个,但是对于我的生活,我记不起是谁了——为此道歉。

您需要确保在触发器开始时更改表名。

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