Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 398 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
best approach to block an insert into a table

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
Best 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
-
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.
-
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.
-
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.
-
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?
-
Why is the application trying to insert in the first place, whereas per your description, it shouldn't?
-
-
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.
-
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?
-
- 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.
-
I tried that and it works very well. thanks for your suggestion!