Forum Stats

  • 3,872,403 Users
  • 2,266,416 Discussions
  • 7,911,189 Comments

Discussions

Reg: Max no of triggers on a Table

infochandu
infochandu Member Posts: 30
edited Dec 29, 2013 2:17PM in SQL & PL/SQL

All,

Could you please clarify me on, How many triggers we can apply on a table. As far as i know, there is no limit from 11g on wards, but not sure, before 11g it was only 12 ( 12 types of triggers).  Could you please provide me a link where i can check this in the oracle documentation.

Thanks

Tagged:

Answers

  • Could you please clarify me on, How many triggers we can apply on a table. As far as i know, there is no limit from 11g on wards, but not sure, before 11g it was only 12 ( 12 types of triggers).  Could you please provide me a link where i can check this in the oracle documentation.

    That is not discussed in any documentation that I am aware of.

    Since you can define separate triggers on  different columns the only limiting factor will be based on the number of columns in the table.

    Don't be afraid of breaking Oracle by actually trying things; that is the best way to learn.

    Create a simple two column table and see how many triggers you can create on it. Then post your results here.

  • SKP
    SKP Member Posts: 844 Gold Badge

    Oracle Document never says the maximum number of trigger on a table.

    So we can not  say about the maximum number of trigger.

  • infochandu
    infochandu Member Posts: 30

    Thank you very much rp for your reply. I am not very clear on this below statement.

    "Since you can define separate triggers on  different columns the only limiting factor will be based on the number of columns in the table."

    How the trigger will be limiting based on the no of columns in a table.

    Thanks

  • TSharma-Oracle
    TSharma-Oracle Member Posts: 1,900 Silver Trophy

    There is no real limit.

    You can try creating a simple trigger with some simple when condition with different names just by changing when condition. Like rp said it has not been explicitly mentioned in any doc but if you just want to show somebody, you can check this metaling id and search for the "trigger".

    Recommended number of columns in a table (Doc ID 480411.1)

  • infochandu
    infochandu Member Posts: 30

    Mr  TSharma, Thank you for providing the metalink id. I'll with some hands on.

    Thanks

  • "Since you can define separate triggers on  different columns the only limiting factor will be based on the number of columns in the table."
    
    How the trigger will be limiting based on the no of columns in a table.
    

    Just a heads up for you and others that think there are shortcuts to learning.

    You aren't going to learn if you are unwilling to read the documentation or to try things for yourself.

    It is very easy to do both of those things so when people won't do them it gives the impression that they are lazy and just want other people to do their work for them.

    In an interview is is VERY easy to tell when you have one of those people in front of you.

    A simple web search for 'oracle 11g creating trigger' lists this link to the PL/SQL Langauge Reference as the second result

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#BABCIBBJ

    At the top of that link is a list of the topics covered with a link to each of them. One of those topics is 'Creating Triggers':

    Creating Triggers

    To create a trigger, use the CREATE TRIGGER statement. By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement. For information about trigger states, see Overview of Triggers.
    When using the CREATE TRIGGER statement with an interactive tool, such as SQL*Plus or Enterprise Manager, put a single slash (/) on the last line, as in Example 9-1, which creates a simple trigger for the emp table.
    Example 9-1 CREATE TRIGGER Statement
    CREATE OR REPLACE TRIGGER Print_salary_changes  BEFORE DELETE OR INSERT OR UPDATE ON emp
       FOR EACH ROW
       WHEN (NEW.EMPNO > 0)
       DECLARE  sal_diff number;
       BEGIN
      sal_diff  := :NEW.SAL  - :OLD.SAL;
       dbms_output.put('Old salary: ' || :OLD.sal);
       dbms_output.put('  New salary: ' || :NEW.sal);
       dbms_output.put_line('  Difference ' || sal_diff);
       END;
       / 

    Did you notice this line?

     WHEN (NEW.EMPNO > 0)
    

    So create a new trigger by changong the name of the trigger to Print_salary_changes1 but use this line

     WHEN (NEW.EMPNO < 0)
    

    Now do it again with a new name and use this line

     WHEN (NEW.EMPNO = 0)

    How many triggers are there now?

    Keep going as long as you like using a new name and a new line:

     WHEN (NEW.EMPNO IS NULL)
     WHEN (NEW.EMPNO = 11)
     WHEN (NEW.EMPNO = 22)
     WHEN (NEW.EMPNO = 33)
     WHEN (NEW.EMPNO = 44)
    

    How many triggers do you have now for just this one column?

    Keep going and let us know when Oracle gives you the exception 'TOO MANY TRIGGERS'.

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    infochandu wrote:
    
    All,
    
    Could you please clarify me on, How many triggers we can apply on a table. As far as i know, there is no limit from 11g on wards, but not sure, before 11g it was only 12 ( 12 types of triggers).  Could you please provide me a link where i can check this in the oracle documentation.
    
    Thanks
    

    First, I want to concur 100% with what RP0428 said about reading documentation and doing your own testing.  Every person on this forum who is generally considered by the community to be an "expert" or "guru" (regardless of whatever points badge the forum has awareded) developed that reputation by doing both - reading documentation and performing their own experiments.

    Second, in regards to limits (on triggers or anything else in oracle) I find that any actual limits are ususally so far 'out there' that to even come close to approaching them is a good indication of a serious design flaw.

    Finally, and as an extension of my previous point, RP's example of how to quickly test for limits of 'triggers on a column' is not only that, but also a good example of my second point.  If the different checks indicated by each of those individual triggers was actually a business requirement, one would combine them all into a single trigger.  I dare say that any time anyone is having to contemplate the max number of triggers, one should reconsider how those triggers are being designed and written.

This discussion has been closed.