求3个触发器的写法

Question

-- Table structure for tcharge  
-- ----------------------------
CREATE TABLE `tcharge` (
  `tid` int(10) NOT NULL AUTO_INCREMENT,
  `method` char(16) NOT NULL,
  `from_tag` char(64) NOT NULL,
  `to_tag` char(64) NOT NULL,
  `callid` char(64) NOT NULL,
  `sip_code` char(3) NOT NULL,
  `sip_reason` char(32) NOT NULL,
  `caller` char(64) NOT NULL,
  `callee` char(64) NOT NULL,
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;INSERT INTO `tcharge` VALUES ('53', 'INVITE', '417bff32', '29572476', 'N2QxMTA1ZGU3MDA0MmE0MTY2YzZkNzc4NjM1YWY1ODE.', '200', 'OK', 'sip:[email protected]', 'sip:[email protected]', '2010-08-07 09:26:08');
INSERT INTO `tcharge` VALUES ('54', 'BYE', '29572476', '417bff32', 'N2QxMTA1ZGU3MDA0MmE0MTY2YzZkNzc4NjM1YWY1ODE.', '200', 'OK', 'sip:[email protected]', 'sip:[email protected]', '2010-08-07 09:26:13');
  这个表的数据是成对出现的，callid相同的为一对，method里的INVITE表示成对数据的第一个，BYE表示第二个。
-- Table structure for uinfoe
-- ----------------------------
CREATE TABLE `uinfoe` (
  `ID` int(100) NOT NULL AUTO_INCREMENT,
  `TLEAVINGS` int(50) NOT NULL DEFAULT '0',
  `TENDTIME` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
  `USTATE` varchar(100) NOT NULL DEFAULT '0',  
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2010000007 DEFAULT CHARSET=utf8;INSERT INTO `ctcm_en_userinfoes` VALUES ('2010000005', '20', '2011-01-24 21:42:29', '1');
INSERT INTO `ctcm_en_userinfoes` VALUES ('2010000006',  '0', '2000-01-01 00:00:00', '0');
触发器1：tcharge表的成对数据全部插入后 method为BYE的那条数据的time减去INVITE那条数据的time，得到一个以秒为单位的值。然后uinfoe表的TLEAVINGS减去这个值。
触发器2：当uinfoe表的TLEAVINGS减少后，检查TLEAVINGS是否<=0，如果是，则USTATE更新为0，TLEAVINGS更新为0.如果否，则什么都不做。
触发器3：USTATE=1的数据 ，当TENDTIME里的时间<=当前时间时，USTATE更新为0

Answer 1

这个我在数据库中拿出后进行了修改，漏改一处
CREATE TABLE `tcharge` (
`tid` int(10) NOT NULL AUTO_INCREMENT,
`method` char(16) NOT NULL,
`from_tag` char(64) NOT NULL,
`to_tag` char(64) NOT NULL,
`callid` char(64) NOT NULL,
`sip_code` char(3) NOT NULL,
`sip_reason` char(32) NOT NULL,
`caller` char(64) NOT NULL,
`callee` char(64) NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;

Answer 2

触发器1：tcharge表的成对数据全部插入后 method为BYE的那条数据的time减去INVITE那条数据的time，得到一个以秒为单位的值。然后uinfoe表的TLEAVINGS减去这个值。
例uinfoe表里的数据：
INSERT INTO `uinfoe` VALUES ('2010000005', '20', '2011-01-24 21:42:29', '1');
INSERT INTO `uinfoe` VALUES ('2010000006', '0', '2000-01-01 00:00:00', '0');执行
INSERT INTO `tcharge` VALUES ('53', 'INVITE', '417bff32', '29572476', '2010000005', '200', 'OK', 'sip:[email protected]', 'sip:[email protected]', '2010-08-07 09:26:08');
INSERT INTO `tcharge` VALUES ('54', 'BYE', '29572476', '417bff32', '2010000005', '200', 'OK', 'sip:[email protected]', 'sip:[email protected]', '2010-08-07 09:26:13');需要达到的结果为：
uinfoe 表里id='2010000005'的TLEAVINGS数据减少为15；触发器2：当uinfoe表的TLEAVINGS减少后，检查TLEAVINGS是否<=0，如果是，则USTATE更新为0，TLEAVINGS更新为0.如果否，则什么都不做。
uinfoe 表里的TLEAVINGS数据是不断减少的，当减少<=0时，则
update UINFOE set USTATE='0' , TLEAVINGS='0'本人是个菜鸟，如发帖不规范或描述不清楚，还请前辈多多指教
 

Answer 3

1
DELIMITER $$
DROP TRIGGER IF EXISTS `ee`.`ntc`$$
CREATE TRIGGER `ee`.`ntc` BEFORE INSERT 
    ON `ee`.`tcharge`
    FOR EACH ROW BEGIN
UPDATE uinfoe a INNER JOIN     
    (SELECT callid,SUM(TIMEDIFF(`time`,new.`time`)) AS aa FROM tcharge GROUP BY callid) b
    ON a.id=new.callid
SET a.`TLEAVINGS`=`TLEAVINGS` +COALESCE(aa,0);
    END$$DELIMITER ;2
DELIMITER $$
DROP TRIGGER IF EXISTS `ee`.`ntc1`$$
CREATE TRIGGER `ee`.`ntc1` BEFORE UPDATE
    ON `ee`.`uinfoe`
    FOR EACH ROW BEGIN
IF new.TLEAVINGS<=0 THEN
SET new.TLEAVINGS=0;
SET new.USTATE=0;
END IF;
    END$$DELIMITER ;

Answer 4

delimiter |CREATE TRIGGER tr_tcharge_ai_1 after INSERT ON tcharge
  FOR EACH ROW BEGIN
   declare v_INVITE_time datetime;
  
   if NEW.method = 'BYE' THEN
   select time into v_INVITE_time 
   from tcharge 
   where callid=NEW.callid 
   and method='INVITE' 
   order by tid desc limit 1;
  
   update uinfoe 
   set TLEAVINGS=TLEAVINGS-(UNIX_TIMESTAMP(new.time)-UNIX_TIMESTAMP(v_INVITE_time))
   where ID=new.callid;
   END IF;
  END;
|delimiter ;mysql>
mysql> select * from uinfoe;
+------------+-----------+---------------------+--------+
| ID         | TLEAVINGS | TENDTIME            | USTATE |
+------------+-----------+---------------------+--------+
| 2010000005 |        20 | 2011-01-24 21:42:29 | 1      |
| 2010000006 |         0 | 2000-01-01 00:00:00 | 0      |
+------------+-----------+---------------------+--------+
2 rows in set (0.06 sec)mysql> select * from tcharge;
Empty set (0.02 sec)mysql> delimiter |
mysql>
mysql> CREATE TRIGGER tr_tcharge_ai_1 after INSERT ON tcharge
    ->   FOR EACH ROW BEGIN
    ->          declare v_INVITE_time datetime;
    ->
    ->          if NEW.method = 'BYE' THEN
    ->                  select time into v_INVITE_time
    ->                          from tcharge
    ->                          where callid=NEW.callid
    ->                          and method='INVITE'
    ->                          order by tid desc limit 1;
    ->
    ->                  update uinfoe
    ->                  set TLEAVINGS=TLEAVINGS-(UNIX_TIMESTAMP(new.time)-UNIX_T
IMESTAMP(v_INVITE_time))
    ->                  where ID=new.callid;
    ->          END IF;
    ->   END;
    -> |
Query OK, 0 rows affected (0.03 sec)mysql>
mysql> delimiter ;
mysql> select * from uinfoe;
+------------+-----------+---------------------+--------+
| ID         | TLEAVINGS | TENDTIME            | USTATE |
+------------+-----------+---------------------+--------+
| 2010000005 |        20 | 2011-01-24 21:42:29 | 1      |
| 2010000006 |         0 | 2000-01-01 00:00:00 | 0      |
+------------+-----------+---------------------+--------+
2 rows in set (0.00 sec)mysql> INSERT INTO `tcharge` VALUES ('53', 'INVITE', '417bff32', '29572476', '20
10000005', '200', 'OK', 'sip:[email protected]', 'sip:[email protected]', '2
010-08-07 09:26:08');
Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO `tcharge` VALUES ('54', 'BYE', '29572476', '417bff32', '20100
00005', '200', 'OK', 'sip:[email protected]', 'sip:[email protected]', '2010
-08-07 09:26:13');
Query OK, 1 row affected (0.00 sec)mysql> select * from tcharge;
+-----+--------+----------+----------+------------+----------+------------+-------------------------+-------------------------+---------------------+
| tid | method | from_tag | to_tag   | callid     | sip_code | sip_reason | caller                  | callee                  | time                |
+-----+--------+----------+----------+------------+----------+------------+-------------------------+-------------------------+---------------------+
|  53 | INVITE | 417bff32 | 29572476 | 2010000005 | 200      | OK         | sip:[email protected] | sip:[email protected] | 2010-08-07 09:26:08 |
|  54 | BYE    | 29572476 | 417bff32 | 2010000005 | 200      | OK         | sip:[email protected] | sip:[email protected] | 2010-08-07 09:26:13 |
+-----+--------+----------+----------+------------+----------+------------+-------------------------+-------------------------+---------------------+
2 rows in set (0.00 sec)mysql> select * from uinfoe;
+------------+-----------+---------------------+--------+
| ID         | TLEAVINGS | TENDTIME            | USTATE |
+------------+-----------+---------------------+--------+
| 2010000005 |        15 | 2011-01-24 21:42:29 | 1      |
| 2010000006 |         0 | 2000-01-01 00:00:00 | 0      |
+------------+-----------+---------------------+--------+
2 rows in set (0.00 sec)mysql>

Answer 5

delimiter |CREATE TRIGGER tr_uinfoe_bi before INSERT ON tcharge
  FOR EACH ROW BEGIN
   if new.TLEAVINGS<=0 then
   set new.USTATE=0;
   set new.TLEAVINGS=0;
   end if;
  END;
|delimiter ;

Answer 6

很抱歉，出差中，没有及时回帖
触发器一已经ok
触发器二我做了改动(红色部分)
CREATE TRIGGER tr_uinfoe_bi before updat ON uinfoe
  FOR EACH ROW BEGIN
      if new.TLEAVINGS<=0 then
          set new.USTATE=0;
          set new.TLEAVINGS=0;
      end if;
  END;
|触发器三：由于前几天时间紧张没写触发器三要求，请谅解触发器3
表uinfoe里所有USTATE=1的数据 ，当TENDTIME里的时间<=当前时间时，USTATE更新为0

Answer 7

红色部分update不小心丢个e

Answer 8

1、直接用UPDATE 替换；
2、
CREATE TRIGGER tr_uinfoe_bi before updat ON uinfoe
  FOR EACH ROW BEGIN
  if new.TLEAVINGS<=0 or TENDTIME <=now() then
  set new.USTATE=0;
  set new.TLEAVINGS=0;
  end if;
  END;

Answer 9

不是每次更新才判断下TENDTIME <=now() 而是当TENDTIME <=now()时触发。或每天的0点触发也可

Answer 10

表：
CREATE TABLE `uinfoe` (
  `ID` int(100) NOT NULL AUTO_INCREMENT,
  `TLEAVINGS` int(50) NOT NULL DEFAULT '0',
  `TENDTIME` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
  `USTATE` varchar(100) NOT NULL DEFAULT '0',   
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2010000007 DEFAULT CHARSET=utf8;表里数据：
INSERT INTO `uinfoe` VALUES ('2010000005', '20', '2010-12-7 21:42:29', '1');
INSERT INTO `uinfoe` VALUES ('2010000006', '0', '2000-01-01 00:00:00', '0');触发器触发条件：
TENDTIME<=now() 并且 USTATE='1'（或每天定时触发）结果：
USTATE='0'  TLEAVINGS=0

Answer 11

每天定时触发：可用EVENT解决

Answer 12

参考下贴。另征集定时执行mysql数据库任务方案。
http://topic.csdn.net/u/20091020/22/c51bb709-6dbc-4fd1-b874-077714ed547e.html