Trigger to prevent delete according to select [message #657770] |
Tue, 22 November 2016 05:00 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I want to stop deleting records from one table based on a select statement in another table. For example, I need to stop deleting any record from table int in case the foreign key it has for table agr has type =1 (just an example):
As I am new to triggers, I have the following inquiries:
1- The below example is not working, it actually stops deleting any record regardless of the value of type in table agt.
2- I have this need in more than one place in the DB, is it possible to have a package of triggers only to include all data protection triggers?
3- Is there any other way better than triggers to do this?
create table agt
(
id number primary key,
type number
);
create table int
(
id number primary key,
fk_id number references agt
);
insert all
INTO agt values (1,0)
INTO agt values (2,1)
INTO agt values (3,0)
INTO agt values (4,1)
INTO int values (11,1)
INTO int values (12,2)
INTO int values (13,3)
INTO int values (14,4)
select * from dual;
create or replace trigger trg_1
before delete
on int
for each row
DECLARE
itype integer;
begin
select type into itype from agt where id =:old.fk_id;
if (itype = 1)
THEN
RAISE_APPLICATION_ERROR(-20101, 'Record cannot bet deleted! Please contract DB Admin');
ROLLBACK;
END IF;
end;
delete from int where id = 3;
Thanks,
Ferro
|
|
|
Re: Trigger to prevent delete according to select [message #657771 is a reply to message #657770] |
Tue, 22 November 2016 05:12 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:1- The below example is not working, it actually stops deleting any record regardless of the value of type in table agt.
No it doesn't:
rclz>
orclz> delete from int where id = 11;
1 row deleted.
orclz> delete from int where id = 12;
delete from int where id = 12
*
ERROR at line 1:
ORA-20101: Record cannot bet deleted! Please contract DB Admin
ORA-06512: at "SCOTT.TRG_1", line 8
ORA-04088: error during execution of trigger 'SCOTT.TRG_1'
orclz> delete from int where id = 13;
1 row deleted.
orclz> delete from int where id = 14;
delete from int where id = 14
*
ERROR at line 1:
ORA-20101: Record cannot bet deleted! Please contract DB Admin
ORA-06512: at "SCOTT.TRG_1", line 8
ORA-04088: error during execution of trigger 'SCOTT.TRG_1'
orclz>
I wish you would not say "record" when you mean "row".
|
|
|
|
|
|
|
|
Re: Trigger to prevent delete according to select [message #657853 is a reply to message #657852] |
Thu, 24 November 2016 07:46 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The code that OraFerro wrote works. He did not need to post the question, I think he just confused himself with his test data.
The only issue is that he included a ROLLBACK which wouldn't work but because he placed it after the RAISE, it doesn't matter.
|
|
|
|
|