CODEWITHSUNDEEP
sqloracle-databasetriggersconditional-statementsinsert-into
dw1977
dw1977

Reputation: 11

Oracle Trigger after insert with condition

I am new to PL/SQL programming and i've been task to create a simple trigger; see code below:

CREATE OR REPLACE TRIGGER  TRG_ACCT_IDW   
AFTER INSERT   
ON ACNTGROUPS  
FOR EACH ROW   
BEGIN   
INSERT INTO IDWORKS_HC   
(ACCOUNT,IDW_CATEGORYNO,GRTYPE)   
VALUES   
(:NEW.ACCOUNT,:NEW.GROUP_,:NEW.TYPE)   
WHERE ACNTGROUPS.TYPE = '1'  
END;

Everything works great without the where clause. Can an 'after insert' trigger have a condition clause? does my code above need reformating? thanks in advance.

Upvotes: 1

Views: 7203

Answers (2)

Forage
Forage

Reputation: 2900

Using the WHEN condition would be the cleanest solution.

CREATE OR REPLACE TRIGGER TRG_ACCT_IDW
    AFTER INSERT
    ON ACNTGROUPS
    FOR EACH ROW
    WHEN (NEW.TYPE = '1')
BEGIN   
    INSERT INTO IDWORKS_HC   
    (ACCOUNT,IDW_CATEGORYNO,GRTYPE)   
    VALUES   
    (:NEW.ACCOUNT,:NEW.GROUP_,:NEW.TYPE); 
END;

Do not put a colon (:) before the correlation name NEW, OLD, or PARENT in the WHEN condition.

Upvotes: 3

Dmitry.Samborskyi
Dmitry.Samborskyi

Reputation: 485

In your case you need something like this

CREATE OR REPLACE TRIGGER  TRG_ACCT_IDW   
AFTER INSERT   
ON ACNTGROUPS  
FOR EACH ROW   
BEGIN   
    IF :NEW.TYPE = '1' then
        INSERT INTO IDWORKS_HC   
        (ACCOUNT,IDW_CATEGORYNO,GRTYPE)   
        VALUES   
        (:NEW.ACCOUNT,:NEW.GROUP_,:NEW.TYPE); 
    END IF;
END;

Upvotes: 1

Related Questions