I'm coding a Trigger to ensure only one type of money can be set as official.
My intention is code a "BEFORE INSERT OR UPDATE" trigger.
Create separate triggers for each change event (insert,update and/or delete).
The INSERT section works fine but the problem is coding the UPDATING section because when I try to update the table I recieve ORA-04091 "mutanting table".
I need to write an Insert, Update Trigger on table A which will delete all rows from table B whose one column (say Desc) has values like the value inserted/updated in the table A's column (say Col1).
Id; //if trigger is insert at the time I call to SP1 EXEC Stored Procedure1 @record Id //if trigger is Upadeted at the time I call to SP2 EXEC Stored Procedure2 @record Id END A couple of really major things here.
First of all you should NEVER use a scalar variable in a trigger that is populated from the inserted or deleted values.
Compra AFTER INSERT, UPDATE, DELETE AS BEGIN -- id Compra is PK DECLARE @v Id Compra_Ins INT,@v Id Compra_Del INT SELECT @v Id Compra_Ins=Compra FROM Inserted SELECT @v Id Compra_Del=Compra FROM Deleted IF (@v Id Compra_Ins IS NOT NULL AND @v Id Compra_Del IS NULL) Begin -- Todo Insert End IF (@v Id Compra_Ins IS NOT NULL AND @v Id Compra_Del IS NOT NULL) Begin -- Todo Update End IF (@v Id Compra_Ins IS NULL AND @v Id Compra_Del IS NOT NULL) Begin -- Todo Delete End END Declare @Type varchar(50)=''; IF EXISTS (SELECT * FROM inserted) and EXISTS (SELECT * FROM deleted) BEGIN SELECT @Type = 'UPDATE' END ELSE IF EXISTS(SELECT * FROM inserted) BEGIN SELECT @Type = 'INSERT' END El SE IF EXISTS(SELECT * FROM deleted) BEGIN SELECT @Type = 'DELETE' END A potential problem with the two solutions offered is that, depending on how they are written, an update query may update zero records and an insert query may insert zero records.