Re: Using like on :new variable in trigger code

I think you need to concatenate the %s: Try
where id_table.tracking_num like '%' || :new.partial_tracking_num || '%'

hth

toddboss_at_yahoo.com (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
Received on Tue Dec 17 2002 - 15:13:30 CST