Re: trigger puzzlement

"Mike Stenzler" <mstenzler_at_ssaris.com> wrote in message news:BAOWb.78592$va1.36814_at_fe23.usenetserver.com...

> -- test if record has already been inserted today
> select 'TRUE' into v_exists from DUAL where EXISTS
> (select * from test.t2
> where last_change = TO_DATE(TO_CHAR(SYSDATE(),
 'MM-DD-YYYY'),'MM-DD-YYYY')
> and cmdy = :new.cmdy_sym
> );

>

There are several ways to do this, without using a cursor, or error-handling.

select count(*) into v_count from test.t2 where ...; if v_count > 0 then ...

or if you really want to use exists:

select (select 'TRUE' from dual where exists (...) union select 'FALSE' from dual where NOT exists (...))
into v_exists from dual;
if v_exists = 'TRUE' then ...

There are probably more ways to do this, but these should get you going.

Theo Received on Sat Feb 14 2004 - 22:46:31 CET