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.
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.
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.
In my experience, Developers/Business Analysts often tend to think in transaction workflows.
PUT_LINE('Record successfully inserted into emp_backup table'); END; Second Problem: We want o update a record in the ’emp_backup’ table if a corresponding record is updated in the ’emp’ table.As we’ve already created both the tables in above example, we will directly write a trigger for ‘AFTER UPDATE’.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.A database trigger is a stored procedure that automatically executes whenever an event occurs. Oracle initiates an ‘AFTER INSERT’ trigger after an insert event has been occurred and an ‘AFTER UPDATE’ trigger after an update event has been occurred.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.