指定列上的SQL Server触发器

Question

嘿，

我正在尝试更新SQL Server 2014中表中的一些列，并在trigger上编写了一些代码，然后使用以下代码过程将新值插入到新表中：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Atrin Noori
-- =============================================
ALTER TRIGGER [dbo].[UsersOnUPDATE] 
ON  [dbo].[Users]
After UPDATE
AS 
BEGIN
SET NOCOUNT ON;
--
DECLARE @user_key int
SELECT @user_key = i.User_key FROM inserted i;
--  
IF UPDATE (User_fullname) 
BEGIN
DECLARE @newfullname nvarchar(MAX);
DECLARE @oldfullname nvarchar(MAX);
--
SELECT @oldfullname = i.User_fullname FROM deleted i;
SELECT @newfullname = i.User_fullname FROM inserted i;
--
INSERT INTO UsersLogs (UL_user_key_r, UL_date, UL_oldname, UL_newname, UL_IsDeleted)
VALUES (@user_key, GETDATE(), @oldfullname, @newfullname, 0)
END
--
ELSE IF UPDATE (User_password) 
BEGIN
DECLARE @newpassword nvarchar(10);
DECLARE @oldpassword nvarchar(10);      
-- 
SELECT @oldpassword = i.User_password FROM deleted i;
SELECT @newpassword = i.User_password FROM inserted i;
--
INSERT INTO UsersLogs (UL_user_key_r,UL_date,UL_oldpass,UL_newpass,UL_IsDeleted)
VALUES (@user_key, GETDATE(), @oldpassword, @newpassword, 0)
END 
--
ELSE IF UPDATE (User_username) 
BEGIN
DECLARE @newusername nvarchar(10);
DECLARE @oldusername nvarchar(10);
--
SELECT @oldusername = i.User_username FROM deleted i;
SELECT @newusername = i.User_username FROM inserted i;
--
INSERT INTO UsersLogs (UL_user_key_r, UL_date, UL_oldusername, UL_newusername, UL_IsDeleted)
VALUES (@user_key, GETDATE(), @oldusername, @newusername, 0)
END
--
ELSE IF UPDATE (User_position_id_r) 
BEGIN
DECLARE @newposid tinyint;
DECLARE @oldposid tinyint;
--
SELECT @oldposid = i.User_position_id_r FROM deleted i;
SELECT @newposid = i.User_position_id_r FROM inserted i;
--
INSERT INTO UsersLogs (UL_user_key_r, UL_date, UL_oldPosid, UL_newPosid, UL_IsDeleted)
VALUES (@user_key, GETDATE(), @oldposid, @newposid, 0)
END
END

当我手动更改每列中的值并插入到一个名为UsersLogs的新表中时，此触发器工作正常。然而，当我使用我正在开发的c#应用程序来更新值时，它并不能正常工作。。。

考虑：

我正试图通过我的应用程序更改用户的密码并且使用CCD_ 4。

