CODEWITHSUNDEEP
oracle-databasetriggersalter
Radomir
Radomir

Reputation: 9

Alter trigger from another trigger

I wish to have alter trigger under another trigger.

CREATE TRIGGER izmeni_naziv_kupac
AFTER UPDATE OF naziv ON Kupac
FOR EACH ROW
DECLARE
    novi_naziv varchar2(255);
    pibK INT;    
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER ZabranaPromeneNazivaKupaca DISABLE';

    pibK := :new.pib;
    novi_naziv := :new.naziv;
    
        UPDATE Porudzbina
        SET naziv_kupca = novi_naziv
    WHERE kupac_PIB = pibK;
EXECUTE IMMEDIATE 'ALTER TRIGGER ZabranaPromeneNazivaKupaca ENABLE';
END;

Problem is that system sent error message:

ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYSTEM.IZMENI_NAZIV_KUPAC", line 5
ORA-04088: error during execution of trigger 'SYSTEM.IZMENI_NAZIV_KUPAC'

Upvotes: 0

Views: 158

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

This is not a practical way to manage trigger activation, and for the same reason, I'd advise against using 'autonmous' to work around it because its very rare to want to turn off a trigger for all users, because typically that breaks an application.

More likely is that you want to disable a trigger for this current session, for example, to perform some sort of maintenance activity etc. For this task, you would code the trigger to have a flag in order to let you control its execution.

Thus your code would be along the lines of (psuedo-code):

trigger my_trigger
before insert ...
begin
  if i_am_meant_to_fire then
     ...
     ... my normal code
     ...
  end if;
end;

where you then have session level control over when the trigger should take any action. It is always enabled, but might not do anything based on the flag.

A full walkthrough of some options here

https://youtu.be/P1OFbNhgT1k

Upvotes: 0

Related Questions