Reputation: 89
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
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