CODEWITHSUNDEEP
postgresqltimetriggers
user1197558
user1197558

Reputation: 71

How to add a Time dependent trigger in PostgreSQL?

I'm confused and clueless on how to:

This is the sequence once events just for clarity

  1. Admin sees entry in db via a php generated html page.
  2. Admin clicks 'approve' hyperlink next to db entry.
  3. 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.

Upvotes: 4

Views: 3297

Answers (1)

Chris Travers
Chris Travers

Reputation: 26454

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.

Upvotes: 2

Related Questions