Triggers After Insert and After Update - Good Practice

Question

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?

Answer 1

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.

Answer 2

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;