After insert or update trigger

[eileen1017]

I need to write a trigger that one some columns of the master table have changed, the trigger will first insert the change to a copy table. Later on, if there are more changes to the master of the same row, it will compare the new value to the old value and delete the same row in the copy table and insert the latest change.

I have wrote the following trigger. Some how when I run it, it has compilation error. Does anyone have any idea on how should I approach this?

create or replace trigger TRITON.AFTER_INSERT_UPDATE_ITM001100
after insert or update on TRITON.TTIITM001100
for each row
declare
v_reflag char(1);
begin
v_reflag:='0';
if new.T$ITEM != ld.T$ITEM or :new.T$DSCA != ld.T$DSCA) then
delete from ticcrm.ttiitm001100_copy;
insert /*+append*/ into ticcrm.ttiitm001100_copy
(T$ITEM,
T$DSCA,
T$DSCB,
T$DSCC,
T$DSCD,
T$WGHT,
T$SEAK,
REFLAG)
values
new.T$ITEM,
:new.T$DSCA,
:new.T$DSCB,
:new.T$DSCC,
:new.T$DSCD,
:new.T$WGHT,
:new.T$SEAK,
v_reflag);
end if;
end;
/

 

[SantaMufasa]

Some how when I run it, it has compilation error.

Should we start listing the reasons for all possible compilation errors in an Oracle trigger, or might it be less labor intensive for you to post the error that you are encountering? <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[eileen1017]

Well, the error is not specific.

SQL> @crt_trg_ttiitm001.sql

Warning: Trigger created with compilation errors.

I am not sure where can I look for the error.

 

[SantaMufasa]

When you see the message: "Warning: Trigger created with compilation errors", you can issue the SQL*Plus command:

SQL> show errors

Let us know wht you find out.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[eileen1017]

Thanks. That does the trick. Once I know what the error is, I know exactly what I need to fix. Learn something new everyday.