CODEWITHSUNDEEP
phpmysqlsqldatabasetriggers
Channa S. Bandara
Channa S. Bandara

Reputation: 11

Automatically delete data using date MySQL trigger

I need to use trigger for one of my database, this is my offer data table

CREATE TABLE IF NOT EXISTS `offer_detail` (
  `id` int(60) NOT NULL AUTO_INCREMENT,
  `company_id` int(60) NOT NULL,
  `offertype_id` int(60) NOT NULL,
  `category_id` int(60) NOT NULL,
  `place_id` int(100) NOT NULL,
  `offer_title` varchar(150) NOT NULL,
  `offer_description` text NOT NULL,
  `image` varchar(150) NOT NULL,
  `coupon_id` varchar(60) NOT NULL,
  `price` text NOT NULL,
  `unit_id` varchar(60) NOT NULL,
  `price_now` varchar(60) NOT NULL,
  `offer_from` varchar(80) NOT NULL,
  `offer_to` varchar(80) NOT NULL,
  `user_id` int(10) NOT NULL,
  `created` varchar(11) NOT NULL,
  `modified` varchar(10) NOT NULL,
  `Active` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);

I get offer duration using offer_from to offer_to

I need to delete offer detail from datatable if offer_to is earlier than current time, like this :

DELETE FROM Offer_detail
WHERE offer_to< NOW()

I need do this automatically, how to do this?

Upvotes: 1

Views: 2574

Answers (2)

Jelle Ferwerda
Jelle Ferwerda

Reputation: 1229

You can schedule this using CRON jobs; e.g. you set a cron to run a delete script once every 24 hours, or though MySQL scheduling tools.

Better would be to write your application in a way that you never get offers from the database which have expired. Keeping data in your database will help you later to run stats such as how many offers did we make, how many did this client accept and how many refuse etcetc.

You can automatically in your queries test whether a datetime colum OfferExpiryDate has a date > now(). That will only get you hits for still-valild offers.

Upvotes: 0

VancleiP
VancleiP

Reputation: 657

MySQL Event Scheduler (MySQL 5.1 >)

With the Event Scheduler you can schedule tasks that you want to perform on your database. This is great for web developers who can’t create cron jobs on their webspace, because their host won’t let them! It really is a great replacement for cron!

Example:

CREATE EVENT PurgeOfferDetailTable
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
DELETE FROM Offer_detail
WHERE offer_to< NOW()
END

Upvotes: 3

Related Questions