How to add a Time dependent trigger in PostgreSQL?

Question

I'm confused and clueless on how to:

Add a trigger to a postgresql db once the db has been set up, i.e. adding a trigger by clicking a hyperlink
Write a trigger function so that it deletes a specific row after a week has passed since the trigger was written.

This is the sequence once events just for clarity

Admin sees entry in db via a php generated html page.
Admin clicks 'approve' hyperlink next to db entry.
This creates a trigger in the db to delete that specific row after 1 week has passed.

I need help with step 3

Would an alternative way of doing this be to have a trigger on the table, so that when a row is inserted a new trigger is created which checks the value of an 'approved' column for that row. When the Admin clicks 'approve' in the html page the word 'yes' is inserted to the 'approved' column for that row. The trigger will detect this and add a new trigger to delete the row after a week?

As you can tell i'm really not sure which way to do it and how to do it so any feedback would be much appreciated. Thanks.

there's no such thing like a time dependent trigger. triggers are before/after update/insert/delete. if you have a table which is constantly updated with dates then you can do it. otherwise you will have to setup a cron(?) job or create one in the application. — Sergey Benner
– Sergey Benner, Commented Feb 8, 2012 at 15:53
ok so I'll have a trigger on insert, where a date will be inserted into the table. Should this date be current date or date of deletion? And then how would I get the trigger to do delete the row on the date inserted? — user1197558
– user1197558, Commented Feb 8, 2012 at 16:24
you can have two triggers on two diff tables. first trigger on your original table will update your new job table with your row_id to be deleted and the date of insert. a job will update this table with a current_timestamp(current_date) every day and the new trigger before update/insert(?) will check interval of these two dates and delete the row_id in the original table if the interval matches 7 days — Sergey Benner
– Sergey Benner, Commented Feb 8, 2012 at 16:44
remember to have 3 fields such as rowtodelete_id, insert_date, current_date — Sergey Benner
– Sergey Benner, Commented Feb 8, 2012 at 17:20

Chris Travers · Accepted Answer · 2012-09-08 06:01:52Z

2

Simple: Store an expiration date for the url in your db, only retrieve currently valid records (you can use a view for this) in your select, and delete expired urls from time to time.

You can't use a trigger though. pgAgent or cron could be used to start a cleanup process though.

answered Sep 8, 2012 at 6:01

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add a Time dependent trigger in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related