Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Simple trigger question
It sounds to me like you can solve your problem using the :NEW and :OLD
pseudo-records to test and force the data to be correct (in a BEFORE
INSERT FOR EACH ROW trigger, the :OLD columns are undefined).
Something along the lines of
...
if :new.cust_type = 'A' then
:new.cust_payment_type := 3;
end if;
so whatever was supplied originally for cust_payment_type will be overwritten with the value 3 for customers of type 'A'.
In the case of a row update, I am not sure if :NEW has values defined for columns that are not being SET in the UPDATE statement, but I'll take a wild stab and guess that non-referenced columns the :NEW value is the same as the :OLD value, so the above snippet would still work - but please check before quoting me.
Martin Doherty
Kurt wrote:
>I'm real rusty with triggers..and I need one fairly quickly, so I
>would really appreaciate a bit of help.
>
>
>All I need to do:
>
>Upon insert or update of a row in a table, I need to check the value
>of one of the columns, and if it equals a certain value, then update
>one of the other columns and 'force' it to a certain value regardless
>of it's value in the insert the statement just issued. (the statement
>will provide a value for that column, but I need to make sure it gets
>forced to a particular value)
>
>This is a temporary fix for an application bug.
>
>Hope that makes sense,
>
>Thanks!
>
>
Received on Mon Jan 13 2003 - 20:07:38 CST