两个表,
tbl1(id, attr1, attr2);
tbl2(id, attr1, attr2);在执行insert tbl2(id) values(...)的时候,会触动触发器,tbl2.id=tbl1.id时的attr1和attr2的值,并插入到tbl2的相应字段中!能否做到?
tbl1(id, attr1, attr2);
tbl2(id, attr1, attr2);在执行insert tbl2(id) values(...)的时候,会触动触发器,tbl2.id=tbl1.id时的attr1和attr2的值,并插入到tbl2的相应字段中!能否做到?
解决方案 »
- 帮忙帮忙帮忙帮忙
- 请各位大侠帮我解决一个数据导入导出的问题--在线等。。。。。。。
- Mysql在Redhat Linux 5 Server
- 求一sql语句
- Mysql怎么运行DOS命令?
- (大家好)这样的语句在mysql里能通过吗:select f1 from table1 where f2 in (select f3 from table2);
- MYSQL环境变量如何设置?
- 怎样卸载MyODBC???????????
- 为什么我在jsp中往mysql库中输入的中文字成了乱码?
- MySQL中如何实现Oracle中decode函数的效果
- mysql 现在是否有database link功能?
- 求助大虾有关mysql树的问题
tbl2是不允许的,
tbl
在AFTER INSERT 中
insert into tbl1
select a.attr1,a.attr2 from tbl2 a inner join tbl1 on tbl2.id=tbl1.id
从tbl1中找到相应记录的值插入到tbl2中。
举个例子,在执行语句 insert into tbl2(id) (5);的时候,能够将tbl1中id为5的attr1, attr2插入到tbl2的attr1,attr2里!tbl1:
id attr1 attr2
5 s1 s2执行 insert into tbl2(id) (5);
tbl2:
id attr1 attr2
5 NULL NULL
触动触发器,则变为:
tbl2:
id attr1 attr2
5 s1 s2tbl2中attr1和attr2的值是通过触动触发器从tbl1取得并更新的!这样的需求能实现吗?
?
create trigger trInsert on tbl1 for insert
as
insert into tbl2(id);
declare @id as int;
declare @at1 as varchar(200);
declare @at2 as varchar(200);
select @id=last_insert_id() from tbl2;
select id, @at1=attr1, @at2=attr2 from tbl1 where id=@id;
update tbl2 set attr1=@at1,attr2=@at2 where id=@id;
create trigger trInsert on tbl2 for insert
as
insert into tbl2(id);
declare @id as int;
declare @at1 as varchar(200);
declare @at2 as varchar(200);
select @id=last_insert_id() from tbl2;
select id, @at1=attr1, @at2=attr2 from tbl1 where id=@id;
update tbl2 set attr1=@at1,attr2=@at2 where id=@id;
应该建在tbl2上面
是因为要避免死循环吗?
不过我这个是在tbl2插入数据后,从tbl1寻求数据!
+----+-------+-------+
| id | attr1 | attr2 |
+----+-------+-------+
| 5 | s1 | s2 |
+----+-------+-------+
1 row in set (0.00 sec)mysql> delimiter |
mysql> CREATE TRIGGER testref BEFORE INSERT ON tbl2
-> FOR EACH ROW BEGIN
-> declare c1,c2 varchar(20);
-> select attr1,attr2 into c1,c2 from tbl1 where id=new.id;
-> set new.attr1=c1;
-> set new.attr2=c2;
-> END;
-> |
Query OK, 0 rows affected (0.09 sec)mysql> delimiter ;
mysql>
mysql> insert into tbl2 (id) values (5);
Query OK, 1 row affected (0.05 sec)mysql> select * from tbl2;
+----+-------+-------+
| id | attr1 | attr2 |
+----+-------+-------+
| 5 | s1 | s2 |
+----+-------+-------+
1 row in set (0.00 sec)mysql>
使用before或after触发器都可以完成你的需求,使用before insert更简单些。CREATE TRIGGER testref BEFORE INSERT ON tbl2
FOR EACH ROW BEGIN
declare @at1 as varchar(128);
declare @at2 as varchar(128);
select id, @at1=attr1, @at2=attr2 from tbl1 where id=NEW.id;
insert into tbl2(id,attr1,attr2) values(NEW.id,if(@at1,@at1,NEW.attr1),if(@at2,@at2,NEW.attr2));
END
纠正一下,因为插入的id在tbl1中可能不存在,必须要考虑到,最严谨的写法:CREATE TRIGGER testref BEFORE INSERT ON tbl2
FOR EACH ROW BEGIN
declare @at1 varchar(128);
declare @at2 varchar(128);
select id, @at1=attr1, @at2=attr2 from tbl1 where id=NEW.id;
set NEW.attr1=if(@at1,@at1,NEW.attr1);
set NEW.attr2=if(@at2,@at2,NEW.attr2);
END
CREATE TRIGGER testref BEFORE INSERT ON tbl2
FOR EACH ROW BEGIN
select attr1, attr2 into @ee1,@ee2 from tbl1 where id=NEW.id;
set NEW.attr1=@ee1;
set NEW.attr2=@ee2;
END