CODEWITHSUNDEEP
oracle-databasetriggers
josef
josef

Reputation: 89

How to update a trigger in Oracle

I created a trigger like that :

create or replace
 TRIGGER "TRIG_DECLENCHEMENT_PARAM"
 AFTER UPDATE ON t_balise
 FOR EACH ROW
WHEN (NEW.no_serie like '2%')
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  P_UPDATE_BALISE(:NEW.no_serie, :NEW.date, :NEW.vitesse);
 COMMIT;
END;

P_UPDATE_BALISE it's a method in another data base which update another table. It works well like that. I want to update this Trigger and change the condition NEW.no_serie like '2%' to NEW.no_serie between 200 and 299. There is a script like Alter Trigger... which make an update of Trigger ?

Upvotes: 0

Views: 9210

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

The alter trigger statement only lets you "enable, disable, or compile a database trigger". The documentation states:

Note:
This statement does not change the declaration or definition of an existing trigger. To redeclare or redefine a trigger, use the CREATE TRIGGER statement with the OR REPLACE keywords.

You will have to modify your existing statement (which should probably be in source control anyway) to have the new condition:

create or replace
 TRIGGER "TRIG_DECLENCHEMENT_PARAM"
 AFTER UPDATE ON t_balise
 FOR EACH ROW
WHEN (NEW.no_serie between 200 and 299)
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  P_UPDATE_BALISE(:NEW.no_serie, :NEW.date, :NEW.vitesse);
 COMMIT;
END;

You've changed that comparison from a string to a number. Hopefully no_serie is actually a number and it was the old check that was incorrectly treating it as a string, and triggering on values you didn't want (2, 20, 2000 etc.).

Not directly relevant, but having this trigger as an autonomous transaction means that if the update on t_balise is rolled back, any changes made by the call to P_UPDATE_BALISE will not be rolled back - since they've been committed independently. That isn't generally something you want - the update on this table and whatever changes are made elsewhere (you said to another table; hopefully you haven't made this autonomous because the procedure is actually updating the same table) would normally be atomic and part of the same transaction. Breaking atomicity is something that is very rarely needed or desirable, so I'd check that is really what you intended and isn't just a hack to avoid a deeper problem.

Upvotes: 1

Related Questions