使用日期mysql触发器自动删除数据 [英] automatically delete data using date mysql trigger

问题描述

我需要为我的一个数据库使用触发器这是提供数据表

I need to use trigger for one of my database this is 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,

<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使用 offer_from 来获取优惠期限 offer_to



i需要删除优惠详情来自datatable如果offer_to小于currectdate



喜欢这个

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

DELETE FROM Offer_detail WHERE offer_to< NOW()

i需要自动执行此操作

i need do this Automatically how do this

推荐答案

您的表设计中存在严重问题：您的offer_from和offer_to字段被定义为varchar（80） - 因此与NOW（）相比将永远不会起作用。你应该将它们都声明为日期时间字段。



一旦你修复了，你可以采取Thomas ktg的评论，正确的解决方案，并实施它。



如果你坚持使用触发器，你通常可以通过在insert和update上删除所有满足你<的表元素来处理它。现在（）标准。当然，如果没有人及时修改表格，这将不会删除过期的项目。



另一种选择：使用基于true / false的计算字段设置在比较日期。根据您如何定义true / false，让您的查询忽略过期的字段。



再次 - 所有这些都取决于您将varchar更改为datetime以进行适当的比较可以制作 - 或者，我想，您可以尝试将varchar转换为动态日期时间值。

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.

这篇关于使用日期mysql触发器自动删除数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！