Pre Insert logic in Trigger [message #661886] |
Wed, 05 April 2017 07:05 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
I have two tables t_spp and t_condition.
Inserts against t_spp should not happen if the matching spp_id is found in t_condition table with flg value as 'Y'
Inserts against t_spp happen from other procedure using UTL_FILE so I don't want to throw error just the record shoud not be inserted.
Could you please help me how to implement above logic in tirggers.
create table t_spp (spp_name varchar2(10), sales number);
create table t_condition (spp_id Varchar2(10), flg varchar2(1));
insert into t_condition values ('SP001','Y');
insert into t_condition values ('SP002','Y');
insert into t_condition values ('SP003','Y');
-- Following insert should not insert a row into t_spp and it should not raise any error
as SP001 with flg as 'Y' is found in t_condition.
insert into t_spp values ('SP001',200);
Thank you in advance.
Regards,
Pointers
|
|
|
|
|
|
|
|
|
|
|
Re: Pre Insert logic in Trigger [message #661947 is a reply to message #661942] |
Thu, 06 April 2017 15:35 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
The following code will do what you want. The code assumes that there is only one row in t_condition for each SPP_ID. If there are multiple rows it would be easy to alter.
Build a trigger on the t_spp table
CREATE OR REPLACE TRIGGER T_spp_t1
BEFORE INSERT
ON T_spp
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
V_spp_id T_condition.Spp_id%TYPE;
BEGIN
SELECT A.Spp_id
INTO V_spp_id
FROM T_condition A
WHERE A.Flg <> 'Y' AND A.Spp_id = :new.Spp_name;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
Raise_application_error (-20000, 'The flag is set to Y', TRUE);
END T_spp_t1;
/
If you want to ignore the error, have the following code in your calling procedure.
DECLARE
Illegal_flag EXCEPTION;
PRAGMA EXCEPTION_INIT (Illegal_flag, -20000);
BEGIN
INSERT INTO T_spp VALUES ('SP001', 200);
EXCEPTION
WHEN Illegal_flag
THEN
NULL;
END;
The NULL command allows the failure to act as NOP. If you want another action to occure replace the NULL; with your handling code. If you just run the command
INSERT INTO T_spp VALUES ('SP001', 200);
you will get the following error stack returned
ORA-20000: The flag is set to Y
ORA-06512: at "T_SPP_T1", line 13
ORA-01403: no data found
ORA-04088: error during execution of trigger 'T_SPP_T1'
[Updated on: Thu, 06 April 2017 15:37] Report message to a moderator
|
|
|
Re: Pre Insert logic in Trigger [message #661948 is a reply to message #661947] |
Thu, 06 April 2017 21:09 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Thank you one and everyone for your time on this.
@Bill, I think your solution seems to me a possible intuitive code for me.
Let me share your idea to our team.
Thank you again.
Regards,
Pointers
|
|
|