I'm running Oracle DB 9i.
I have a table with various constraints to ensure data integrity. In addition to the constraints, I have triggers on BEFORE INSERT
and BEFORE UPDATE
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 BEFORE INSERT
are different than my BEFORE UPDATE
.
My question is this:
Can I have one common trigger that uses IF / THEN
, or should I make separate triggers?
For example:
-- 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 IF;
END;
/
Or:
-- 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;
/
Currently, these two triggers are combined. Should I separate them out?
Best Answer
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.