CODEWITHSUNDEEP
oracle-databasedatabase-trigger
Shajith
Shajith

Reputation: 1

subquery in oracle trigger

I want include condition in my trigger, based on data from a table. As oracle to do allow subquery in trigger, how it can be achived. Please find my code below. Trans_code_master holds the list of valid code, which can be changed.

Thanks in advance for all your help.

CREATE OR REPLACE TRIGGER CUST_TRG
BEFORE INSERT OR UPDATE ON CUST_ALL_TRANS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
WHEN (NEW.TRANSACTION_CODE IN(SELECT TRANS_CODE FROM TRANS_CODE_MASTER))
BEGIN

  INSERT INTO CUST_DEPO_TRANS
  (
   CUST_ID
  ,AC_ID
  ,TRANSACTION_CODE
  )
  VALUES(
   :NEW.CUST_ID
  ,:NEW.AC_ID
  ,:NEW.TRANSACTION_CODE
  )
EXCEPTION
   WHEN OTHERS THEN
     -- Consider logging the error and then re-raise
     RAISE;
END;
/

Upvotes: 0

Views: 95

Answers (1)

Gaj
Gaj

Reputation: 886

Its not possible in write query in when clause. Try the below way

CREATE OR REPLACE TRIGGER CUST_TRG
BEFORE INSERT OR UPDATE ON CUST_ALL_TRANS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
     V_CNT NUMBER;
BEGIN
  SELECT COUNT(1) INTO V_CNT
  FROM TRANS_CODE_MASTER 
  WHERE TRANS_CODE = NEW.TRANSACTION_CODE;
  IF V_CNT > 0 THEN
  INSERT INTO CUST_DEPO_TRANS
  (
   CUST_ID
  ,AC_ID
  ,TRANSACTION_CODE
  )
  VALUES(
   :NEW.CUST_ID
  ,:NEW.AC_ID
  ,:NEW.TRANSACTION_CODE
  )
  END IF;
EXCEPTION
   WHEN OTHERS THEN
     -- Consider logging the error and then re-raise
     RAISE;
END;
/

Upvotes: 1

Related Questions