My answer was: "Heck if I know. Check the Oracle documentation."
To which he replied: "Actually Oracle documentation does not mention anything about this. But this a favorite question asked during interviews for PL/SQL jobs in India."
A favorite interview question? How absurd! When I interview a person for a job as a PL/SQL developer (which I admit I have not done all that often). I am much more interested in:
Problem solving skills (MOST IMPORTANT): Can you think logically? If you are not well versed in symbolic logic (whether or not you know it as such), then it will be very difficult to solve your programming puzzles, debug your code, and understand other people's code.
Language familiarity (SECOND MOST IMPORTANT): Do you know about and have experience with critical, non-beginner features of the PL/SQL language, such as collections, FORALL, BULK COLLECT, AUTHID, autonomous transactions, etc.?
Awareness of limitations like the maximum number of triggers doesn't play any role in writing high quality code. In fact, one might argue that a brain filled with such arbitrary and irrelevant data is less likely to have a solid grasp of fundamentals and principles.
But if you really want to know the answer, the best way to check/prove the limit on the number of triggers you can define on a table is to test it with code (documentation can always be wrong, but the code is...the code...the "reality" within this particular chunk of cyberspace). Here's a script you can use to do just that:
SET SERVEROUTPUT ON
CREATE TABLE limits_table (n NUMBER)
n PLS_INTEGER := 1;
EXECUTE IMMEDIATE 'create or replace trigger limits_table'
|| n ||
' before insert on limits_table for each row begin null; end;';
DBMS_OUTPUT.put_line ('Created ' || n || ' triggers!');
n := n + 1;
SELECT COUNT ( * )
WHERE trigger_name LIKE 'LIMITS_TABLE%'
FOR rec IN (SELECT *
WHERE trigger_name LIKE 'LIMITS_TABLE%')
EXECUTE IMMEDIATE 'drop trigger ' || rec.trigger_name;
The result on Oracle Database 11g Release 1 was interesting: I ran this script while flying from Chicago down to Florida for Collaborate09. I started this script and many minutes later, I had to turn off the laptop since we were landing. By that time, over 5000 triggers had been created on the same table. Maybe I will run this later and let it run overnight, but really what's the point? No one is ever going to create that many triggers on a single table. Perhaps there is a lower, hard limit like 12 on Oracle Database 10g or earlier versions. Why don't you run this script on your database and let me know what you get?
To conclude, I suggest that if your interviewer asks you silly questions like this one, you should answer as follows:
"Why do you think it matters if I know this information? How will it help me be more successful on your team? And how do you know you've even got the right answer? Let's build a script to prove that the answer is right!"
Then whip out your laptop, throw together a script and show what a good problem solver you are. I guarantee you will have a much better shot at your job with this kind of answer.