CREATING A READ ONLY TABLE USING 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 “myschema.TRG_TBL_READ_ONLY”, line 2
ORA-04088: error during execution of trigger ‘myschema.TRG_TBL_READ_ONLY’
SQL>


Thanks.
NJ