best approach to block an insert into a table

user8101600

Hello, I have been trying to write a trigger before insert to block insert into a table - ideally I want to stop insert instead of allowing insert and rollback.     Can someone help me to understand why would it still insert the row given there is a trigger to rollback?     What can be done to stop an insert besides what I have done and which does not work.   thanks.

Here is what I tried:

SQL> CREATE TABLE t (
testcol VARCHAR2(20));  2


Table created.


SQL> CREATE OR REPLACE TRIGGER bi_t BEFORE INSERT ON t
  2  DECLARE
  3    PRAGMA AUTONOMOUS_TRANSACTION;
  4  BEGIN
  5     ROLLBACK;
  6     DBMS_OUTPUT.PUT_LINE('ROLLBACK DONE');
  7  END bi_t;
  8  /


Trigger created.


SQL> insert into t values ('1');
SP2-0734: unknown command beginning "insert in..." - rest of line ignored.
SQL> insert into t values ('1');
ROLLBACK DONE


1 row created.


SQL> select * from t;


TESTCOL
--------------------
1

PhilHerring

• Rename the table.
• Create a view, with the original table name, that selects all rows from the renamed table.
• Create an INSTEAD OF trigger on the view that does nothing.

There are variants on this. E.g., depending on your exact situation, you might be able to use a synonym to redirect the insert to the view, rather than changing the table name.

John Stegeman

Triggers cannot rollback the transaction. Period. Full Stop.

You are using an autonomous transaction (which is completely separate from the transaction doing the insert) and rolling it back.

A better thought - why not just don't grant insert on the table?

Alternatively, you could use an insert trigger that raises an error.

odie_63

What can be done to stop an insert besides what I have done and which does not work. 

For example, raising an exception in the trigger.

Saubhik

Trigger is not the proper way, However you can try something like :

CREATE OR REPLACE TRIGGER bi_t BEFORE INSERT ON t 

    BEGIN 

      RAISE_APPLICATION_ERROR(-20001,'Insert Prohibited');

END bi_t; 

It is better if you just revoke the privileges.

user8101600

This is helpful.  thanks.  i have an application tries to insert, however, developers do not wants to see "ERR" in the server side, how do I suppress the "Insert Prohibited" error from the application's view?

odie_63

Why is the application trying to insert in the first place, whereas per your description, it shouldn't?

Etbin

Maybe VPD:  http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_rls.htm#ARPLS052

Regards

Etbin

John Stegeman

So, application developers wrote code that does something prohibited but do not want to see any errors when they do that which is prohibited?

That's like a thief saying that they want to be able attempt to steal something from the store, but when they are caught trying, they want to not be arrested.

user8101600

exactly, and I like your synonym.   this is unfortunate, we'll have to temporary solve this problem on the database side before application developers can change the code not to insert.   

any idea how to stop insert but suppress application to see error?

PhilHerring

• Rename the table.
• Create a view, with the original table name, that selects all rows from the renamed table.
• Create an INSTEAD OF trigger on the view that does nothing.

There are variants on this. E.g., depending on your exact situation, you might be able to use a synonym to redirect the insert to the view, rather than changing the table name.

user8101600

I tried that and it works very well.  thanks for your suggestion!