Re: Using like on :new variable in trigger code

Todd Boss wrote:
> Hello.
>
> I'm trying to perform a like comparison on a column in the :new table
> within a trigger and cannot get it to work. The trigger looks like this:
>
> create or replace trigger mytrigger
> after insert or update on mytable
> for each row
>
> declare
> new_id number;
>
> select id into new_id
> from id_table
> where id_table.tracking_num like '%:new.partial_tracking_num%'
>
> ... (the rest of the trigger code, which works fine without this statement)
>
> How do you reference the :new.partial_tracking_num in the above select statement?
> Just substituting in the actual value in a normal sql statement from sqlplus
> works just fine. I've also tried selecting :new.partial_tracking_num
> into a variable and using the variable in the like statement, with no luck.
>
> In fact, i can't even get :new.partial_tracking_num to print out in a
> dbms_output line to even SEE what it is going into the select statement.
>
> Any trigger gurus out there?
>
> thanks in advance,
> Todd

There is no such column -it's a pseudo column, that only exists during the transaction.
Actually, your code uses some defaults, and reads:   create or replace trigger mytrigger
  after insert or update on mytable
  referencing old as old new as new <<<<<< Look!   for each row

The look bit is where :new comes from.
Notice there is also an old value. What happens is, that just before the actual update of a column in a table, that value-to-be is available as :new.column_name, and the current value as :old.column_name.

If you would write a journalling trigger, you would use both: that old as well as the new values, with some user info and timestamps, so you can always trace the history of data: user so-and-so changed this column from this value to that value at this date/time.

Anyway - I guess (not knowing what you try to establish here) that this would do the job:

create or replace trigger mytrigger
after insert or update on mytable
for each row

declare

     new_id number := :new.partial_tracking_num;

This would make new_id get the value of what partial_tracking_num is going to be. Remeber, at this point, is still has the old value, which can be obtained by referring to :old.partial_tracking_num.

Hth, grtz, Frank Received on Thu Dec 19 2002 - 03:51:36 CST