Re: Is this a trigger bug?

Or leave it as it is (inefficiency and all) and simply add an EXCEPTION clause to catch the NO_DATA_FOUND error.

Mark Styles wrote:
>
> 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 Thu Mar 13 1997 - 00:00:00 CET