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 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

One thing that I find irksome is when people make statements such as "I need this trigger" when, in fact, they "need this functionality not the trigger."

Write a stored procedure to delete the records.

Execute the stored procedure on a regular schedule using DBMS_JOBS.

Go to http://tahiti.oracle.com and search for "MUTATING TRIGGER" so that you will be better prepared in the future when you do need to use a trigger.

Daniel Morgan Received on Mon Dec 23 2002 - 02:38:03 CST