Mir Sayeed Hassan – Oracle Blog

Change the table with Read only in oracle database by using trigger

In this below scenario, Create a table & insert some data in it before restrict then create a trigger to restrict any insert/update/delete statement on table

SQL> conn mir/mir123;
Connected

SQL> create table test_read_only(eno number(10));
Table created.

SQL> insert into test_read_only values(1);
1 row created.

SQL> insert into test_read_only values(2);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test_read_only;

ENO
----------
1
2

SQL> create trigger table_test_read_only before insert or delete or update on test_read_only begin raise_application_error(-20000,'TABLE STATUS: READ ONLY !');
END;
/
Trigger created.

SQL> sho user;
USER is "MIR"

SQL> insert into test_read_only values(3);

insert into test_read_only values(3)
*
ERROR at line 1:
ORA-20000: TABLE STATUS: READ ONLY!
ORA-06512: at "MIR.TABLE_TEST_READ_ONLY", line 1
ORA-04088: error during execution of trigger 'MIR.TABLE_TEST_READ_ONLY'

============Hence the above scenario is tested & verifed in our test db============