Re: Need help writing a trigger

Keith S. wrote:
>
> I have 2 tables involved in a trigger (currently on Interbase).
> The trigger goes something like this ...
>
> create trigger test
> after delete on A
> for each row
>
> begin
> update B
> set status = 'X'
> where code = (select code from A tab_a
> where tab_a.code = :old.code
> and tab_a.mydate <= sysdate);
>
> end;
>
> code is the primary key on B, but not on A
>
> Obviously, this trigger causes errors (table mutating). I'm
> wondering if there is a smart way around this. I cannot re-engineer
> the tables.
> --
>
> kshave_at_minet.gov.mb.ca

Somehow (in a new column of your A table or in a package) you need to save the 'old' values of code in a before delete trigger for each row on A. Then, in an after statement trigger on A, use the stored information to update the table B. The after statement trigger has no problem reading from A, since it is no longer mutating.

Isn't this fun? If you need an explicit example, let me know.

-- 
Diana Duncan	     | My opinions are my own.
Sr. Consultant	     | 
REALOGIC, Inc.	     | Excitement, Adventure and
dduncan_at_realogic.com | Really Wild Things - Z.B.