Home » SQL & PL/SQL » SQL & PL/SQL » Trigger to prevent delete based on SQL statement (11.0.2.10)
Trigger to prevent delete based on SQL statement [message #650595] Thu, 28 April 2016 03:33 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi All,

I am very bad with triggers, and I have the following case where I need to prevent deleting from one table based on an SQL statement that checks another table.
The case is as follows:

create table test_trans
  (
    serial number primary key,
    amount number,
    trn_date date,
    status_id number
  )

create table test_interest
  (
  
    serial number primary key,
    amount number,
    int_date date,
    fk_transid references test_trans
  )
  
  ;

insert ALL
  into test_trans values (1, 1200, to_date('01-01-2016','dd-mm-yyyy'),1)
  into test_trans values (2, 100, to_date('01-01-2016','dd-mm-yyyy'),0)

  into test_interest values (10, 1200, to_date('01-01-2016','dd-mm-yyyy'),1)
  into test_interest values (20, 100, to_date('01-01-2016','dd-mm-yyyy'),2)
  into test_interest values (30, 1050, to_date('01-01-2016','dd-mm-yyyy'),null)
select * from dual;



I tried a before delete trigger but failed as I could not avoid the recursive trigger call (delete from a before delete trigger)!



CREATE OR REPLACE TRIGGER tst_trg_interest_delete
before delete  ON test_interest
REFERENCING NEW AS n      

FOR EACH ROW
DECLARE
   status number;
BEGIN
   SELECT status_id INTO status FROM test_trans WHERE serial = :n.fk_transid;
   IF status <> 0  THEN
       ----> Allow delete
       delete from TEST_INTEREST where SERIAL = :n.serial;
   ELSE
       ----> Prevent delete and raise error
       Raise_application_error(-20000, 'Invalid delete record' || :n.serial || ' has a transaction with status processed');
   END IF;
END;


Thanks,
Ferro
Re: Trigger to prevent delete based on SQL statement [message #650597 is a reply to message #650595] Thu, 28 April 2016 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Triggers are designed to allow you to run code in addition to the DML that caused them to fire in the first place. They don't override the original DML. So why are you trying to write a delete statement in the trigger at all?
Re: Trigger to prevent delete based on SQL statement [message #650599 is a reply to message #650597] Thu, 28 April 2016 04:16 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear Cookiemonster,

Thanks for your reply.

I tried removing the delete clause but I could not delete any row from the table with the following trigger code:

CREATE OR REPLACE TRIGGER tst_trg_interest_delete
before delete  ON test_interest
REFERENCING NEW AS n      

FOR EACH ROW
DECLARE
   status number;
BEGIN
   SELECT status_id INTO status FROM test_trans WHERE serial = :n.fk_transid;
   IF status = 1  THEN
        Raise_application_error(-20000, 'Invalid delete record' || :n.serial || ' has a transaction with status processed');
   END IF;
END;


Thanks,
Ferro
Re: Trigger to prevent delete based on SQL statement [message #650600 is a reply to message #650599] Thu, 28 April 2016 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Of course, your fk_transid in test_interest either matches a test_trans with status 1 or does not match any test_trans so you raise a no_data_found exception!

[Updated on: Thu, 28 April 2016 05:46]

Report message to a moderator

Re: Trigger to prevent delete based on SQL statement [message #650602 is a reply to message #650600] Thu, 28 April 2016 04:54 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
As Michel states, if it's not working then you should be getting an error. If you want our help fixing it you need to tell us what the error is.
Re: Trigger to prevent delete based on SQL statement [message #650604 is a reply to message #650602] Thu, 28 April 2016 05:14 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear Michel and Cookiemonster,

Actually I receive no error but always receive " 0 rows deleted [0.007s]" even when
delete from test_interest where serial = 10 
.

Thanks,
Ferro
Re: Trigger to prevent delete based on SQL statement [message #650605 is a reply to message #650604] Thu, 28 April 2016 05:28 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then there are 0 rows in test_interest where serial = 10 or you've got an exception handler somewhere hiding errors thrown by the trigger.

The only way a trigger can alter the number of rows deleted in a table by a delete statement against that table is by raising an error. If that happens the whole statement is rolled back and the program that ran the delete gets the error.
Re: Trigger to prevent delete based on SQL statement [message #650610 is a reply to message #650605] Thu, 28 April 2016 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I bet for a WHEN OTHERS he did not post to prevent me from saying it is stupid. Wink

Re: Trigger to prevent delete based on SQL statement [message #650613 is a reply to message #650605] Thu, 28 April 2016 05:58 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear cookiemonster,

Right you are, a no data exception is raised:
SQL> delete from test_interest where serial = 20;
delete from test_interest where serial = 20
            *
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "TST_TRG_INTEREST_DELETE", line 4
ORA-04088: error during execution of trigger 'TST_TRG_INTEREST_DELETE'



I tried to avoid the no data exception by checking on the fk value first as below:

CREATE OR REPLACE TRIGGER tst_trg_interest_delete
before delete  ON test_interest
REFERENCING NEW AS n      
FOR EACH ROW
DECLARE
   transid number;
   status number;
BEGIN
   status := 0;
   select fk_transid into transid from test_interest where SERIAL = :n.serial;
   if transid is not null then
    SELECT status_id INTO status FROM test_trans WHERE serial = :n.fk_transid;
   end if;
   IF status = 1  THEN
        Raise_application_error(-20000, 'Invalid delete, record: ' || :n.serial || ' has a transaction with status processed');
   END IF;
END;



but I got:
Quote:


1 ORA-04091: table TEST_INTEREST is mutating, trigger/function may not see it
ORA-06512: at "TST_TRG_INTEREST_DELETE", line 6
ORA-04088: error during execution of trigger 'TST_TRG_INTEREST_DELETE' SQL.sql 58 1


Thanks,
Ferro
Re: Trigger to prevent delete based on SQL statement [message #650614 is a reply to message #650613] Thu, 28 April 2016 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Right you are, a no data exception is raised:


This is what I said (and prove you didn't post the actual code).

Quote:
I tried to avoid the no data exception by checking on the fk value first as below:


Wrong approach. Let it raise and trap it, this what EXCEPTION is made for.

Re: Trigger to prevent delete based on SQL statement [message #650615 is a reply to message #650614] Thu, 28 April 2016 06:22 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi Michel,

Smile
Quote:

This is what I said (and prove you didn't post the actual code).

It was the right code but I ran it from a tool called dbForge the suppressed the exception for a reason that I do not know, then I used sqlplus after receiving your reply.

Quote:

Let it raise and trap it


I think I understand what you mean and I though of writing a WHEN EXCEPTION block to handle it, but I actually want the system to proceed with normal delete in case no FK is there (which means that status_id is not 1 for sure).

Thanks,
Ferro
Re: Trigger to prevent delete based on SQL statement [message #650625 is a reply to message #650615] Thu, 28 April 2016 07:39 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want it to carry on with the delete if there's no corresponding record in the other table then all you need to do is put the exception handler in the trigger.
Re: Trigger to prevent delete based on SQL statement [message #650627 is a reply to message #650615] Thu, 28 April 2016 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
It was the right code but I ran it from a tool called dbForge the suppressed the exception for a reason that I do not know


This is why we ask to ALWAYS use SQL*Plus when you post a question.

Re: Trigger to prevent delete based on SQL statement [message #650630 is a reply to message #650615] Thu, 28 April 2016 08:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
OraFerro wrote on Thu, 28 April 2016 12:22

It was the right code but I ran it from a tool called dbForge the suppressed the exception for a reason that I do not know


That'd be a bug in dbForge
Re: Trigger to prevent delete based on SQL statement [message #650648 is a reply to message #650630] Thu, 28 April 2016 11:48 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
You may not operate on the same table the trigger is on when doing an "for each row" trigger. It is NOT allowed. It generates a mutating trigger error. It will NEVER work. There are ways around it using a memory array, a before statement trigger, for each row trigger, and an after statement trigger but a normal trigger will NEVER work.

[Updated on: Thu, 28 April 2016 12:25]

Report message to a moderator

Re: Trigger to prevent delete based on SQL statement [message #650660 is a reply to message #650615] Thu, 28 April 2016 18:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
When deleting, you need to reference :OLD... values in the trigger, not :NEW... values.

To deal with no_data_found, you can either handle the exception executing null or you can select a count instead, as I have demonstrated below.

SCOTT@orcl> create table test_trans
  2    (
  3  	 serial number primary key,
  4  	 amount number,
  5  	 trn_date date,
  6  	 status_id number
  7    )
  8  /

Table created.

SCOTT@orcl> create table test_interest
  2    (
  3  
  4  	 serial number primary key,
  5  	 amount number,
  6  	 int_date date,
  7  	 fk_transid references test_trans
  8    )
  9  /

Table created.

SCOTT@orcl> 
SCOTT@orcl> insert ALL
  2    into test_trans values (1, 1200, to_date('01-01-2016','dd-mm-yyyy'),1)
  3    into test_trans values (2, 100, to_date('01-01-2016','dd-mm-yyyy'),0)
  4  
  5    into test_interest values (10, 1200, to_date('01-01-2016','dd-mm-yyyy'),1)
  6    into test_interest values (20, 100, to_date('01-01-2016','dd-mm-yyyy'),2)
  7    into test_interest values (30, 1050, to_date('01-01-2016','dd-mm-yyyy'),null)
  8  select * from dual;

5 rows created.

SCOTT@orcl> 
SCOTT@orcl> 
SCOTT@orcl> 
SCOTT@orcl> CREATE OR REPLACE TRIGGER tst_trg_interest_delete
  2    BEFORE DELETE ON test_interest
  3    FOR EACH ROW
  4  DECLARE
  5    status  NUMBER;
  6  BEGIN
  7    SELECT COUNT (status_id) INTO status FROM test_trans WHERE serial = :OLD.fk_transid AND status_id = 1;
  8    IF status > 0  THEN
  9  	 Raise_application_error
 10  	   (-20000, 'Invalid delete record ' || :OLD.serial || ' has a transaction with status processed');
 11    END IF;
 12  END tst_trg_interest_delete;
 13  /

Trigger created.

SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> delete from test_interest where serial = 10
  2  /
delete from test_interest where serial = 10
            *
ERROR at line 1:
ORA-20000: Invalid delete record 10 has a transaction with status processed
ORA-06512: at "SCOTT.TST_TRG_INTEREST_DELETE", line 6
ORA-04088: error during execution of trigger 'SCOTT.TST_TRG_INTEREST_DELETE'


SCOTT@orcl> delete from test_interest where serial = 20
  2  /

1 row deleted.

SCOTT@orcl> delete from test_interest where serial = 30
  2  /

1 row deleted.


Re: Trigger to prevent delete based on SQL statement [message #650664 is a reply to message #650595] Fri, 29 April 2016 02:00 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Thanks alot I was about to try selecting nvl (status, -1) to avoid the exception but count is better.
Thanks all,
Ferro
Re: Trigger to prevent delete based on SQL statement [message #650667 is a reply to message #650664] Fri, 29 April 2016 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No COUNT is not better, it is the way to program in another language but in PL/SQL the EXCEPTION IS the way to program. This language is made to work with exception, exceptions are normal.
But you are free to bad program.

Re: Trigger to prevent delete based on SQL statement [message #650691 is a reply to message #650667] Fri, 29 April 2016 13:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Michel,

Are you suggesting that the trigger below is a better method than the trigger that I provided using count? If so, then please explain why. If not, then please demonstrate what you are suggesting.

SCOTT@orcl> create table test_trans
  2    (
  3  	 serial number primary key,
  4  	 amount number,
  5  	 trn_date date,
  6  	 status_id number
  7    )
  8  /

Table created.

SCOTT@orcl> create table test_interest
  2    (
  3  
  4  	 serial number primary key,
  5  	 amount number,
  6  	 int_date date,
  7  	 fk_transid references test_trans
  8    )
  9  /

Table created.

SCOTT@orcl> 
SCOTT@orcl> insert ALL
  2    into test_trans values (1, 1200, to_date('01-01-2016','dd-mm-yyyy'),1)
  3    into test_trans values (2, 100, to_date('01-01-2016','dd-mm-yyyy'),0)
  4  
  5    into test_interest values (10, 1200, to_date('01-01-2016','dd-mm-yyyy'),1)
  6    into test_interest values (20, 100, to_date('01-01-2016','dd-mm-yyyy'),2)
  7    into test_interest values (30, 1050, to_date('01-01-2016','dd-mm-yyyy'),null)
  8  select * from dual;

5 rows created.

SCOTT@orcl> 
SCOTT@orcl> 
SCOTT@orcl> 
SCOTT@orcl> CREATE OR REPLACE TRIGGER tst_trg_interest_delete
  2    BEFORE DELETE ON test_interest
  3    FOR EACH ROW
  4  DECLARE
  5    status  NUMBER;
  6  BEGIN
  7    BEGIN
  8  	 SELECT status_id INTO status FROM test_trans WHERE serial = :OLD.fk_transid;
  9    EXCEPTION
 10  	 -- allow deletion when no matching record found
 11  	 WHEN NO_DATA_FOUND THEN NULL;
 12    END;
 13    IF status = 1 THEN
 14  	 -- prevent deletion when status = 1
 15  	 Raise_application_error
 16  	   (-20000, 'Invalid delete record ' || :OLD.serial || ' has a transaction with status processed');
 17    END IF;
 18    -- allow deletion when status other than 1
 19  END tst_trg_interest_delete;
 20  /

Trigger created.

SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> delete from test_interest where serial = 10
  2  /
delete from test_interest where serial = 10
            *
ERROR at line 1:
ORA-20000: Invalid delete record 10 has a transaction with status processed
ORA-06512: at "SCOTT.TST_TRG_INTEREST_DELETE", line 12
ORA-04088: error during execution of trigger 'SCOTT.TST_TRG_INTEREST_DELETE'


SCOTT@orcl> delete from test_interest where serial = 20
  2  /

1 row deleted.

SCOTT@orcl> delete from test_interest where serial = 30
  2  /

1 row deleted.


Re: Trigger to prevent delete based on SQL statement [message #650692 is a reply to message #650691] Fri, 29 April 2016 13:14 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
You did it wrong.

CREATE OR REPLACE TRIGGER Tst_trg_interest_delete
   BEFORE DELETE
   ON Test_interest
   FOR EACH ROW
DECLARE
   Status   NUMBER;
BEGIN
   SELECT Status_id
     INTO Status
     FROM Test_trans
    WHERE Serial = :OLD.Fk_transid;

   IF Status = 1
   THEN
      -- prevent deletion when status = 1
      Raise_application_error (
         -20000,
            'Invalid delete record '
         || :OLD.Serial
         || ' has a transaction with status processed');
   END IF;
-- allow deletion when status other than 1
EXCEPTION
   -- allow deletion when no matching record found
   WHEN NO_DATA_FOUND
   THEN
      NULL;
END Tst_trg_interest_delete;
/

[Updated on: Fri, 29 April 2016 13:15]

Report message to a moderator

Re: Trigger to prevent delete based on SQL statement [message #650693 is a reply to message #650692] Fri, 29 April 2016 13:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Why do you consider mine wrong? The result is the same whether you trap the exception in a separate block or in the main part of the program. I demonstrated that it works when there is no data found. Also, I still don't see how this is better than my previous trigger using count.

[Updated on: Fri, 29 April 2016 13:22]

Report message to a moderator

Re: Trigger to prevent delete based on SQL statement [message #650694 is a reply to message #650693] Fri, 29 April 2016 13:26 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
You are relaying on status being null if the exception block is fired because it will keep falling through the trigger. My version will get out immediately or if you put RETURN in the exception block instead of null, it would also not waste time checking the value of status. What you have will work, but I tend to make my code as efficient as possible.
Re: Trigger to prevent delete based on SQL statement [message #650695 is a reply to message #650694] Fri, 29 April 2016 13:30 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Good points. Thanks for the explanation.
Previous Topic: Update Query
Next Topic: error creating external via dbms_utility.exec_ddl_statement@myDB
Goto Forum:
  


Current Time: Fri Mar 29 03:57:46 CDT 2024