Hi,
I've these tables:
TAB TR:
PROP_TI..........PROP_TR
ONE..............ONE_NEW
TWO..............TWO_NEW
FOUR.............FOUR_NEW
FIVE.............FIVE_NEW
TAB LS_TR
COD...........PROP..........MY_PROP.......PROP_TI
001............A............MY_ONE...........ONE
002............B............MY_TWO...........TWO
005............A............MY_FOUR...........FOUR
006............C............MY_FIVE...........FIVE
I'd like to update the column PROP_TI when I update the col PROP with these conditions:
If PROP='A' then PROP_TI=LS_TR.MY_PROP
IF PROP='B' or 'C' then PROP_TI=TR.PROP_TR
I created this trigger:
CREATE OR REPLACE TRIGGER upd_prop_tr
BEFORE UPDATE
ON ls_tr
FOR EACH ROW
DECLARE
v_prop_tr VARCHAR2 (64);
BEGIN
IF :NEW.prop = 'A'
THEN
:NEW.prop_ti := :OLD.my_prop;
ELSIF :NEW.PROP IS NULL THEN
:NEW.prop_ti := NULL;
ELSIF :NEW.prop = 'B' OR :NEW.prop = 'C'
THEN
SELECT prop_tr
INTO v_prop_tr
FROM tr
WHERE prop_tr = :OLD.prop_ti;
:NEW.prop_ti := v_prop_tr;
END IF;
END;
/
I tried this update:
UPDATE LS_TR
SET PROP='A'
WHERE COD='002';
OK ---> PROP_TI = 'MY_TWO'
UPDATE LS_TR
SET PROP=NULL
WHERE COD='002';
OK ---> PROP_TI = NULL
UPDATE LS_TR
SET PROP='B'
WHERE COD='002';
In this case I get error:
ORA-01403: no data found
ORA-06512: at "UPD_PROP_TR", line 11
ORA-04088: error during execution of trigger 'UPD_PROP_TR'
I think there are any problem on query:
SELECT prop_tr
INTO v_prop_tr
FROM tr
WHERE prop_tr = :OLD.prop_ti;
because WHERE prop_tr = :OLD.prop_ti not always valid (no data found)
I tried also
SELECT prop_tr
INTO v_prop_tr
FROM tr
WHERE prop_ti = :OLD.prop_ti;
but I get same error!
How can I resolve my problem???
Thank in advance