Subquery in trigger value

Question

I am creating a trigger in PLSQL:

CREATE OR REPLACE TRIGGER ALS_INT_WH_CFA_EXT_AIUDR
    AFTER DELETE OR INSERT OR UPDATE
    ON WH_CFA_EXT
    FOR EACH ROW
DECLARE
    v_wh   NUMBER := NULL;
BEGIN
    v_wh := :new.wh;

    IF (v_wh IN (SELECT wh
                   FROM wh
                  WHERE wh != physical_wh))
    THEN
        INSERT INTO WH_MFQUEUE_TEST (SEQ_NO,
                                     WH,
                                     MESSAGE_TYPE,
                                     FAMILY,
                                     PUB_STATUS,
                                     TRANSACTION_NUMBER,
                                     TRANSACTION_TIME_STAMP)
             VALUES (WH_MFSEQUENCE.NEXTVAL,
                     :new.wh,
                     'whmod',
                     'WH',
                     'U',
                     1,
                     SYSDATE);
    END IF;
END;
/

It should me the error:

Error(7,13): PLS-00405: subquery not allowed in this context

How do I use subquery in this case?

kara · Accepted Answer · 2019-04-17 11:56:38Z

1

You have to store your query-result into a variable.
You should put the v_wh-Condition into you where-block

Example:

DECLARE
    v_result NUMBER;
BEGIN
-- [...]
    SELECT COUNT(*) INTO v_result FROM wh WHERE wh != physical_wh AND wh = v_wh;

    IF v_result > 0
    THEN
        --[...]

answered Apr 17, 2019 at 11:56

kara

3,4854 gold badges24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BadZen · Accepted Answer · 2019-04-17 11:54:07Z

0

You need to use a temp variable:

SELECT wh INTO tmpvar FROM wh WHERE wh != physical_wh;
if (v_wh in tmpvar) THEN ...

Subqueries can only be used in SQL statements, which the IF statement is not.

answered Apr 17, 2019 at 11:54

BadZen

4,3853 gold badges30 silver badges55 bronze badges

Comments

Kaushik Nayak · Accepted Answer · 2019-04-17 12:29:23Z

0

You may check for existence of rows with wh.wh = :new.wh and wh != physical_wh using a separate integer variable.

CREATE OR REPLACE TRIGGER als_int_wh_cfa_ext_aiudr AFTER
     DELETE OR INSERT OR UPDATE ON wh_cfa_ext
     FOR EACH ROW
DECLARE
     v_wh INT;
BEGIN
     SELECT CASE WHEN EXISTS
     ( select 1  v_wh
       FROM wh WHERE wh != physical_wh
           AND wh = :new.vh ) THEN 1 ELSE 0 END INTO v_wh from dual;

     IF v_wh = 1 THEN 
     INSERT INTO wh_mfqueue_test (
          seq_no,wh,message_type,family,pub_status,transaction_number,
          transaction_time_stamp
     ) VALUES (
          wh_mfsequence.NEXTVAL,:new.wh,'whmod','WH','U',1,SYSDATE
     );

     END IF;

END;
/

answered Apr 17, 2019 at 12:29

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

Subquery in trigger value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related