Problem solve Get help with specific problems with your technologies, process and projects.

Trigger to restrict number of rows in a table

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

Dig Deeper on Oracle and SQL