Server :使用一个查询触发更新
Server :使用一个查询触发更新
提问于 2013-10-31 18:33:39
我有一个触发器，当某些列被更新时，它会登录到Audit表。在删除一列之后，我在使用columns_updated()时遇到了很多问题，所以我正在重写触发器--只使用Update()，这似乎是可行的。
但是，我一直在每个IF语句If Update(ColumnName)中重复查询，请参阅下面的代码，因为我需要动态更改列名。有什么方法可以欺骗这个问题吗?这样我就不必再重复INSERT查询了吗？
IF (@TYPE IN ('U') and UPDATE(ShippingAddressFlag))
BEGIN
SELECT @fieldname = 'ShippingAddressFlag'
SELECT @SQL = 'insert Audit (Type, TableName, PKCol, PK, FieldName, OldValue, NewValue, UpdateDate, DBUserName, UserID)'
SELECT @SQL = @SQL + ' select ''' + @TYPE + ''''
SELECT @SQL = @SQL + ',''' + @TableName + ''''
SELECT @SQL = @SQL + ',''' + @PKCol + ''''
SELECT @SQL = @SQL + ',' + @PK
SELECT @SQL = @SQL + ',''' + @fieldname + ''''
SELECT @SQL = @SQL + ',convert(varchar(1000),d.' + replace(@fieldname, '''', '') + ')'
SELECT @SQL = @SQL + ',convert(varchar(1000),i.' + replace(@fieldname, '''', '') + ')'
SELECT @SQL = @SQL + ',''' + @UpdateDate + ''''
SELECT @SQL = @SQL + ',''' + @UserName + ''''
SELECT @SQL = @SQL + ',''' + @UserID + ''''
SELECT @SQL = @SQL + ' from #ins i full outer join #del d'
SELECT @SQL = @SQL + @PKCols
SELECT @SQL = @SQL + ' where i.' + @fieldname + ' <> d.' + @fieldname
SELECT @SQL = @SQL + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
SELECT @SQL = @SQL + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
Print @SQL
END
IF (@TYPE IN ('U') and UPDATE(Amount))
BEGIN
SELECT @fieldname = 'Amount'
SELECT @SQL = 'insert Audit (Type, TableName, PKCol, PK, FieldName, OldValue, NewValue, UpdateDate, DBUserName, UserID)'
SELECT @SQL = @SQL + ' select ''' + @TYPE + ''''
SELECT @SQL = @SQL + ',''' + @TableName + ''''
SELECT @SQL = @SQL + ',''' + @PKCol + ''''
SELECT @SQL = @SQL + ',' + @PK
SELECT @SQL = @SQL + ',''' + @fieldname + ''''
SELECT @SQL = @SQL + ',convert(varchar(1000),d.' + replace(@fieldname, '''', '') + ')'
SELECT @SQL = @SQL + ',convert(varchar(1000),i.' + replace(@fieldname, '''', '') + ')'
SELECT @SQL = @SQL + ',''' + @UpdateDate + ''''
SELECT @SQL = @SQL + ',''' + @UserName + ''''
SELECT @SQL = @SQL + ',''' + @UserID + ''''
SELECT @SQL = @SQL + ' from #ins i full outer join #del d'
SELECT @SQL = @SQL + @PKCols
SELECT @SQL = @SQL + ' where i.' + @fieldname + ' <> d.' + @fieldname
SELECT @SQL = @SQL + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
SELECT @SQL = @SQL + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
Print @SQL
END回答 1
Stack Overflow用户
回答已采纳
发布于 2013-10-31 20:55:46
我不确定以下内容的性能，但您可以使用unpivot获得类似的效果
将任何audit表设为
CREATE TABLE dbo.AuditLog
(
DatabaseName SYSNAME,
SchemaName SYSNAME,
TableName SYSNAME,
ColumnName SYSNAME,
KeyValue SQL_VARIANT,
OldValue SQL_VARIANT,
NewValue SQL_VARIANT,
TransactionType VARCHAR(10),
LogDate AS CURRENT_TIMESTAMP
)
GO然后是要记录的“普通”表
CREATE TABLE dbo.MyTable
(
Id INT IDENTITY(1,1),
Col1 INT,
Col2 INT,
Col3 INT
)
GO使用unpivot记录更改的触发器为
ALTER TRIGGER dbo.trg_MyTable_AuditLog
ON dbo.MyTable
FOR INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
BEGIN
DECLARE @DATABASENAME SYSNAME = DB_NAME(),
@TABLENAME SYSNAME = N'MyTable',
@SCHEMANAME SYSNAME = N'dbo',
@INSERTED SMALLINT = 1,
@DELETED SMALLINT = 2,
@UPDATED SMALLINT = 3
DECLARE @action SMALLINT = 0
SELECT @action = CASE WHEN EXISTS(SELECT 1 FROM inserted) THEN @INSERTED ELSE 0 END +
CASE WHEN EXISTS(SELECT 1 FROM deleted) THEN @DELETED ELSE 0 END
IF @action = @INSERTED
BEGIN
INSERT INTO dbo.AuditLog (DatabaseName, SchemaName, TableName, ColumnName, KeyValue, OldValue, NewValue, TransactionType)
SELECT @DATABASENAME, @SCHEMANAME, @TABLENAME, ColumnName, Id, NULL, NewValue, 'INSERT'
FROM inserted
UNPIVOT (
NewValue
FOR ColumnName IN ( Col1, Col2 )
) upiv
END
IF @action = @DELETED
BEGIN
INSERT INTO dbo.AuditLog (DatabaseName, SchemaName, TableName, ColumnName, KeyValue, OldValue, NewValue, TransactionType)
SELECT @DATABASENAME, @SCHEMANAME, @TABLENAME, ColumnName, Id, OldValue, Null, 'DELETE'
FROM deleted
UNPIVOT (
OldValue
FOR ColumnName IN ( Col1, Col2 )
) upiv
END
IF @action = @UPDATED
BEGIN
INSERT INTO dbo.AuditLog (DatabaseName, SchemaName, TableName, ColumnName, KeyValue, OldValue, NewValue, TransactionType)
SELECT @DATABASENAME, @SCHEMANAME, @TABLENAME, upiv_inserted.ColumnName,
upiv_inserted.Id, upiv_deleted.OldValue, upiv_inserted.NewValue, 'UPDATE'
FROM (
SELECT ColumnName, Id, NewValue
FROM inserted
UNPIVOT (
NewValue
FOR ColumnName IN ( Col1, Col2 )
) upiv
) upiv_inserted
JOIN (
SELECT ColumnName, Id, OldValue
FROM deleted
UNPIVOT (
OldValue
FOR ColumnName IN ( Col1, Col2 )
) upiv
) upiv_deleted
ON upiv_deleted.Id = upiv_inserted.Id
AND upiv_deleted.ColumnName = upiv_inserted.ColumnName
WHERE upiv_deleted.OldValue <> upiv_inserted.NewValue
END
END
GO要做到这一点，未旋转的列必须是相同的数据类型，因此您可能需要添加一些派生表，酌情转换列。
而且，unpivot似乎对varchar的长度和排序规则很敏感，一个更好的解决方案是使用cross apply而不是unpivot。cross apply似乎对字符串长度没有相同的“敏感性”，可以将表达式更简洁地应用于列。
这意味着将以前的枢轴更改为
INSERT INTO dbo.AuditLog (DatabaseName, SchemaName, TableName, ColumnName, KeyValue, OldValue, NewValue, TransactionType)
SELECT @DATABASENAME, @SCHEMANAME, @TABLENAME, ColumnName, Id, OldValue, Null, 'DELETE'
FROM deleted
CROSS APPLY (
VALUES
('Col1', Col1),
('Col2', Col2)
) upiv (ColumnName, OldValue)编辑备注：
IF @action = @UPDATED
BEGIN
INSERT INTO dbo.AuditLog (DatabaseName, SchemaName, TableName, ColumnName, KeyValue, OldValue, NewValue, TransactionType)
SELECT @DATABASENAME, @SCHEMANAME, @TABLENAME, upiv_inserted.ColumnName,
upiv_inserted.Id, upiv_deleted.OldValue, upiv_inserted.NewValue, 'UPDATE'
FROM (
SELECT ColumnName, Id, NewValue
FROM inserted
CROSS APPLY (
VALUES
('Col1', Col1),
('Col2', Col2)
) upiv (ColumnName, NewValue)
) upiv_inserted
JOIN (
SELECT ColumnName, Id, OldValue
FROM deleted
CROSS APPLY (
VALUES
('Col1', Col1),
('Col2', Col2)
) upiv (ColumnName, OldValue)
) upiv_deleted
ON upiv_deleted.Id = upiv_inserted.Id
AND upiv_deleted.ColumnName = upiv_inserted.ColumnName
WHERE upiv_deleted.OldValue <> upiv_inserted.NewValue
END页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19714122
复制相关文章
相似问题
腾讯云开发者
