有dbadapter数据库下的表sms_sent表列如下
massmsid gwsmsid sismsid extcode destaddr requesttime senttime sentresult smsstatus
在tdzy数据库下有相同结构相同名称的表
现在我需要在dbadapter数据库下的sms_sent表中添加两个触发器,,一个在sms_sent表中插入数据时同步插入到tdzy库下的sms_sent表中,另外一个在dbadapter数据库下的sms_sent中的sentresult字段更新时同步更新到tdzy库下的sms_sent表中,请问这个触发器要如何写,谢谢
massmsid gwsmsid sismsid extcode destaddr requesttime senttime sentresult smsstatus
在tdzy数据库下有相同结构相同名称的表
现在我需要在dbadapter数据库下的sms_sent表中添加两个触发器,,一个在sms_sent表中插入数据时同步插入到tdzy库下的sms_sent表中,另外一个在dbadapter数据库下的sms_sent中的sentresult字段更新时同步更新到tdzy库下的sms_sent表中,请问这个触发器要如何写,谢谢
解决方案 »
- 软件运行一段时间后,增加了一些功能,因此当前的数据库与旧数据库相比增加了一些表和字段,旧数据库中的表和字段在当前数据库中都有,请问怎样将客户旧数据库中已经录入的
- mysql union 与union all 使用
- 请教,一个统计问题
- mysql 导入CVS问题
- MYSQL在大于1G的。。。
- MYSQL数据库转移的问题。#12 - Can't read dir of './discuz2_back/' (Errcode: 13)
- 请教交手机话费的系统其数据库中的表结构是怎么样的??
- win2000下如何配置mysql4.1(a)
- LOAD DATA FILE为什么每次只导入一半的记录?
- mysql数据库多表查询
- MySQL 左右值法计算问题,请大家指点一下
- mysql中两个表的合并问题
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);delimiter |CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|delimiter ;INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);Suppose that you insert the following values into table test1 as shown here: mysql> INSERT INTO test1 VALUES
-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0As a result, the data in the four tables will be as follows: mysql> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)mysql> SELECT * FROM test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)mysql> SELECT * FROM test4;
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)
begin
insert into tdzy.sms_sent (select * from dbadapter.sms_sent where id=NEW.id);
end
因为两个表只是同步数据,所以从表的ID不能设计为自增,为主键即可。
#当更新数据时,使用的触发器,比如我的一个主表是test.dd,从表是szoa.dd,可以在update before设置以下触发器,当字段bb发生变化时触发,在MYSQL中NEW代表当前数据库的最新纪录NEW.字段就是他的值。
begin
if(OLD.bb<>NEW.bb) then
update szoa.dd set szoa.dd.bb=NEW.bb where id=OLD.id;
end if;
end
FOR EACH ROW BEGIN
insert into tdzy.sms_sent (select * from dbadapter.sms_sent where masmmsid=NEW.masmmsid);
end;
这是为什么了?
MySQL Data Transfer
Source Host: localhost
Source Database: tdzy
Target Host: localhost
Target Database: tdzy
Date: 2010-7-8 11:32:14
*/SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for sms_sent
-- ----------------------------
DROP TABLE IF EXISTS `sms_sent`;
CREATE TABLE `sms_sent` (
`massmsid` varchar(50) collate utf8_bin NOT NULL,
`gwsmsid` varchar(50) collate utf8_bin default NULL,
`sismsid` varchar(50) collate utf8_bin NOT NULL,
`extcode` varchar(21) collate utf8_bin default NULL,
`destaddr` varchar(21) collate utf8_bin NOT NULL,
`requesttime` datetime NOT NULL,
`senttime` datetime NOT NULL,
`sentresult` int(11) NOT NULL,
`smsstatus` varchar(20) collate utf8_bin default NULL,
`statustime` datetime default NULL,
`applicationid` varchar(16) collate utf8_bin NOT NULL,
PRIMARY KEY (`massmsid`),
KEY `index_2` (`sismsid`),
KEY `index_3` (`gwsmsid`),
KEY `index_4` (`destaddr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;-- ----------------------------
-- Records
-- ----------------------------
MySQL Data Transfer
Source Host: localhost
Source Database: dbadapter
Target Host: localhost
Target Database: dbadapter
Date: 2010-7-8 17:28:23
*/SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for sms_sent
-- ----------------------------
DROP TABLE IF EXISTS `sms_sent`;
CREATE TABLE `sms_sent` (
`massmsid` varchar(50) collate utf8_bin NOT NULL,
`gwsmsid` varchar(50) collate utf8_bin default NULL,
`sismsid` varchar(50) collate utf8_bin NOT NULL,
`extcode` varchar(21) collate utf8_bin default NULL,
`destaddr` varchar(21) collate utf8_bin NOT NULL,
`requesttime` datetime NOT NULL,
`senttime` datetime NOT NULL,
`sentresult` int(11) NOT NULL,
`smsstatus` varchar(20) collate utf8_bin default NULL,
`statustime` datetime default NULL,
`applicationid` varchar(16) collate utf8_bin NOT NULL,
PRIMARY KEY (`massmsid`),
KEY `index_2` (`sismsid`),
KEY `index_3` (`gwsmsid`),
KEY `index_4` (`destaddr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;-- ----------------------------
-- Records
-- ----------------------------
这个不是吗
这个不是吗
是吗?!
it is already used by statement which invoked this stored function/trigger.because
it is already used by statement which invoked this stored function/trigger.触发器代码如下
-- ----------------------------
-- Trigger structure for DBINSET
-- ----------------------------
DELIMITER ;;
CREATE TRIGGER `DBINSET` AFTER INSERT ON `sms_sent` FOR EACH ROW insert into tdzy.sms_sent (select * from dbadapter.sms_sent where massmsid=NEW.massmsid);;
DELIMITER ;-- ----------------------------
-- Trigger structure for DBUPDATE
-- ----------------------------
DELIMITER ;;
CREATE TRIGGER `DBUPDATE` AFTER UPDATE ON `sms_sent` FOR EACH ROW if(OLD.sentresult<>NEW.sentresult) then
update tdzy.sms_sent set tdzy.sms_sent.sentresult=NEW. tdzy.sms_sent where massmsid=OLD.massmsid;
end if;;
DELIMITER ;
网上说是死循环但是我已经是不同的表啦,,难道要表名字也不同
mysql> DELIMITER ;;
mysql> CREATE TRIGGER `DBINSET` AFTER INSERT ON `sms_sent` FOR EACH ROW insert i
nto tdzy.sms_sent (select * from dbadapter.sms_sent where massmsid=NEW.massmsid)
;;
Query OK, 0 rows affected (0.08 sec)mysql> DELIMITER ;
mysql>