大家好,请假下一个关于TRIGGER的问题我有2个DATABASE,分别为BASE1,BASE2BASE1中有TABLE1表,字段为USER_ID,PASSWORD等
BASE2中有TABLE2表,字段为USER_ID,CREATE_TIME 2个字段我现在创建一个TRIGGER,想实现如果TABLE1表有数据追加,就把USER_ID和当前时间(LONG型)追加到TABLE2表中具体code如下:mysql> delimiter $$
mysql> create trigger trigger_insert after insert on BASE1.TABLE1 for each row
-> begin
-> insert into BASE2.TABLE2(USER_ID,CREATE_TIME) values(new.USER_ID,now()+0);
-> end;
-> $$
ERROR 1435 (HY000): Trigger in wrong schema
请教下,是我写的TRIGGER有问题么?
BASE2中有TABLE2表,字段为USER_ID,CREATE_TIME 2个字段我现在创建一个TRIGGER,想实现如果TABLE1表有数据追加,就把USER_ID和当前时间(LONG型)追加到TABLE2表中具体code如下:mysql> delimiter $$
mysql> create trigger trigger_insert after insert on BASE1.TABLE1 for each row
-> begin
-> insert into BASE2.TABLE2(USER_ID,CREATE_TIME) values(new.USER_ID,now()+0);
-> end;
-> $$
ERROR 1435 (HY000): Trigger in wrong schema
请教下,是我写的TRIGGER有问题么?
drop trigger if exists a;
CREATE TRIGGER `test`.`a` BEFORE INSERT ON test.t10 FOR EACH ROW
BEGIN
insert into dbbin.t1 values(now()+0);
END;
没有问题。
你在创建之前USE DB1这样看看。
我怀疑是不是trigger不支持跨数据库的操作啊?
mysql> use test
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.09 sec)mysql> create table t1(col int);
Query OK, 0 rows affected (0.01 sec)mysql> use t1
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.10 sec)mysql> create table t1(col int);
Query OK, 0 rows affected (0.01 sec)mysql> create trigger trigger_insert after insert
-> on t1
-> for each row
-> insert into test.t1 select new.col;
Query OK, 0 rows affected (0.28 sec)mysql> insert t1 select 1;
Query OK, 1 row affected (0.19 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> use test
Database changed
mysql> select * from t1;
+------+
| col |
+------+
| 1 |
+------+
不好意思,可能是我漏说了,再追加下问题描述:目前BASE1中TABLE1已经有一个trigger存在,似乎在同一database中针对数据表不允许复数个trigger的存在,所以我想在数据库BASE2中创建一个新的trigger, 才出现了下面的问题。。
mysql> use BASE2;
mysql> create table TABLE2(USER_ID varchar(20), CREATE_TIME varchar(40))Type=InnoDB;
mysql>
mysql> delimiter $$
mysql> create trigger trigger_insert after insert on BASE1.TABLE1 for each row
-> begin
-> insert into BASE2.TABLE2(USER_ID,CREATE_TIME) values(new.USER_ID,now()+0);
-> end;
-> $$
ERROR 1435 (HY000): Trigger in wrong schema
同一个表同一类型的触发器只允许一个
变通一下
create trigger trigger_insert after insert on BASE1.TABLE1 for each row
==》
create trigger trigger_insert before insert on BASE1.TABLE1 for each row或者吧这个触发器的内容 放到本来那个触发器里面