Re: trigger statement:

spliffmonkey_at_iname.com wrote:
> Quick question. This trigger works fine (it just sets a themepark rides
> status as closed when an accident has been inserted into the accident
> table):
>
> CREATE TRIGGER closeride
> AFTER INSERT ON Accident
> REFERENCING NEW AS newRow
> FOR EACH ROW
> BEGIN
> update Ride set Ride.status = 'closed' where Ride.Ridename =
> :newRow.Ridename;
> END closeride;
>
> Now I want to make a variation on this where if the accident table
> returns more than 3 rows for a given ride then the ride status is set
> to closed. This is my attempt (bellow). Could someone take a look at
> it? Its the same as the one above except for the WHEN
>
> CREATE TRIGGER toomanyaccidents
> AFTER INSERT ON Accident
> REFERENCING NEW AS newRow
> FOR EACH ROW
> WHEN((select * from accident where Ridename = :new.Ridename)> 2)
> BEGIN
> update Ride set Ride.status = 'closed' where Ride.Ridename =
> :newRow.Ridename;
> END closeride;

You'll find you receive a mutating table error with that second trigger. You cannot select from the table the trigger is created against in the trigger itself, however you CAN write a stored procedure to return such information to the trigger:

 CREATE TRIGGER toomanyaccidents
 AFTER INSERT ON Accident
  REFERENCING NEW AS newRow
 FOR EACH ROW
 declare

     acc_ct number := 0;
 BEGIN
    get_accident_ct(:newRow.Ridename, acc_ct);     if acc_ct >= 3 then

             update Ride set Ride.status = 'closed' where Ride.Ridename = :newRow.Ridename;

    end if;
END toomanyaccidents;
/

Where get_accident_ct would be:

create or replace procedure get_accident_ct (ridenme in varchar2, acc_ct out number) as

     pragma autonomous_transaction;
begin

      select count(*) into acc_ct from accident where ridename = ridenme;
end;
/

Of course this only works if you already have 3 accidents inserted for the given ride. You could change the conditional to >= 2 so, on the third accident, the ride would be closed. All of this is explained at asktom.oracle.com if you search for 'mutating table'.

David Fitzjarrell Received on Sat Dec 10 2005 - 15:05:33 CST