问哪种操作类型调用了SQL Server 2008中的触发器？
EN

Stack Overflow用户

提问于 2010-06-18 08:41:34

回答 2查看 4.3K关注 0票数 4

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

是否有任何语句、函数或@@变量可供我查询，以找出哪种操作类型启动了触发器？

我已经看到了以下模式：

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'

但是，还有其他更直接或更明确的东西吗？

谢谢,

尼尔。

tsql

sql-server-2008

triggers

sql

音视频体验馆免费体验试用

一站式·全方位体验音视频产品，所见即所得，30分钟即可上线同款能力

回答 2

Stack Overflow用户

回答已采纳

发布于 2010-06-18 14:31:31

Oracle有INSERTING、DELETING和UPDATING条件谓词。SQL Server中没有等效项。(虽然也可以使用IF UPDATE (column))

现在，使用MERGE语句可以在同一操作中完成所有这三个操作(编辑，尽管看起来SQL Server将使用divide it up into 3 operations)

票数 1

Stack Overflow用户

发布于 2010-06-18 08:51:34

我让这个Audit触发器在我身上工作得很好。它需要一个审计表格，如下所示：

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