Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Database triggers
Mousa,
Could you, please, provide some more information:
Oracle version
Operating system, version
Hardware
The SQL used to create the various tables, constraints, indexes, triggers, etc. you use. Some representative data would also be nice.
Could there be a relationship between the table messages and apartments that is causing the tigger to call itself without end (hence the mutating).
Bertram Moshier
Oracle Certified Professional 8i and 9i DBA
http://www.bmoshier.net/bertram
"Mousa AL-Mohammed" <mousa.al-mohammed_at_nokia.com> wrote in message
news:4PmM9.23084$ws6.467919_at_news2.nokia.com...
> Hi;
> I have a table such as messages(msg_id, msg_orginator, msg_valid_date,
> msg_sub_date, msg_details)
>
> The msg_valid_date is the valid time for the message to be exist in the
> database, I need to delete this message if it past (i.e. msg_valid_date
> become in the past). This to be happen upon every table access (insert,
> update, delete, or even select).
>
>
> I have tried to use a trigger like:
> -------------
> CREATE OR REPLACE TRIGGER DELETE_OLD_MSGS
> BEFORE INSERT OR UPDATE OR DELETE ON messages
> REFERENCING OLD AS oldRow
> FOR EACH ROW
> BEGIN
> DELETE FROM APARTMENTS WHERE :oldRow.msg_valid_date < SYSDATE;
>
> END DELETE_OLD_MSGS;
> .
> RUN;
> ------------
>
> with this kind of trigger a problem of Mutating Table problem. I have
study
> the Trigger chapter of PL/SQL Programming book by SCOTT URMAN it was
taking
> about to work around the mutating table, but that also does not work with
> me, or may be I don't understand it very will.
>
>
> So any help Please
> Mousa
>
>
Received on Thu Dec 19 2002 - 17:04:40 CST