如何在 SQL Server 中使用条件触发器进行更新

Question

>我有这样的表格：

---------------table1--------------------------------------------
id    |   name  | lname   |   displayStatus   | delStatus
1         a         b               1              0
2         aa        bb              0              0
3         aaa       bbb             1              0
4         aaaa      bbbb            0              1
---------------tbl_LOG--------------------------------------------
id    |   rowData| actType

我有这个更新触发器：

alter trigger tgr_delete
on table1
after update
as
begin
declare @rowData nvarchar(max), @username nvarchar(50)
if update(delStatus)
begin
if((select delStatus from inserted) = 1)
begin
set @rowData = (select CAST((CAST(id as nvarchar(12)) + ' | ' + name) as nvarchar(max)) from inserted)
exec sp_insert_LOG 'table1', @rowData, 2
end
else if((select delStatus from inserted) = 0)
begin
set @rowData = (select CAST((CAST(id as nvarchar(12)) + ' | ' + name) as nvarchar(max)) from inserted)
exec sp_insert_LOG 'table1', @rowData, 3
end
end
else if update(displayStatus)
begin
if((delStatus = 0 or delStatus is null) and (select displayStatus from inserted) = 0)
begin
set @rowData = (select CAST((CAST(id as nvarchar(12)) + ' | ' + name) as nvarchar(max)) from inserted)
exec sp_insert_LOG 'table1', @rowData, 4
end
else if((delStatus = 0 or delStatus is null) and (select displayStatus from inserted) = 1)
begin
set @rowData = (select CAST((CAST(id as nvarchar(12)) + ' | ' + name) as nvarchar(max)) from inserted)
exec sp_insert_LOG 'table1', @rowData, 5
end
end
end

我有 3 个存储过程

• 首先是用于更新 delStatus
• 第二个用于更新显示状态
• 第三个是用于更新所有列

现在我应该怎么做才能避免干扰前两个条件。

Answer 1

这不是一个完整的答案，因为我不知道sp_insert_LOG做什么，但它应该让你开始正确的方向：

CREATE TRIGGER trg_Table1_Update ON Table1
FOR UPDATE
AS

DECLARE @LogData AS TABLE
(
TableName sysname,
RowData nvarchar(max),
ActionType int
)
INSERT INTO @LogData (TableName, RowData, ActionType)
SELECT 'Table1', 
CAST((CAST(id as nvarchar(12)) + ' | ' + name) as nvarchar(max)),
CASE WHEN i.delStatus = 1 THEN 2
WHEN i.delStatus = 0 THEN 3
WHEN ISNULL(i.delStatus, 0) = 0 AND i.displayStatus = 0 THEN 4
WHEN ISNULL(i.delStatus, 0) = 0 AND i.displayStatus = 1 THEN 5
END
FROM INSERTED i 
INNER JOIN Deleted d ON i.Id = d.Id
WHERE (i.name = d.name OR i.name IS NULL and d.name IS NULL)
AND (i.lname = d.lname OR i.lname IS NULL and d.lname IS NULL)
AND
(
(
ISNULL(i.delStatus, 0) <> ISNULL(d.delStatus, 0)
AND ISNULL(i.displayStatus, 0) = ISNULL(d.displayStatus, 0)
)
OR 
(
ISNULL(i.delStatus, 0) = ISNULL(d.delStatus, 0)
AND ISNULL(i.displayStatus, 0) <> ISNULL(d.displayStatus, 0)
)
)

此时，@LogData表包含要发送到sp_insert_LOG的任何数据。我建议创建一个新的存储过程，如sp_insert_log将接受表值参数并处理此数据。