Home » SQL & PL/SQL » SQL & PL/SQL » Trigger to prevent delete according to select (11.0.2.10)
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
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jul 10 01:22:36 CDT 2025
|