Re: two triggers on the same event

Anita,
Do you have a Foreign key on CUSTOMER referencing SUBSCRIBER with CASCADE on delete?
If you do this is your problem. Trigger is trying to remove row from CUSTOMER and this is cascading delete
back to same row in SUBSCRIBER...
You can remove second part of your trigger : "else...." , and let FK CASCADE delete statement.

HTH
Thomas

"Orban Anita" <anita.orban_at_sysdata.siemens.hu> wrote in message news:3B446E57.88B08302_at_sysdata.siemens.hu...
> 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???
>
Received on Sat Jul 21 2001 - 16:29:33 CDT