Re: two triggers on the same event

Orban Anita wrote:

> Hello! I need your help!
>
> There are three tables. vx.User, vx.Subscriber, vx.Customer.
> Every subscriber may have users. So the users have their subscriber's Id
> as well.
> Subscribers-Customers is a 1-1 relation. So the subscribers have a
> CustomerId too.
> If a subscriber is deleted, the customer, belonging to it, should be
> deleted too.
> The other problem is to check if there are users belonging to this
> subscriber, and if there are some, then we cannot delete the subscriber
> (so the Customer also not), so we raise an exception.
>
> These are two things, which should be done when deleting a subscriber.
> These I solved in the same trigger:
>
> CREATE OR REPLACE TRIGGER vx.Subscriber_delbefor_trig
> BEFORE DELETE
> ON vx.Subscriber
> REFERENCING OLD AS OLD
> FOR EACH ROW
> declare
> subscriberId vx.Subscriber.subscriberId%TYPE;
>
> -- check if there are any users of this subscriber
> cursor c1 is
> select subscriberId from vx.User where subscriberId =
> :OLD.subscriberId;
>
> Begin
> open c1;
> fetch c1 into subscriberId;
>
> if c1%found then
> close c1;
> raise_application_error( -20100, 'Cannot delete Subscriber with
> subscriberID "'||subscriberId||'"! You
> have to remove the users of it first!' );
> else
> close c1;
> delete from vx.Customer where customerId = :OLD.customerId;
> end if;
> End;
> /
>
> If I try to delete a subscriber with users, it's ok, the error is
> coming, that's what I wanted
> but if I delete a subscriber without users it's giving an exception,
> which I didn't want....
> I'm getting the following exception:
> ORA-04091: table vx.Subscriber is mutating, trigger/function may not see
> it
>
> What's the problem???

The problem is that a trigger can not modify records in the table on which it is hosted.

Daniel A. Morgan Received on Sat Jul 21 2001 - 16:29:21 CDT