automatically delete data using date mysql trigger

Question

0.00/5 (No votes)

See more:

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

SQL

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,

<big></big>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 delete offer detail from datatable if offer_to less than currectdate

like this

SQL

DELETE FROM Offer_detail WHERE offer_to< NOW()

i need do this Automatically how do this

Posted 15-Oct-13 23:38pm

Channa Senevirathne Bandara

Add a Solution

Comments

Thomas ktg 16-Oct-13 6:17am

Create an event scheduling in MySql to do this.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Answer 1 · 2013-10-17T02:56:00

You have a significant problem in your table design: your offer_from and offer_to fields are defined as varchar(80) - so comparing to NOW() will never work. You should declare them both as datetime fields.

Once you have that fixed, you can take Thomas ktg's comment, the proper solution, and implement it.

If you insist on using triggers, you can mostly handle it by having triggers on insert and update delete all table element that satisfy your < NOW() criterion. This will, of course, not remove expired items if no one is modifies the table in time.

Another option: use a computed field set to true/false, based upon the date comparison. Have your queries ignore fields that are expired base upon how you define your true/false.

Again - all of these depend upon you changing your varchar to datetime so proper comparisons can be made - or, I suppose, you could try to convert the varchar to datetime values on the fly.