Using like on :new variable in trigger code

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 Received on Tue Dec 17 2002 - 14:24:32 CST