Oracle plsql if updating
to ensure that necessary data goes into the table, in some cases allowing the application layer to omit the information from their queries, and in some cases forcing that it be present.The actions that I need to do on my -- Scenario 1 CREATE OR REPLACE TRIGGER my_trg BEFORE INSERT OR UPDATE ON my_table FOR EACH ROW BEGIN IF UPDATING THEN IF : NEW.my_col IS NULL THEN raise_application_error(...); END IF; IF : NEW.my_col2 IS NULL THEN : NEW.my_col2 := (...); END IF; END IF; IF INSERTING THEN IF : NEW.my_col3 IS NULL THEN SELECT my_seq.
NEXTVAL INTO : NEW.my_col3 FROM DUAL; END IF; IF : NEW.my_col4 IS NULL THEN (...) END IF; END; / CREATE OR REPLACE TRIGGER my_trg2 BEFORE UPDATE ON my_table FOR EACH ROW BEGIN IF : NEW.my_col IS NULL THEN raise_application_error(...); END IF; IF : NEW.my_col2 IS NULL THEN : NEW.my_col2 := (...); END IF; END; / I can't see any major advantage to one or the other.The separate approach is slightly less risky in case of updates (e.g.if you want to change the INSERT trigger you can't accidentally break the UPDATE one if they're separate), but otherwise - much of a muchness.The actions you're performing on the data is technically different between the insert/update portions, but what about the business logic difference?IMO, your best bet is to contain a functional unit of work inside of a trigger, so that there is no discrepancy in what that trigger does.Many applications update all of the columns in a table -- setting them to their old value, rather then trying to figure out exactly which columns have been updated by the client. create or replace trigger test_trigger 2 after update of x,y on test 3 for each row 4 begin 5 if updating( 'X' ) then 6 dbms_output.put_line( 'X is being updated' ); 7 end if; 8 9 if updating( 'Y' ) then 10 dbms_output.put_line( 'Y is being updated' ); 11 end if; 12 end; 13 / Trigger created.
SQL Notice in the last update it indicated that x and y both were being updated (which they were), but the update didn't change the value (both x and y were 1 prior to the update which set them to 1). You can use updating (column) but it will only tell you that the column itself is being updated and not if the value has changed.
If you need to determine if the value is changing, you'll still need to code comparison logic for :old and :new values.
In my experience, Developers/Business Analysts often tend to think in transaction workflows.
I have found this to be especially helpful when I or others have to troubleshoot the consequences of user data corruption as it relates to triggers being fired - the less triggers you have to try to piece together the logic for, the easier it is to see how data was manipulated. Obviously the separate triggers are more efficient because you're doing fewer logical checks, but you have a decent point on the benefit of having fewer triggers.
In my particular case, I don't think that separating them out will introduce too much complexity, but I will accept your answer as it's the kind of info I was looking for.
By submitting your email address, you agree to receive emails regarding relevant topic offers from Tech Target and its partners. Contact Tech Target at 275 Grove Street, Newton, MA. It works, but it won't tell you if the value changed, only that the column is being updated (even if the update doesn't change the value).