Trigger on recursive update

Question

Trigger on recursive update

Massimo Valtolina 0

Hello

I created a trigger that only works if I update only one record at a time. If I perform a multiple update, only the first record updates me. Any ideas?

Thanks in advance

Jingyang Li 5,496 Reputation points

2023-09-18T13:47:01.29+00:00

You need to provide your sample table and data for your question. It is hard to to provide suggestion from posting.

Massimo Valtolina 0

sorry you're right. When I update the table mov_carico should I update the table anag_silos with some data

with update mov_carico set quantita=quantita*1 where id_movmag=121

works with no problems, but not works with

update mov_carico set quantita=quantita*1

this is the trigger

CREATE TRIGGER I40_Silos on [mov_carico] AFTER INSERT , UPDATE ,DELETE
AS DECLARE @ID INT ;

IF EXISTS (SELECT 0 FROM inserted)
BEGIN
   IF EXISTS (SELECT 0 FROM deleted)
		SELECT @ID = ins.id_movmag FROM INSERTED ins;
		UPDATE GS set 
		GS.quantita=C1.quantita
		,GS.Prodotto=a1.cod_magazzino
		,GS.Data_reg=c1.data_reg
		,GS.lotto=c1.varie_uno
		FROM Geco_silos GS
		inner join mov_carico c1 on c1.id_movmag=@ID
		inner join tab_cmaga d1 on d1.id_doc=c1.id_doc
		inner join anag_magazzino a1 on a1.id_magazzino=c1.id_magazzino
		where isnull(d1.flgI40_Silos,0)+isnull(a1.flgi40_silos,0)=2
		and gs.id_movmag=@id;
		IF @@ROWCOUNT=0
		INSERT INTO Geco_silos (Data_reg,Prodotto,Num_silos,Quantita,Lotto,ID_Movmag)
		SELECT c1.data_reg,a1.cod_magazzino [prodotto],1 [num_silos],c1.quantita,c1.varie_uno [lotto],c1.id_movmag
		from mov_carico c1 
		inner join tab_cmaga d1 on d1.id_doc=c1.id_doc
		inner join anag_magazzino a1 on a1.id_magazzino=c1.id_magazzino
		where isnull(d1.flgI40_Silos,0)+isnull(a1.flgi40_silos,0)=2
		AND c1.id_movmag=@ID
END ELSE 
BEGIN
	SELECT @ID = del.id_movmag FROM	DELETED del;
   DELETE FROM Geco_silos where ID_Movmag=@ID
END

Jingyang Li 5,496 Reputation points

2023-09-18T15:21:28.2733333+00:00

Thanks for posting your code.

Inside the trigger, you are using a variable that caused your problem.

You need to use to use the virtual tables(deleted/inserted) to work with your code.

https://learn.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-ver16

Jingyang Li 5,496 Reputation points

2023-09-18T13:47:01.29+00:00

You need to provide your sample table and data for your question. It is hard to to provide suggestion from posting.
Massimo Valtolina 0 Reputation points

2023-09-18T14:07:58.4466667+00:00

sorry you're right. When I update the table mov_carico should I update the table anag_silos with some data

with update mov_carico set quantita=quantita*1 where id_movmag=121

works with no problems, but not works with

update mov_carico set quantita=quantita*1

this is the trigger

CREATE TRIGGER I40_Silos on [mov_carico] AFTER INSERT , UPDATE ,DELETE AS DECLARE @ID INT ; IF EXISTS (SELECT 0 FROM inserted) BEGIN IF EXISTS (SELECT 0 FROM deleted) SELECT @ID = ins.id_movmag FROM INSERTED ins; UPDATE GS set GS.quantita=C1.quantita ,GS.Prodotto=a1.cod_magazzino ,GS.Data_reg=c1.data_reg ,GS.lotto=c1.varie_uno FROM Geco_silos GS inner join mov_carico c1 on c1.id_movmag=@ID inner join tab_cmaga d1 on d1.id_doc=c1.id_doc inner join anag_magazzino a1 on a1.id_magazzino=c1.id_magazzino where isnull(d1.flgI40_Silos,0)+isnull(a1.flgi40_silos,0)=2 and gs.id_movmag=@id; IF @@ROWCOUNT=0 INSERT INTO Geco_silos (Data_reg,Prodotto,Num_silos,Quantita,Lotto,ID_Movmag) SELECT c1.data_reg,a1.cod_magazzino [prodotto],1 [num_silos],c1.quantita,c1.varie_uno [lotto],c1.id_movmag from mov_carico c1 inner join tab_cmaga d1 on d1.id_doc=c1.id_doc inner join anag_magazzino a1 on a1.id_magazzino=c1.id_magazzino where isnull(d1.flgI40_Silos,0)+isnull(a1.flgi40_silos,0)=2 AND c1.id_movmag=@ID END ELSE BEGIN SELECT @ID = del.id_movmag FROM DELETED del; DELETE FROM Geco_silos where ID_Movmag=@ID END
Jingyang Li 5,496 Reputation points

2023-09-18T15:21:28.2733333+00:00

Thanks for posting your code.

Inside the trigger, you are using a variable that caused your problem.

You need to use to use the virtual tables(deleted/inserted) to work with your code.

https://learn.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-ver16

Trigger on recursive update

Activity