Make Table read only

Today I’ll show how we can make a table read only using database triggers


In this example, we’ll create a table, insert data in it, and then create trigger to prevent any insert, update or delete statement on table


SQL> CREATE TABLE tbl_read_only (id NUMBER);


Table created.


SQL> INSERT INTO tbl_read_only VALUES(1);


1 row created.


SQL> COMMIT;


Commit complete.


SQL> SELECT * FROM tbl_read_only;


        ID
———-
         1


SQL> CREATE TRIGGER trg_tbl_read_only BEFORE DELETE OR INSERT OR UPDATE
  2  ON tbl_read_only
  3  BEGIN
  4  RAISE_APPLICATION_ERROR(-20202,’Table Status: READ ONLY!!!’);
  5  END;
  6  /


Trigger created.


SQL> INSERT INTO tbl_read_only VALUES(2);
INSERT INTO tbl_read_only VALUES(2)
            *
ERROR at line 1:
ORA-20202: Table Status: READ ONLY!!!
ORA-06512: at “KAMRAN.TRG_TBL_READ_ONLY”, line 2
ORA-04088: error during execution of trigger ‘KAMRAN.TRG_TBL_READ_ONLY’
SQL>