Friday, March 30, 2012

Help On Trigger For Delete

I have a SQL statement that deletes a lot of records in a table (PACCESOS_DET) and a Trigger that fires for delete on the table.
The Trigger works fine when only one record is deleted but no when more than record is deleted; it only works for 1 and there is no error message.
For each row deleted I need to update a column in another table (PACCESOS_CAB).
This the trigger...

CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET
FOR DELETE
AS
declare @.mdias as int
declare @.mFKFeria as int
declare @.mtipo as char(1)
declare @.mfkcontacto as varchar(7)

if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo)
begin
select @.mfkferia=m.fkferia, @.mfkcontacto = m.fkcontacto, @.mtipo = m.tipo, @.mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo
update PACCESOS_CAB set diasvisita = @.mdias -1 where FKFeria= @.mFKFeria and FKContacto=@.mFKContacto and Tipo=@.mTipo
end

Thanks in advanced."Inside every large program is a small program screaming to get out."

Your trigger is MUCH too complicated. Best I can figure, this is all you need:

CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET
FOR DELETE
AS

begin
update PACCESOS_CAB
set PACCESOS_CAB.diasvisita = PACCESOS_CAB.diasvisita -1
from PACCESOS_CAB
inner join deleted
on PACCESOS_CAB.FKFeria = deleted.FKFeria
and PACCESOS_CAB.fkcontacto = deleted.fkcontacto
and PACCESOS_CAB.Tipo = deleted.Tipo
end|||It worked fine, sinco I keep on not Knowing the problem with the first solution.

anyway, lot of thanks blindman.|||Problems with your first solution:

This phrase is completely unnecessary, as records between the two tables will be matched in the UPDATE query:

"if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo)"

These variables are not required, because as you can see from the solution all the records can be updated simultaneously with a single UPDATE statement, so it is not necessary to store values in temporary variables:

"declare @.mdias as int
declare @.mFKFeria as int
declare @.mtipo as char(1)
declare @.mfkcontacto as varchar(7)"

This statement fails when more than one record is deleted, because your FROM clause will return more than one record, and the parameters can hold only single values:

"select @.mfkferia=m.fkferia, @.mfkcontacto = m.fkcontacto, @.mtipo = m.tipo, @.mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo"

No comments:

Post a Comment