Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Why would you want to change what you have if it works? Are you encountering problems? (9i?) –  Mat Jul 28 at 5:10
    
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. –  Phil Sumner Jul 28 at 13:02
    
@Mat I don't have anything currently; I'm setting up the triggers for a new set of tables that we're building in an existing DB. But good point. –  Matt Mc Jul 29 at 17:06

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks Cap'n. 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. –  Matt Mc Jul 29 at 17:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.