Reputation: 421
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?
Upvotes: 2
Views: 1483
Reputation: 421
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?
Upvotes: 0
Reputation: 4585
I see two problems.
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.
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;
Upvotes: 1
Reputation: 6015
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
Upvotes: 0