Sunday, October 18, 2009

table in read only mode OR restricitng dml on a table

HI ,

I found this somewhere and it's a nice trigger to make a table in read only mode .


16:06:06 > create table test11 (num number(20));

Table created.

16:06:13 > insert into test11 values(1);

1 row created.

16:06:26 > commit;

Commit complete.

16:06:29 > select * from test11;

NUM
----------
1

Now , create the trigger to disable the dml on the table test11

16:06:33 > create or replace trigger test11_read_only
before insert or update or delete
on test11
begin
raise_application_error (-20001, 'RAHUL has restricted DML on this table , Table TEST11 is Read Only ');
end;
/
16:06:56 2 16:06:56 3 16:06:56 4 16:06:56 5 16:06:56 6 16:06:56 7
Trigger created.

16:06:56 > insert into test11 values(1);
insert into test11 values(1)
*
ERROR at line 1:
ORA-20001: RAHUL has restricted DML on this table , Table TEST11 is Read Only
ORA-06512: at "APPS.TEST11_READ_ONLY", line 2
ORA-04088: error during execution of trigger 'APPS.TEST11_READ_ONLY'


16:07:03 >

No comments: