Click here to Skip to main content
14,869,580 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
Thomas ktg 16-Oct-13 6:17am
   
Create an event scheduling in MySql to do this.

1 solution

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.
   
Comments
Channa Senevirathne Bandara 18-Oct-13 13:58pm
   
thank your advice i created this Solution

<pre lang="SQL">CREATE DEFINER=`root`@`localhost` EVENT `OfferDetailTable` ON SCHEDULE EVERY 1 DAY STARTS '2013-10-16 15:54:07' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM Offer_detail
WHERE offer_to < DATE_SUB(NOW(), INTERVAL 1 DAY)</pre>

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900