I have two tables- XX
and YY
with their triggers calling each other in case of an update.
The trigger on XX goes like this:
CREATE OR REPLACE TRIGGER SCMA.XX_RBIU
BEFORE INSERT OR UPDATE
ON SCMA.XX FOR EACH ROW
-- PL/SQL BLOCK
BEGIN
IF UPDATING THEN
-- Only update the YY row if the branch id has
-- been modified on the XX row
IF :NEW.BRANCH_ID <> :OLD.BRANCH_ID THEN
UPDATE YY TP
SET TP.BRANCH_ID = :NEW.BRANCH_ID
WHERE TP.XX_ID = :NEW.XX_ID;
END IF;
END IF;
...
... -- Other PL/SQL statements that do some necessary
... -- computation and do not use any SQL.
...
END;
/
And the trigger on YY goes like this:
CREATE OR REPLACE TRIGGER SCMA.YY_RBIU
BEFORE INSERT OR UPDATE
ON SCMA.YY
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_xx_type xx.xx_type_code%TYPE;
BEGIN
select x.xx_type_code
into v_xx_type
from XX x
where x.xx_id = :new.xx_id;
...
... -- Other PL/SQL statements that do some necessary
... -- computation and do not use any SQL.
...
END;
/
I know that the SELECT
statement in the trigger YY_RBIU
is giving this error. How can I code my triggers in order to avoid it?
I tried to wrap the SELECT
statement in YY_RBIU
inside an IF INSERTING THEN
block but this does not run for any updates. How can I skip this SELECT
statement if update is being called from trigger XX_RBIU
?
I also tried putting PRAGMA AUTONOMOUS_TRANSACTION
in XX_RBIU
but it is resulting into a deadlock.
I also tried referring to this, this, this and this but couldn't get a solution.
Any help is much appreciated.
One of a few reasons I avoid triggers. Basically you need to come up with a neater solution which does not have the circular trigger issues. One could be also adding the:
IF :NEW.BRANCH_ID <> :OLD.BRANCH_ID
To the YY
trigger as well. But that may mean your trigger misses some genuine updates.
A hacky solution which will work is to have a new YY_flag
table:
YY_FLAG
xx_id (Primary Key)
Then in your XX
trigger:
INSERT INTO yy_flag VALUES( :new.xx_id );
UPDATE YY ...
DELETE FROM yy_flag WHERE xx_id = :new.xx_id;
And in your YY
trigger:
BEGIN
SELECT count(1) INTO is_trigger FROM yy_flag WHERE xx_id = :new.xx_id;
IF is_trigger = 0 THEN
SELECT FROM XX
...
So basically the yy_flag
table will only contain a record for a given xx_id
is executing a trigger. And the object is to never commit a row to the yy_flag
table and oracle's normal locking should take care of all concurrency stuff.
As I said, this is very hacky, but should work if you cannot redesign your solution for whatever reason.