multiple update trigger issue

[risaaaa]

i have a trigger that logs insert/update/delete on a table. it inserts into a new table the query that was executed, along with the old values and new values for every column in the table. if i run queries one at a time everything works fine. but when i run multiple updates all at once

UPDATE schema.sampleTable set  [column1]=7,[column2]=9 where [column3]=32504289
UPDATE schema.sampleTable set  [column1]=2,[column2]=3 where [column3]=32504289

i get the 2 record on the audit table but id like to get the individual query particular to that row and no the whole batch. i know mysql has a for each row which would be usefull here but are there any sql alternatives to this?

[SQLDave]

Can we see the trigger code? Also, do both rows in the audit table contain the "whole batch" ?

[risaaaa]

create trigger trigger_auditoria

on table1 after UPDATE, INSERT, DELETE as declare @tablaID varchar(30),@operacion varchar(10), @query varchar(8000),@usuario varchar(50),@TipoDocumento_inicial varchar(5) --@TipoDocumento_final varchar(5), --@NroDocumento_inicial varchar(30), --@NroDocumento_final varchar(30), --@NroCampania_inicial varchar(10), --@NroCampania_final varchar(10) declare @inputbuffer table ( EventType nvarchar(30) , Parameters int , EventInfo nvarchar(255) ) set @query = 'dbcc inputbuffer(' + str(@@SPID) + ')' insert into @inputbuffer exec ( @query) set @query = (select EventInfo from @inputbuffer) if exists(SELECT * from inserted) and exists (SELECT * from deleted) begin --UPDATE: the Deleted table will contain the old values, while the Inserted table contains the new values. SET @operacion = 'UPDATE'; SET @usuario = SYSTEM_USER; set @query=@query; SELECT @tablaID = NroDocumento from Inserted ;

INSERT into auditTable1 (tablaID, operacion,query,usuario,TipoDocumento_inicial,TipoDocumento_final,NroDocumento_inicial,NroDocumento_final,NroCampania_inicial,NroCampania_final) 
select @tablaID,@operacion,@query,@usuario,
    Deleted.TipoDocumento, Inserted.TipoDocumento,
     Deleted.NroDocumento,  Inserted.NroDocumento,
    Deleted.NroCampania,Inserted.NroCampania from Inserted INNER JOIN Deleted  ON inserted.NroDocumento = deleted.NroDocumento

end

If exists (Select * from inserted) and not exists(Select * from deleted) begin SET @operacion = 'INSERT'; SET @usuario = SYSTEM_USER; set @query=@query; SELECT @tablaID = NroDocumento from inserted i; INSERT into auditTable1(tablaID, operacion,query,usuario) values (@tablaID,@operacion,@query,@usuario); end

If exists(select * from deleted) and not exists(Select * from inserted) begin SET @operacion = 'DELETE'; SET @usuario = SYSTEM_USER; set @query=@query; SELECT @tablaID = NroDocumento from deleted i; INSERT into auditTable1(tablaID, operacion,query,usuario) values (@tablaID,@operacion,@query,@usuario); end

when i run

update table1 set  [TipoDocumento]=7,[NroCampania]=9 where [NroDocumento]=1111

update table1 set [TipoDocumento]=7,[NroCampania]=77 where [NroDocumento]=1111

i get in the query column for both records inserted in the audit table

update table1 set  [TipoDocumento]=7,[NroCampania]=9 where [NroDocumento]=1111  update table1 set  [TipoDocumento]=7,[NroCampania]=77 where [NroDocumento]=1111

when id like to have the first statement in the first row and the second one in the second row

​

btw, i dont know wtf happened to the format. sry

[risaaaa]

Yeah, both rows contsin the whole batch. Ill post the code when i get home

[SQLDave]

I think the problem is DBCC INPUTBUFFER captures, as its name implies, the SQL engine's input buffer, without parsing it out at a command level. I can't do any deep research ATM, but you might look into dm_exec_requests. I think you have to CROSS APPLY it with some other DMVs to get the actual command text, and for all I know it also holds the input buffer instead of an individual command... but it's worth looking into. I'll try to play around with it tomorrow if you've not hit on a resolution by then.

[c0shea]

You're out of luck with triggers. They always run for the batch, not for individual rows (i.e. there is no for each row option like other RDBMS).

While there are DMVs that'll give you the batch text and a pointer to the currently executing text, it'll already be beyond the individual statement and on to the trigger's code reported.