I have a question concerning triggers. I want to implement following trigger on table: If someone tries to insert a row and the total rows exceed a specific value (for example 100), the insertation should be avoided. Can you tell me what such a trigger should look like? I am not very experienced in Oracle.
I'm not sure I can imagine why you'd be wanting to limit a table to only have 100 records, but...
If you try to do something like a count(*) on the table you're trying to insert into in a trigger, you'll get a mutating table error. There are quite a few answers to previously asked questions on this site that have examples of how to "code around" this problem so I'll refer you to the following link(s) for several answers previously given in this forum to the mutating table problem. One or all should give you the info you need.
If you want to take another approach to limiting the number of rows in a table, why not create all the rows allowed up front but only make them "dummy" records (i.e. only put values in for the key and leave everything else blank). Then you could only give update access to the table and not allow insert privilege to anyone. That way, all that can be done is to have x number of rows and those rows can be updated as needed but no inserts will ever be allowed to occur past your pre-defined limit.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.