更新多行的触发器

Question

我遇到了这个触发器的问题：

ALTER TRIGGER [dbo].[trg_B_U_Login]
   ON [dbo].[Login]
   FOR UPDATE
AS
BEGIN
    IF @@ROWCOUNT = 0
        RETURN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @Campos nvarchar(500);
    DECLARE @Old_DataRemocao nvarchar(20);
    DECLARE @New_DataRemocao nvarchar(20);
    DECLARE @Old_IDCriador nvarchar(10);
    DECLARE @New_IDCriador nvarchar(10);
    SELECT @Campos='';

    IF(Exists(SELECT * FROM deleted WHERE DataRemocao is NULL))
    BEGIN
        SELECT @Old_DataRemocao='NULL';
    END
    ELSE
    BEGIN
        SELECT @Old_DataRemocao=(SELECT * DataRemocao FROM deleted);
    END

    IF(Exists(SELECT * FROM inserted WHERE DataRemocao is NULL))
    BEGIN
        SELECT @New_DataRemocao=NULL;

    END
    ELSE
    BEGIN
        SELECT @New_DataRemocao=(SELECT * DataRemocao FROM inserted);
    END

    IF(@Old_DataRemocao<>@New_DataRemocao)
    BEGIN
        SELECT @Campos=@Campos+'DataRemocao={'+@Old_DataRemocao+' -> ' + @New_DataRemocao +'}; ';
    END

    IF(EXISTS(SELECT * FROM deleted as del, inserted AS ins WHERE ins.Login<>del.Login))
    BEGIN
        SELECT @Campos=@Campos+'Login={'+ del.Login +' -> '+ ins.Login+'}; ' FROM deleted as del, inserted as ins
    END

......

    IF(EXISTS(SELECT * FROM deleted as del, inserted AS ins WHERE ins.DataCriacao<>del.DataCriacao))
    BEGIN
        SELECT @Campos=@Campos+'DataCriacao={'+ del.DataCriacao +' -> '+ ins.DataCriacao+'}; ' FROM deleted as del, inserted as ins
    END

    IF(EXISTS(SELECT * FROM deleted as del, inserted AS ins WHERE ins.Nome<>del.Nome))
    BEGIN
        SELECT @Campos=@Campos+'Nome={'+ del.Nome +' -> '+ ins.Nome+'}; ' FROM deleted as del, inserted as ins
    END

    IF(@Campos<>'')
    BEGIN
        INSERT Login_Hs (IDUtilizador, Tabela, Metodo, Chave, Campos, Data)
            SELECT '1', 'Login', 'UPDATE', 'IDLogin='+Convert(nvarchar,ID),
                @Campos, CONVERT(DATETIME, GETDATE(), 105)
            FROM inserted
    END
END

它保存了更新的每一行中所做的更改，并由此更新执行，

UPDATE Login
SET DataRemocao = CONVERT(datetime,GETDATE(),105)
WHERE IDPerfil = 25 AND DataRemocao IS NULL;

该更新正在更新多行

，并且 sql server 2008 给出此错误：

Msg 512, Level 16, State 1, Procedure trg_B_U_Login, Line 83
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

这仅在我更新时有效一行。 我可以做什么来解决这个问题？

Answer 1

我看到两个问题。

1. 您正在尝试将 (Select * From) 查询写入 varchar 变量。如果有人出现并在您的表中添加一列，这种情况就会被破坏。在选择中指定您的列名称。

2. 如果您的代码一次只更新一行，那么您的触发器应该可以正常工作。如果您更新的内容超过此数量，则 INSERTED 和 DELETED 表变量将具有多行。因此出现错误。如果您在 Oracle 中工作，您可以简单地将 FOR EACH ROW 选项添加到触发器中，代码将游标浏览已更改的记录。

在 MSSQL 中，如果您确实需要这样做，则必须在 DELETED 或 INSERTED 中的记录上打开游标，并逐行处理这些行。这可能真的很慢。如果我是你，我会尝试重构这段代码以使用集合操作。

像这样的东西：

ALTER TRIGGER [dbo].[trg_B_U_Login]
   ON [dbo].[Login]
   FOR UPDATE
AS
BEGIN
        INSERT Login_Hs (IDUtilizador, Tabela, Metodo, Chave, Campos, Data)
            SELECT '1', 'Login', 'UPDATE', 'IDLogin='+Convert(nvarchar,ID),
            'DataRemocao={'+ coalesce(deleted.campos, 'NULL') + ' -> ' 
               + coalesce(inserted.campos, 'NULL'),
               CONVERT(DATETIME, GETDATE(), 105)
            FROM inserted
              Inner Join deleted on inserted.idcol=deleted.idcol
END;

Answer 2

好吧，您正在尝试在潜在的多值查询中捕获单个值。其中一个地方如下：

   SELECT @Old_DataRemocao=(SELECT * DataRemocao FROM deleted);

更新：
根据您最近的言论，我想补充一点，您需要尝试这样的操作来审核登录表的更改：

insert into Login_Hs (columns)
select i.col1, i.col2, ..., i.colN
FROM inserted i

Answer 3

感谢@Bill，

我做到了并且有效！ ID、Login、PassWord、IDHomePage、IDSkin、IDPerfil、IDCriaador、Email、DataCriacao、DataRemocao、Nome 是我的登录表的字段，我保存了它们。

插入 Login_Hs（IDUtilizador、Tabela、Metodo、Chave、Campos、数据）
选择 '1', '登录', '更新', 'ID='+ 转换(nvarchar,i.ID),
'登录={'+ Convert(nvarchar,coalesce(d.Login,'NULL')) + ' -> ' + Convert(nvarchar,coalesce(i.Login,'NULL')) + '};' +
'PassWord={'+合并(d.PassWord,'NULL')+'-> ' +合并(i.PassWord,'NULL') + '};' +
'IDHomePage={'+ Convert(nvarchar,coalesce(d.IDHomePage,'NULL')) + ' -> ' + Convert(nvarchar,coalesce(i.IDHomePage,'NULL')) + '};' +
'IDPerfil={'+ Convert(nvarchar,coalesce(d.IDPerfil,'NULL')) + ' -> ' + Convert(nvarchar,coalesce(i.IDPerfil,'NULL')) + '};' +
'IDCriador={'+ Convert(nvarchar,coalesce(d.IDCriador,'NULL')) + ' -> ' + Convert(nvarchar,coalesce(i.IDCriador,'NULL')) + '};' +
'电子邮件={'+合并(d.Email,'NULL')+'-> ' +合并(i.Email,'NULL') + '};' +
'DataCriacao={'+合并(Convert(varchar(20),d.DataCriacao,105),'NULL') + ' -> ' +合并(Convert(varchar(20),i.DataCriacao,105),'NULL') + '};' +
'DataRemocao={'+合并(Convert(varchar(20),d.DataRemocao,105),'NULL') + ' -> ' +合并(Convert(varchar(20),i.DataRemocao,105),'NULL') + '};' +
'Nome={'+合并(d.Nome,'NULL')+'-> ' + 合并(i.Nome,'NULL') + '};' ,
转换（日期时间，GETDATE（），105）
从插入我
INNER JOIN 删除 d ON i.ID=d.ID;

有什么办法可以只保存不同的吗？