Re: S.O.S! Oracle trigger in PL/SQL

On Wed, 18 Feb 1998 17:58:42 +0800, Novita Leung <nyylcom_at_netvigator.com> wrote:

[,,,]
>
>create trigger scott.changes
> BEFORE
> UPDATE
> on scott.A_table
> for each row

 [...]
> select count(*) into recnum from A_table where
>flight_id=:new.flight_id;

NO! Can't ever do this. If you are in a row trigger on table A, you can't select from A in any way, directly or indirectly. That's because Oracle guarantees that you won't be screwed over by changes in row or index order.

I wish I could give you an alternative, but I'm not exactly sure what you're trying to do. Usually, you create a third table to store the information you need to be able to make the updates later. Then write a statement trigger [no "for each row"] to do the updates you need. Statement triggers aren't bothered by that mutating tables thing.

/cpk Received on Wed Feb 18 1998 - 00:00:00 CST