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.

It is a good practice to have a separate trigger for After Insert and another one for After Update or there is no problem in having both in the same logic?

Also from a perspective of performance it will be better to have them isolated?

share|improve this question
    
What RDBMS are you referring to ? –  Kin Mar 12 at 15:23
    
General use. I use both PostgreSQL but also GT.M. –  hashdava Mar 12 at 15:27
    
GT.M.? What is that? And there cannot be a general answer I think, as some DBMS do not have all kinds of triggers. Some others cannot combine two triggers in one. –  ypercube Mar 12 at 16:37

2 Answers 2

From a performance point of view if the are doing the same thing then there will be no difference. If your trigger performs tests to see what needs to be done (i.e. needs to treat updated rows differently to newly inserted ones) then there could be an impact if query plans that are sub-optimal for common cases get cached and reused for those cases.

For a code maintenance PoV the decision is also split by what differences there are in the behaviours. If the action taken in each case is identical (or near identical) then it makes sense to have one piece of code than two (so if you fix a bug in one you don;t have to remember to replicate the fix in the other location) - in this case using one trigger for both actions is preferred unless there is a significant performance difference. If the actions of each trigger for the trigger are noticeably different then keep them separate, again for code clarity reasons.

Of course it also depends what database system you are using: different ones will have different performance caveats that you may need to be concerned about.

share|improve this answer

I think good practice is use COMPOUND TRIGGER:

FROM: http://www.databasejournal.com/features/oracle/compound-triggers-in-oracle-11g.html:

Oracle 11g offers a new twist on triggers, the compound trigger, a trigger that can act both before and after an update, insert or delete has occurred. This makes possible the ability in one trigger to perform processing similar to a stored procedure without having to write such a procedure to call from a traditional trigger. Compound triggers can be used to avoid the dreaded mutating table error or to process and accept or reject updates to a table based upon desired criteria.

create or replace
TRIGGER COMPOUND_TRIGGER_EXAMPLE FOR INSERT  OR UPDATE ON TEST_TABLE
COMPOUND TRIGGER

BEFORE EACH ROW IS BEGIN

    IF INSERTING THEN

     -- YOUR CODE HERE;      

    ELSIF UPDATING THEN

     -- YOUR CODE HERE;      

    END IF;

END BEFORE EACH ROW;

AFTER EACH ROW IS BEGIN

    IF INSERTING THEN

     -- YOUR CODE HERE;      

    ELSIF UPDATING THEN

     -- YOUR CODE HERE;      

    END IF;  


END COMPOUND_TRIGGER_EXAMPLE;
share|improve this answer
    
I can't downvote this answer because I don't have enough rep here yet, but you didn't actually answer the OP's question. He wanted to know if it was better to have separate triggers for insert and update, or to put flow control into a single trigger for both. Your answer relates to the before and after aspects of triggers (and is also Oracle-specific). –  Matt Mc Jul 28 at 1:26

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.