Re: Trigger(?) question

In article <34B5FE32.C0DF469F_at_iaehv.nl>,   Erik van der Looy <evdlooy_at_iaehv.nl> wrote:
>
> Hello experts,
>
> I've got a problem with creating a trigger, or maybe it
> would be better to solve the problem on another way(?).
> If somebody could give it a go, I would really appreciate
> the help;
>
> On a certain table there are the next columns:
>
> PRODUCT_ID(PK) STATUS OLD_PRODUCT_ID
> ---------- -------- --------------
> 1 INACTIVE 254
> 2 INACTIVE 259
> 3 ACTIVE 280 *
> 4 INACTIVE 312
> 5 INACTIVE 280 *
> 6 ACTIVE 129
>
> When a status is set from INACTIVE to ACTIVE, there
> must be a check (trigger?) to see if the value in OLD_PRODUCT_ID
> is unique in the column OLD_PRODUCT_ID.
> For example the rows indicated with *. So the trigger must
> go off afther changing the status in row 5 to ACTIVE.
>
> I think this shouldn't be too difficult, but one way
> or another, I can't get it too work!
>
> Any help is appreciated, preferred by mail.
> Thanks in advance!
>
> Regards,
> Erik

It looks like you are having a "mutating table" problem. This happens because inside a row trigger you are trying to query the same table for which the trigger was fired and this table is mutating, i.e. changing because the statement which fired the trigger has not finished (due to the trigger). The easiest way to fix it is to make the trigger a statement trigger instead of a row trigger. The trigger should be adapted in this case.

Hope this helps,

Antonio Galdo

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet