Forum Stats

  • 3,816,545 Users
  • 2,259,204 Discussions
  • 7,893,507 Comments

Discussions

best approach to block an insert into a table

user8101600
user8101600 Member Posts: 23
edited Jul 23, 2015 3:29PM in SQL & PL/SQL

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


















Tagged:

Best Answer

  • PhilHerring
    PhilHerring Member Posts: 169
    edited Jul 22, 2015 7:40PM Answer ✓
    • 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.

Answers

This discussion has been closed.