Re: Is this a trigger bug?

From: Mark Styles <lambic_at_msn.com>
Date: 1997/03/05
Message-ID: <331DE94C.413C_at_msn.com>#1/1

amit srivastava wrote:
>
> Can select stamements that do not reutrn any rows be used in a trigger?
> The trigger does not seem to exceute or die if it encounters
> a select stmt. without any records?
>
> .
> .
> .
>
> if updating then
> insert into temp_test values (' in updating');
> select distinct sr_active_dt
> into active_date
> from salesrep_hist
> where sr_id = :old.sr_id
> and to_char(sr_active_dt,'DDMMYY') = to_char(sysdate,'DDMMYY');
> insert into temp_test values (' past select');
> rem above stmt never gets exceuted!!!!!
>
> if (active_date is null) then

Well I don't know about a bug, but this type of code is generally frowned upon. It is always best to explicitly define cursors for queries, rather than letting PL/SQL create them implicitly, which is what you are doing in the above example. Implicit cursors have an overhead, because they require two fetches to be performed, one to fetch the row, and one to check that there are no more rows.

I would write the above code as:

DECLARE
    CURSOR c_active (id number) IS

        SELECT DISTINCT sr_active_dt
        FROM   salesrep_hist
        WHERE  sr_id = id
        AND    TRUNC(sr_active_dt) = TRUNC(SYSDATE);

  . . .
  . . .
BEGIN
  . . .
  . . .
    if updating then

        insert into temp_test values (' in updating');
        open  c_active(:old.sr_id);
        fetch c_active into active_date;
        close c_active;
        insert into temp_test values (' past select');

  . . .
  . . .
END; Hope this helps. Received on Wed Mar 05 1997 - 00:00:00 CET