|
-
Oracle Trigger
I am new to writing triggers and stored procedures and such, so I was wondering if anyone would be able to help me.
I am trying to write a trigger so that once a record is inserted into a table called ABT_HOLD, that the trigger will look at a field called STRCS and if that field contains the word "Curved", then it will enter a 1 into a field called CURVED. If it not then it will enter a 0 into the field. If the same field called STRCS contains the word "Straight" then it will enter a 1 into a field called STRAIGHT.
Also, along the same lines I want it to look at a field called STRSF and if that field contains the word "Snout", then it will enter a 1 into a field called SNOUT. If not put a 0 into the field. If the same field called STRSF contains the word "Fin" then it will enter a 1 into a field called FIN.
Here is what I have so far.
CREATE OR REPLACE TRIGGER tri_abt_hold
BEFORE INSERT
ON abt_hold
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Begin
IF :OLD.STRCS = 'Curved' THEN
update ABT_HOLD Set :OLD.CURVED = 1;
ELSE
update ABT_HOLD Set :OLD.CURVED = 0;
END IF;
IF :OLD.STRCS = 'Straight' THEN
update ABT_HOLD Set :OLD.STRAIGHT = 1;
ELSE
update ABT_HOLD Set :OLD.STRAIGHT = 0;
END IF;
IF :OLD.STRSF = 'Snout' THEN
update ABT_HOLD Set :OLD.SNOUT = 1;
ELSE
update ABT_HOLD Set :OLD.SNOUT = 0;
END IF;
IF :OLD.STRSF = 'Fin' THEN
update ABT_HOLD Set :OLD.FIN = 1;
ELSE
update ABT_HOLD Set :OLD.FIN = 0;
END IF;
End;
/
I am getting an error on this line "update ABT_HOLD Set :OLD.CURVED = 1;" saying "(1):PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification"
If I take the ":OLD." off, then I don't get that error, but I get a mutating table error.
Am I going about this the correct way? Seems like I need the :OLD in there...
Any help would be appreciated.
Thanks in advance.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|