trigger - update field in same table

[Vandy02]

My trigger is on a table called workorder. When the glaccount field is a new entry I want it to insert a substring of the value into another field. How do I do this? My code keeps receiving an error of
PLS-00049: bad bind variable 'NEW.GLCOMP01'
I have also tried referrencing old as old and new as new, but then I get an invalid trigger.

TRIGGER:

declare

v_gl workorder.glcomp01%type;

begin
v_gl := substr(glaccount, 1, 3);

If :new.glcomp01 is null then
:new.glcomp01 = v_gl;
end if;

end;

 

[jackomaltby]

use before update referencing old as old new as new
then don't forget to use := for the assignment, not =
so your code would be:

begin
v_gl := substrnew.glaccount, 1, 3);

If :new.glcomp01 is null then
:new.glcomp01 := v_gl;
end if;

end;

don't have oracle up so i can't give the create trigger part, but i hope this helps.

 

[jee]

You did not tell us what type of trigger you have, but to use :new and ld, it must be a ROW trigger, not a STATEMENT trigger. To change the value, it must be a BEFORE trigger, not an after trigger

The start of the trigger should look something like this:

CREATE OR REPLACE TRIGGER xxx
BEFORE INSERT OR UPDATE
ON TableA
FOR EACH ROW