Trigger for update multi line

Question

Hi,

I'm having a problem with this trigger:

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

that saves the changes made in each row that is updated and is being executed by this update

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

which is updating more than one row

and sql server 2008 is giving this error:

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.

This just works when I update a single line. What can i do to resolve this?

Answer 1

Well, you're trying to catch a single value in a potentially multi-value query. One such place is as follows:

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

Update: In light of your recent remarks, I would like to add that you need to try something like this to audit changes to the login table:

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

Answer 2

I see two problems.

1. You are trying to write from a (Select * From) query into a varchar variable. That will break if someone comes along and adds a column to your table. Specify your column name in the select.

2. Your trigger should work fine if your code updates only one row at a time. If you update more than that, the INSERTED and DELETED table variables have multiple rows. Thus the error. If you were working in Oracle, you could simply add the FOR EACH ROW option to your trigger, and the code would cursor through the changed records.

In MSSQL, if you really need to do this, you will have to open a cursor on the records in either DELETED or INSERTED and handle the rows one by one. This can be really slow. If I were you, I would try to refactor this code to use set operations.

Something like this:

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 3

Thanks to @Bill,

I did this and works! ID, Login, PassWord, IDHomePage, IDSkin, IDPerfil, IDCriador, Email, DataCriacao, DataRemocao, Nome are the fields of my table Login, and i saving them.

INSERT INTO Login_Hs (IDUtilizador, Tabela, Metodo, Chave, Campos, Data) SELECT '1', 'Login', 'UPDATE', 'ID='+ convert(nvarchar,i.ID), 'Login={'+ Convert(nvarchar,coalesce(d.Login,'NULL')) + ' -> ' + Convert(nvarchar,coalesce(i.Login,'NULL')) + '};' + 'PassWord={'+ coalesce(d.PassWord,'NULL') + ' -> ' + coalesce(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')) + '};' + 'Email={'+ coalesce(d.Email,'NULL') + ' -> ' + coalesce(i.Email,'NULL') + '};' + 'DataCriacao={'+ coalesce(Convert(varchar(20),d.DataCriacao,105),'NULL') + ' -> ' + coalesce(Convert(varchar(20),i.DataCriacao,105),'NULL') + '};' + 'DataRemocao={'+ coalesce(Convert(varchar(20),d.DataRemocao,105),'NULL') + ' -> ' + coalesce(Convert(varchar(20),i.DataRemocao,105),'NULL') + '};' + 'Nome={'+ coalesce(d.Nome,'NULL') + ' -> ' + coalesce(i.Nome,'NULL') + '};' , CONVERT(DATETIME, GETDATE(), 105) FROM inserted i INNER JOIN deleted d ON i.ID=d.ID;

Is there any way to just save the ones that are diferent?