Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Database triggers
Mousa AL-Mohammed wrote:
> 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
>
>
Instead of doing this in a trigger, schedule a job (cron or dbms_job) to
run every day and delete old apartments.
Received on Thu Dec 19 2002 - 11:03:27 CST