Oracle Trigger

[airnewhouse]

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.

[PAVB]

mmmhhh... referencing the table that owns the triggers in a before trigger?that's bad -very bad mojo.

Your trigger is looking for troubles, specifically "mutating table" troubles.

Research "mutating table and triggers"

[pss]

You can accomplish everything explained in your post without triggers and with much better performance using default value feature. Read SQL Reference book on create table statement. You need to look for column definition and then default value in it.
Using default values is much cleaner way to do it and self documenting via Oracle data dictionary. Triggers are being abused by developers a lot. If I can make Oracle remove one feature from the database this would be object level DML triggers.

[airnewhouse]

Default values cannot contain references to other columns, so I don't understand how adding a default value would help in my situation. Can you provide an example?

I figured out the necessary syntax to accomplish what I need with a trigger. However, if there is a better way to do it, I'd be glad to try it.

[pss]

My bad. Anything wrong with doing it in the insert command itself? The logic to populate the columns is very simple. Perhaps, creating check constraints will be a very good idea to enforce data integrity and help optimizer a little bit.

[airnewhouse]

Unfortunately, the insert statement, or "export" as they call it, is coming from an OCR COTS product, where performing simple logic to manipulate the insert values on that side is very limited, or maybe I just can't seem to figure that out. So it seems like it is pretty much impossible to decipher from the COTS side if a certain field contains a certain value, then put another value in another field to be inserted into the database.

I would agree though, doing it in the insert would be a better design and much more efficient.