Stored Procedure工作正常并更新值，BUTUserOnUpdate的触发器将User_Fullnme的old和new值(不是旧密码和新密码(插入到新表(UsersLgos)中，并将其他值设置为null(可以为null(。

我的意思是只有这部分代码会运行：

IF UPDATE (User_fullname) 
BEGIN
DECLARE @newfullname nvarchar(MAX);
DECLARE @oldfullname nvarchar(MAX);
--
SELECT @oldfullname = i.User_fullname FROM deleted i;
SELECT @newfullname = i.User_fullname FROM inserted i;
--
INSERT INTO UsersLogs (UL_user_key_r, UL_date, UL_oldname, UL_newname, UL_IsDeleted)
VALUES (@user_key, GETDATE(), @oldfullname, @newfullname, 0)
END

根据我刚才说的。。。我更改了密码并设置了一个条件来检查更新的列。

但问题是，为什么触发器无法实现哪一列是通过应用程序更新的？

注意：

我再说一遍：手动更改和更新效果良好

Answer 1

UPDATE()函数仅告诉列是否存在于UPDATE语句中，而不告知值是否实际更改。此外，teinserted和deleted表可以包含多行，也可以不包含任何行。

所以，你的触发器应该是这样的：

ALTER TRIGGER [dbo].[UsersOnUPDATE] 
ON  [dbo].[Users]
After UPDATE
AS 
SET NOCOUNT ON;
IF NOT (UPDATE(User_fullname) OR UPDATE(User_password) OR UPDATE(User_username) OR UPDATE(User_position_id_r))
RETURN;  -- this only tells you if the column was present
INSERT INTO UsersLogs
(UL_user_key_r, UL_date, UL_IsDeleted,
UL_oldname, UL_newname,
UL_oldpass, UL_newpass,
UL_oldusername, UL_newusername,
UL_oldPosid, UL_newPosid)
SELECT
i.user_key, GETDATE(), 0,
d.fullname, i.fullname,
d.User_password, i.User_password,
d.User_username, i.User_username,
d.User_position_id_r, i.User_position_id_r
FROM inserted i
JOIN deleted d ON i.user_key = d.user_key
WHERE EXISTS (    -- this checks for any differences
SELECT i.fullname, i.User_password, i.User_username, i.User_position_id_r
EXCEPT        -- this will deal with nulls correctly
SELECT d.fullname, d.User_password, d.User_username, d.User_position_id_r
);

在只更新某些列的情况下，这会使其他列具有相同的before和after值。但一些CASE表达式应该能解决这个问题。

ALTER TRIGGER [dbo].[UsersOnUPDATE] 
ON  [dbo].[Users]
After UPDATE
AS 
SET NOCOUNT ON;
IF NOT (UPDATE(User_fullname) OR UPDATE(User_password) OR UPDATE(User_username) OR UPDATE(User_position_id_r))
RETURN;  -- this only tells you if the column was present
INSERT INTO UsersLogs
(UL_user_key_r, UL_date, UL_IsDeleted,
UL_oldname, UL_newname,
UL_oldpass, UL_newpass,
UL_oldusername, UL_newusername,
UL_oldPosid, UL_newPosid)
SELECT
i.user_key, GETDATE(), 0,
CASE WHEN d.fullname <> i.fullname THEN d.fullname END, CASE WHEN d.fullname <> i.fullname THEN i.fullname END
CASE WHEN d.User_password <> i.User_password THEN d.User_password END, CASE WHEN d.User_password <> i.User_password THEN i.User_password END,
CASE WHEN d.User_username <> i.User_username THEN d.User_username END, CASE WHEN d.User_username <> i.User_username THEN i.User_username END,
CASE WHEN d.User_position_id_r <> i.User_position_id_r THEN d.User_position_id_r END, CASE WHEN d.User_position_id_r <> i.User_position_id_r THEN i.User_position_id_r END
FROM inserted i
JOIN deleted d ON i.user_key = d.user_key
WHERE EXISTS (    -- this checks for any differences
SELECT i.fullname, i.User_password, i.User_username, i.User_position_id_r
EXCEPT        -- this will deal with nulls correctly
SELECT d.fullname, d.User_password, d.User_username, d.User_position_id_r
);

如果您希望每个更改的值都有单独的行，则可以使用CROSS APPLY取消透视

Answer 2

update()触发器函数，如果列被更新，即使具有相同的值，也会返回true。因此，当您更新password时，您可能正在更新User_fullname列和其他列(当然，值与之前相同(。因此UPDATE(User_fullname(返回true。

但是，同样按照您编写代码的方式，触发器适用于只有一列被更新的情况，并且在代码中按该顺序更新，例如，如果UPDATE (User_fullname)为true，则由于ELSE IF，您的代码不会检查其他条件。您可能想要删除else并检查每一列，或者完全更改策略以在触发器中记录数据。

然后根据您的评论，去掉所有if-else，并为所有列提供一个类似的插入语句：

INSERT INTO UsersLogs (UL_user_key_r,UL_date,UL_oldpass,UL_newpass,<all columns>)
select (@user_key, GETDATE(), case when deleted.password <> new.password then deleted.password else null end , case when deleted.password <> new.password then inserted.password else null end , ....)
from inserted i
join deleted d on i.userkey = d.userkey