Home » SQL & PL/SQL » SQL & PL/SQL » Pre Insert logic in Trigger (Oracle 11.2.0.3)
Pre Insert logic in Trigger [message #661886] Wed, 05 April 2017 07:05 Go to next message
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 #661888 is a reply to message #661886] Wed, 05 April 2017 07:17 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What code have you tried so far?

Re: Pre Insert logic in Trigger [message #661889 is a reply to message #661888] Wed, 05 April 2017 07:22 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Ho John,

I tried, you know what I could make the values to NULL using :new but not sure about excluding rows. I don't want insert null reocords.

Regards,
Pointers

[Updated on: Wed, 05 April 2017 07:24]

Report message to a moderator

Re: Pre Insert logic in Trigger [message #661890 is a reply to message #661889] Wed, 05 April 2017 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can't stop an insert in a trigger without raising an exception.
You have to choose another way.

Re: Pre Insert logic in Trigger [message #661891 is a reply to message #661889] Wed, 05 April 2017 07:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You could cover the table with a view, and then use an INSTEAD OF trigger. A bit silly, but it would work.
Re: Pre Insert logic in Trigger [message #661892 is a reply to message #661891] Wed, 05 April 2017 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or you could check the condition in the loading procedure.

Re: Pre Insert logic in Trigger [message #661895 is a reply to message #661892] Wed, 05 April 2017 11:37 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
or you could use a procedure for the insert instead of the bare insert. Then you could just return if the condition is meant.
Re: Pre Insert logic in Trigger [message #661936 is a reply to message #661886] Thu, 06 April 2017 10:41 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
It is not a pretty method, but I found it mentioned on several Websites. Give it
a try and see if it meets your requirements

A couple of possible issues I have found because there is a Delete DML in the triggers it is possible
that the Trigger could Freeze because of a Deadlock condition. Also if there is an item with the "flg"
set to 'N' and it is changed to 'Y' it will be removed from "t_spp" the next time there is a Insert Action
that causes the Triggers to execute.

Link:

http://dba.stackexchange.com/questions/24047/oracle-abort-within-a-before-insert-trigger-without-throwing-an-exception


Components:

1. Create a Global Temporary Table to hold the list of "spp_name" Column records that are
rejected because they are listed in the in the "t_condition Table" - "spp_id" Column. This table is only
temporary and the contents disapear when the session ends.

2. Create Before Insert Row Level Trigger on the "t_spp" that will Test if the new record with the
"spp_name" exist in the "t_condition Table" and record the Failures in the Global Temporary. This
allows the Insert Action to Complete. This step loads the data.

3. Create After Insert Table Level Trigger on the "t_spp" that will remove the Invalid records from
the "t_spp" based on the data in the Global Temporary Table "gt_t_spp". This is the Clean Up/Delete
Phase of the process. This step deletes the data that was loaded in step 2, but should not be loaded
because it is marked in the "t_condition" Table with the "flg" = 'Y'



DROP TABLE gt_t_spp;

CREATE GLOBAL TEMPORARY TABLE gt_t_spp
  (spp_name VARCHAR2(10) NOT NULL)
   ON COMMIT DELETE ROWS;


CREATE OR REPLACE TRIGGER trg_bi_t_sup
BEFORE INSERT
ON t_spp
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

  tst_cnt   NUMBER(1) := 0;
  
BEGIN

  SELECT COUNT(*)
  INTO   tst_cnt
  FROM   t_condition t
  WHERE  t.spp_id = :new.spp_name
  AND    t.flg    = 'Y';

  DBMS_OUTPUT.PUT_LINE(tst_cnt);

  IF (tst_cnt > 0) THEN
    INSERT INTO gt_t_spp (spp_name)
      VALUES             (:new.spp_name);
  END IF;

END;
/

SHOW ERRORS;



CREATE OR REPLACE TRIGGER trg_ai_t_sup
  AFTER INSERT
  ON t_spp
BEGIN

  DELETE t_spp t
  where t.spp_name IN (SELECT g.spp_name
                       FROM gt_t_spp g);

END;
/

SHOW ERRORS;


Re: Pre Insert logic in Trigger [message #661942 is a reply to message #661936] Thu, 06 April 2017 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This won't work in multi-user environment.
And using COUNT(*) to check if a row exists is a very bad implementation.
Why do you want to count ALL rows when you just want to know if there is ONE?

Re: Pre Insert logic in Trigger [message #661947 is a reply to message #661942] Thu, 06 April 2017 15:35 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Avoid Distinct
Next Topic: UNION and dependent LOV
Goto Forum:
  


Current Time: Thu Mar 28 07:11:24 CDT 2024