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 Go to next message
OraFerro
Messages: 331
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 Go to previous messageGo to next message
John Watson
Messages: 7622
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 #657772 is a reply to message #657771] Tue, 22 November 2016 05:15 Go to previous messageGo to next message
John Watson
Messages: 7622
Registered: January 2010
Location: Global Village
Senior Member
Also, you cannot use ROLLBACK in a trigger. It works only because the RAISE_APPLICTION_ERROR prevents the ROLLBACK from executing.
Re: Trigger to prevent delete according to select [message #657827 is a reply to message #657770] Wed, 23 November 2016 11:25 Go to previous messageGo to next message
ALEXWE
Messages: 11
Registered: August 2016
Location: Germany
Junior Member
Just write an API a Procedure p_delete_from_int this would be my preferred way.

When you want to stay with the "normal delete statement" i can only think of triggers...
1. rename the Table to int_tab.
2. create a editionable view int.
3. finally use an instead of delete Trigger on this VIEW (note: cant be used on tables)


Re: Trigger to prevent delete according to select [message #657842 is a reply to message #657827] Thu, 24 November 2016 03:17 Go to previous messageGo to next message
cookiemonster
Messages: 13292
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm not sure why the instead of trigger would be an improvement over a trigger on the table itself.
Re: Trigger to prevent delete according to select [message #657843 is a reply to message #657842] Thu, 24 November 2016 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... And why an editionable view which means you editions-enable the user and enter in the editions world?

Re: Trigger to prevent delete according to select [message #657852 is a reply to message #657843] Thu, 24 November 2016 07:41 Go to previous messageGo to next message
ALEXWE
Messages: 11
Registered: August 2016
Location: Germany
Junior Member
Does a Trigger on the Table itself work?
You can`t rollback in a Trigger, nor you can insert the delete ROW.
Do I miss something?

An editionable view isn`t a good solution, just stay with a Procedure.
Re: Trigger to prevent delete according to select [message #657853 is a reply to message #657852] Thu, 24 November 2016 07:46 Go to previous messageGo to next message
John Watson
Messages: 7622
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.
Re: Trigger to prevent delete according to select [message #657854 is a reply to message #657852] Thu, 24 November 2016 08:01 Go to previous messageGo to next message
cookiemonster
Messages: 13292
Registered: September 2008
Location: Rainy Manchester
Senior Member
ALEXWE wrote on Thu, 24 November 2016 13:41

You can`t rollback in a Trigger, nor you can insert the delete ROW.
OP doesn't need to do either of those. As John said the trigger actually works just fine.
Re: Trigger to prevent delete according to select [message #657855 is a reply to message #657854] Thu, 24 November 2016 08:16 Go to previous message
ALEXWE
Messages: 11
Registered: August 2016
Location: Germany
Junior Member
Ah, Thanks.
Previous Topic: Please provide best approach to design ETL in Oracle.
Next Topic: Trigger to prevent update/delete/insert operation
Goto Forum:
  


Current Time: Mon Oct 22 18:44:01 CDT 2018