UPDATE Column in Same Table after INSERT using trigger in Oracle

Question

i need to update one column in the same table after inserting is done. INSERT already coded and cannot be changed . So we are going with TRIGGER approach to update one column value.

create or replace TRIGGER "BLT_TRIGGER" 
AFTER INSERT ON BLT_MAPPING 
FOR EACH ROW
BEGIN
UPDATE BLT_MAPPING 
SET FIELD_ID = (SELECT CASE WHEN CORR_TI = 'B' THEN '12345' ELSE '54321' END 
AS INSTITUTION FROM BROKER CO,LOAN LO
WHERE LO.CORR_ID = CO.CORR_ID AND LO.LOAN_ID = loanid_in_BLT_MAPPING_table 
whcih_got_inserted )
END;

I am getting purge error . What is the mistake i am doing in thins trigger .

Why not contemplate having a MERGE whereby INSERT and UPDATE are possible with WHERE conditions in a single statement; thus, there is a possibility of correcting the records. Besides, as rightly pointed out by @eaolson, triggers are obscure — Jacob
– Jacob, Commented Dec 1, 2018 at 6:06

eaolson · Accepted Answer · 2018-12-01 02:20:11Z

2

Are you trying to update the same row that you just inserted? In that case, you want to make this a BEFORE INSERT trigger, and change :NEW.fieldid before it is inserted into the table. Something like this:

create or replace TRIGGER "BLT_TRIGGER" 
BEFORE INSERT ON BLT_MAPPING 
FOR EACH ROW
    l_new_fieldid     number; -- Or whatever the correct datatype is
BEGIN

    SELECT CASE WHEN CORR_TI = 'B' THEN '12345' ELSE '54321' END 
      INTO l_new_fieldid
      FROM BROKER CO,LOAN LO
     WHERE LO.CORR_ID = CO.CORR_ID 
       AND LO.LOAN_ID = :NEW.loanid;

    :NEW.fieldid := l_new_fieldid;

END;

But this is an inferior solution. Triggers obscure what you are trying to really do and are hard to debug when there is a problem. The better solution is to correct your INSERT statement with what you're trying to really do.

answered Dec 1, 2018 at 2:20

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

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

1 Comment

VKP Over a year ago

Thanks Its Working .

Collectives™ on Stack Overflow

UPDATE Column in Same Table after INSERT using trigger in Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related