3

Trying to create a trigger that will delete any record that is 90 days old. I used a trigger statement from stackoverflow that I have found and changed the statement a bit, but in my MySQL Workbench, I am getting a syntax error. I cannot seem to figure what is wrong.

Below is my query:

create trigger user_connections_dump after insert on user_connections 
for each row
begin
   delete from connection_time where Date('2014-06-09') > Date('now','-90 days')
end;
AznDevil92
  • 554
  • 2
  • 11
  • 39

3 Answers3

15

Your need looks more like an Event than a Trigger.

CREATE EVENT IF NOT EXISTS `Clean_Older_Than_90_days_logs`
ON SCHEDULE
  EVERY 1 DAY_HOUR
  COMMENT 'Clean up log connections at 1 AM.'
  DO
    DELETE FROM log
    WHERE log_date < DATE_SUB(NOW(), INTERVAL 90 DAY)

References: MySQL Event Scheduler on a specific time everyday

Community
  • 1
  • 1
celerno
  • 1,367
  • 11
  • 30
  • Just trying to understand this statement. Still a junior to Database and sql. So basically this statement is creating an event where everyday at 1am it will delete records from my table that are 90 days or older is that correct? And this is a automatic thing right? – AznDevil92 Jun 11 '14 at 16:33
  • 1
    @AznDevil92 Yes, if you are a junior in MySQL (as I'm too), you'll need to check some other things like Event Scheduler status and stuff. Its easy, you can learn more here: http://dev.mysql.com/doc/refman/5.5/en/create-event.html. Play with it. – celerno Jun 11 '14 at 16:36
  • 2
    How you know that this event will be triggered exactly at 1 am? There should be something like STARTS STR_TO_DATE(DATE_FORMAT(NOW(),'%Y%m%d 0100'),'%Y%m%d %H%i') + INTERVAL 1 DAY – sbrbot Jun 25 '15 at 11:59
2
CREATE TRIGGER user_connections_dump
AFTER INSERT ON user_connections
FOR EACH ROW
DELETE FROM log
WHERE log_date < DATE_SUB(NOW(), INTERVAL 90 DAY)

You should be comparing the date column in the log table, not a literal date. Then you use DATE_SUB to subtract dates, not the Date function.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I've used your delete query in my answer below. – celerno Jun 11 '14 at 16:26
  • Thank you for your input, however I am getting a syntax error saying "unexpected DELETE_SYM, expecting FOR_SYM" – AznDevil92 Jun 11 '14 at 16:27
  • 1
    Apparently `FOR EACH ROW` is required in triggers, even if it's not dependent on the row. Maybe you should be using the event scheduler instead of a trigger. – Barmar Jun 11 '14 at 16:30
1
CREATE EVENT IF NOT EXISTS `Delete_Older_Than_90_Days`
  ON SCHEDULE EVERY 1 DAY
  STARTS STR_TO_DATE(DATE_FORMAT(NOW(),'%Y%m%d 0100'),'%Y%m%d %H%i') + INTERVAL 1 DAY
DO
  DELETE LOW_PRIORITY FROM log WHERE log_date < DATE_SUB(NOW(),INTERVAL 90 DAY)
sbrbot
  • 6,169
  • 6
  • 43
  • 74