Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!news.glorb.com!newsfeed.icp.pl!newsfeed.atman.pl!newsfeed.tpinternet.pl!atlantis.news.tpi.pl!news.tpi.pl!not-for-mail
From: "remg" <remq75@wp.pl>
Newsgroups: comp.databases.oracle.server
Subject: Trigger problem
Date: Thu, 3 Mar 2005 14:16:56 +0100
Organization: tp.internet - http://www.tpi.pl/
Lines: 29
Message-ID: <d0730g$5h4$1@nemesis.news.tpi.pl>
NNTP-Posting-Host: 193.108.206.253
X-Trace: nemesis.news.tpi.pl 1109856080 5668 193.108.206.253 (3 Mar 2005 13:21:20 GMT)
X-Complaints-To: usenet@tpi.pl
NNTP-Posting-Date: Thu, 3 Mar 2005 13:21:20 +0000 (UTC)
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:237354

Hi everyone,
I try to write trigger on Oracle 8.1.7. The trigger on table A should delete
records on table B only when table A doesn't contain any records with
special ID.

For example:

CREATE OR REPLACE TRIGGER "myTrigger" AFTER
    DELETE
    ON "myTable"
    FOR EACH ROW
    declare cnt number;
begin
   select count(distinct id) into cnt  from myTable  where myColumn =
:new.myColumn;
   if (cnt=0) then
      delete from table B where .....
   end if;
end;

Unfortunatelly, I get an error "table is mutating, trigger may not see it..
" which is obvious for me, because trigger tries to count records from table
on which it is based.
Is there any way to solve that problem? How to write this "conditional"
trigger? The trigger must be "for each row".
Regards,
remg