Oracle Triggers – Separate or Combine Before Update and Before Insert

oracleoracle-9iplsqltrigger

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.