database triggers

From: Mousa AL-Mohammed <mousa.al-mohammed_at_nokia.com>
Date: Mon, 23 Dec 2002 08:12:44 GMT
Message-ID: <0AzN9.23229$ws6.470266@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 MESSAGES 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.

I need this trigger in order not to show the user any old messages.

So any help Please
Mousa Received on Mon Dec 23 2002 - 02:12:44 CST