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 |
|
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 #650605 is a reply to message #650604] |
Thu, 28 April 2016 05:28 |
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 #650648 is a reply to message #650630] |
Thu, 28 April 2016 11:48 |
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 |
|
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 #650691 is a reply to message #650667] |
Fri, 29 April 2016 13:06 |
|
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 |
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 #650694 is a reply to message #650693] |
Fri, 29 April 2016 13:26 |
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.
|
|
|
|
Goto Forum:
Current Time: Fri Mar 29 03:57:46 CDT 2024
|