1、
DELIMITER |
CREATE TRIGGER table1_insert
BEFORE INSERT
ON table1
FOR EACH ROW
BEGIN
declare v_t1 date;
if new.s1 is not null and new.d1 >= '2010-01-01' then
   if new.d1 < date(new.c1) then
      select t1 into v_t1 from table2 where s1 = new.s1;
      if new.d1 < v_t1 and new.c1 > concat(v_t1, ' 15:00:00') then
         set new.isvalid = 0;
      end if;
   end if;
end if;
END;

2、
DELIMITER |
CREATE TRIGGER table1_update
BEFORE UPDATE
ON table1
FOR EACH ROW
BEGIN
declare v_t1 date;
if new.s1 is not null and new.d1 != old.d1 and new.d1 >= '2010-01-01' then
   if old.isvalid != 1 then
      if new.d1 >= date(new.c1) then
         set new.isvalid = 1;
      end if;
   else
      if new.d1 < date(new.c1) then
         select t1 into v_t1 from table2 where s1 = new.s1;
         if new.d1 < v_t1 and new.c1 > concat(v_t1, ' 15:00:00') then
            set new.isvalid = 0;
         end if;
      end if;
   end if;
end if;
END;

3、
DROP TRIGGER `test`.`test_insert`//
CREATE TRIGGER `test`.`test_insert` BEFORE INSERT ON `test`.`test`
 FOR EACH ROW BEGIN
declare v_t1 date;
if new.s1 is not null and new.d1 >= '2010-01-01' then
   if new.d1 < date(new.c1) then
      select t1 into v_t1 from test2 where s1 = new.s1;
      if new.d1 < v_t1 and new.c1 > concat(v_t1, ' 15:00:00') then
         set new.isvalid = 0;
      end if;
   end if;
end if;
END
//

创建两个表

create database if not exists ENOTICE;

CREATE TABLE ENOTICE.VIO_USERINFO(

ID INT AUTO_INCREMENT NOT NULL comment '自动编号',

DLM VARCHAR(20) NOT NULL UNIQUE comment '登录名' ,

DLMM VARCHAR(20) NOT NULL comment '密码',

PRIMARY KEY (ID)

)ENGINE=INNODB DEFAULT CHARSET=GBK;

create database if not exists supersms;

create table supersms.sms_user(

id int not null auto_increment comment '自动编号',

login_name varchar(200) not null UNIQUE comment '登录名',

password varchar(200) comment '密码',

primary key(`id`)

)ENGINE=InnoDB DEFAULT CHARSET=gbk;

创建触发器

/*同步插入触发器*/

DROP TRIGGER IF EXISTS ENOTICE.tg_sync_insert;

DELIMITER |

CREATE TRIGGER ENOTICE.tg_sync_insert

AFTER INSERT

ON ENOTICE.VIO_USERINFO

FOR EACH ROW

BEGIN

INSERT INTO supersms.sms_user(login_name,password) values (NEW.DLM,NEW.DLMM) ;

END;

|

DELIMITER ;

/*同步删除触发器*/

DROP TRIGGER IF EXISTS ENOTICE.tg_sync_delete;

DELIMITER |

CREATE TRIGGER ENOTICE.tg_sync_delete

AFTER DELETE

ON ENOTICE.VIO_USERINFO

FOR EACH ROW

BEGIN

DELETE FROM supersms.sms_user WHERE login_name=OLD.DLM;

END;

|

DELIMITER ;

/*同步更新触发器*/

DROP TRIGGER IF EXISTS ENOTICE.tg_sync_update;

DELIMITER |

CREATE TRIGGER ENOTICE.tg_sync_update

AFTER UPDATE

ON ENOTICE.VIO_USERINFO

FOR EACH ROW

BEGIN

UPDATE supersms.sms_user SET login_name=NEW.DLM,password=NEW.DLMM WHERE login_name=OLD.DLM;

END;

|

DELIMITER ;

插入测试

mysql> select * from supersms.sms_user;

Empty set (0.00 sec)

mysql> select * from vio_userinfo;

Empty set (0.00 sec)

mysql> insert into vio_userinfo(DLM,DLMM,SITEID,YHLX) values

-> ('a','a',2,'1'),

-> ('b','b',3,'1');

Query OK, 2 rows affected (0.02 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from supersms.sms_user;

+----+------------+----------+

| id | login_name | password |

+----+------------+----------+

|  9 | a          | a        |

| 10 | b          | b        |

+----+------------+----------+

2 rows in set (0.00 sec)

mysql> select * from vio_userinfo;

+----+-----+------+

| ID | DLM | DLMM |

+----+-----+------+

| 14 | a   | a    |

| 15 | b   | b    |

+----+-----+------+

2 rows in set (0.00 sec)

更新测试

mysql> update vio_userinfo set dlm='c' where dlm='a';

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from supersms.sms_user;

+----+------------+----------+

| id | login_name | password |

+----+------------+----------+

|  9 | c          | a        |

| 10 | b          | b        |

+----+------------+----------+

2 rows in set (0.00 sec)

mysql> select * from vio_userinfo;

+----+-----+------+

| ID | DLM | DLMM |

+----+-----+------+

| 14 | c   | a    |

| 15 | b   | b    |

+----+-----+------+

2 rows in set (0.00 sec)

删除测试

mysql> delete from vio_userinfo where id=15;

Query OK, 1 row affected (0.02 sec)

mysql> select * from supersms.sms_user;

+----+------------+----------+

| id | login_name | password |

+----+------------+----------+

|  9 | c          | a        |

+----+------------+----------+

1 row in set (0.00 sec)

mysql> select * from vio_userinfo;

+----+-----+------+

| ID | DLM | DLMM |

+----+-----+------+

| 14 | c   | a    |

+----+-----+------+

1 row in set (0.00 sec)