Make Table read only

最新推荐文章于 2025-12-01 09:45:03 发布

转载最新推荐文章于 2025-12-01 09:45:03 发布 · 991 阅读

文章标签：

#table #insert #delete #application #database #sql

PL/SQL 同时被 2 个专栏收录

45 篇文章

订阅专栏

ORALCE DBA

10 篇文章

订阅专栏

本文介绍如何通过数据库触发器实现表的只读功能。首先创建了一个名为tbl_read_only的表并插入了数据，然后定义了一个触发器trg_tbl_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>