56,687
社区成员
发帖
与我相关
我的任务
分享
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 ;
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:615102@192.168.1.78', 'sip:610186@192.168.1.78', '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:610186@192.168.1.78', 'sip:615102@192.168.1.78', '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:615102@192.168.1.78 | sip:610186@192.168.1.78 | 2010-08-07 09:26:08 |
| 54 | BYE | 29572476 | 417bff32 | 2010000005 | 200 | OK | sip:610186@192.168.1.78 | sip:615102@192.168.1.78 | 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>