Re: trigger?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 18 May 2001 13:15:52 -0400
Message-ID: <7tlagt852nr2rraocp8evtar4bilrp4c9g@4ax.com>

A copy of this was sent to ttigger4321_at_hotmail.com (tony) (if that email address didn't require changing) On Fri, 18 May 2001 13:32:46 GMT, you wrote:

>Thanks. Please see my comments below.
>
>On Thu, 17 May 2001 18:34:15 -0700, "Daniel A. Morgan"
><dmorgan_at_exesolutions.com> wrote:
>
>>tony wrote:
>>
>>> Hi. I want to create a trigger that will delete rows that are sysdate
>>> - 3 or older.
>>>

A better method, one that will let you system perform better, would be to use DBMS_JOB and schedule a stored procedure that cleans out old data.

You could:

declare

   l_job number;
begin

   dbms_job.submit( l_job,

                   'delete from table_a where date_field <= sysdate-3; commit;',
                    sysdate,
                   'trunc(sysdate,''hh'')+1/24' );

end;
/

that would schedule that delete to run every hour on the hour.

In that fashion, if no one modifies your table for 3 hours (hence your trigger would not fire) you will still have data more then 3 days old getting removed. If you wanted this to run every N minutes instead of hourly, you would use

  'sysdate + n/24/60'

and so on. See
http://technet.oracle.com/doc/oracle8i_816/server.816/a76936/dbms_job.htm#999107 for details on use dbms_job including the relevant init.ora parameters job_queue processes and job_queue interval.

In my opinion, I would probably schedule this job to run 2 or so times A DAY and use a view that retrieves only the records in the last three days.

>>> Here's a select that returns the correct rows:
>>>
>>> select * from table_a
>>> where
>>> last_activity_date >= sysdate - 3
>>>
>>> Can anyone show me the syntax to cretate this trigger?
>>>
>>> TIA,
>>> tony
>>
>>What you are describing is not a suitable usage for a trigger.
>>
>>There are three basic trigger types, on insert, on update, and on delete.
>>What event would you expect to set off this trigger? And do you understand
>>that the trigger would fire each and every time this triggering event
>>happened? And are you aware that you can not use a trigger to modify the
>>table on which it is placed in the manner you indicate above?
>>
>
>I want the trigger to fire after update, and yes, I'm aware that
>triggers are event driven. I don't want to modify the table. I do
>want to delete rows where the value of a date field indicates that the
>row is 3 or more days old.
>
>>At the very least, you need to indicate the relationship of the table the
>>trigger is on with the table where the deletes will take place and the
>>type of triggering event for anyone to help you.
>>
>>Daniel A. Morgan
>>
>
>I want the trigger on the same table that I want the deletes to occur.
>Anyway, here's what I've come up with. I hope it's at least close.
>
> create or replace trigger trigger_name1
> after update on tableA
> for each row /* I don't think I need this */
> begin
> delete from table_a
>where
>date_field <= sysdate - 3;
> end;
>
>Thanks for you comments.
>tony

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Fri May 18 2001 - 12:15:52 CDT