Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

best approach to block an insert into a table

user8101600Jul 22 2015 — edited Jul 23 2015

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

This post has been answered by PhilHerring on Jul 22 2015
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 20 2015
Added on Jul 22 2015
10 comments
2,633 views