Oracle plsql if updating
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.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.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.
There are 2 syntaxes for an update query in Oracle depending on whether you are performing a traditional update or updating one table with data from another table.
You can also perform more complicated updates in Oracle.
NEXTVAL INTO : NEW.my_col3 FROM DUAL; END IF; IF : NEW.my_col4 IS NULL THEN (...) END IF; END IF; END; / -- Scenario 2 CREATE OR REPLACE TRIGGER my_trg1 BEFORE INSERT ON my_table FOR EACH ROW BEGIN 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.