Re: trigger?

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.
>>
>> 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 Received on Fri May 18 2001 - 08:32:46 CDT