Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Simple trigger question
Martin,
Thanks for the help. I started down this path and ran into some issues that led me to try an AFTER trigger, but that ran into the issue Karsten warned....mutating table error.
Now I have the trigger as a BEFORE event and it is working perfectly.
Thanks again,
Kurt
Martin Doherty <martin.doherty_at_nospam.com> wrote in message news:<3E2370EA.6080508_at_nospam.com>...
> 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 Tue Jan 14 2003 - 15:53:56 CST