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