有一个表 , 这个表有2个外键
p_id,e_id(也只有这2个字段)。现在需要有一个功能, 当删除这个ss表某条记录的时候
他所对应的 e—id对应的主表的该条记录也跟着删除我思考了 只能用触发器实现。
但不知道如何写?CREATE TABLE `ss` (
`p_id` int(11) NOT NULL ,
`e_id` int(10) unsigned NOT NULL COMMENT 'It''s id of element',
PRIMARY KEY (`pn_id`,`ele_id`),
KEY `fk_ele_id (`ele_id`),
CONSTRAINT `fk_ele_` FOREIGN KEY (`e_id`) REFERENCES `elemen` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pn_` FOREIGN KEY (`p_id`) REFERENCES `Part` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT
p_id,e_id(也只有这2个字段)。现在需要有一个功能, 当删除这个ss表某条记录的时候
他所对应的 e—id对应的主表的该条记录也跟着删除我思考了 只能用触发器实现。
但不知道如何写?CREATE TABLE `ss` (
`p_id` int(11) NOT NULL ,
`e_id` int(10) unsigned NOT NULL COMMENT 'It''s id of element',
PRIMARY KEY (`pn_id`,`ele_id`),
KEY `fk_ele_id (`ele_id`),
CONSTRAINT `fk_ele_` FOREIGN KEY (`e_id`) REFERENCES `elemen` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pn_` FOREIGN KEY (`p_id`) REFERENCES `Part` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT
after delete
on ss
for each row
begin
delete from elemen where id=old.e_id;
end;
Query OK, 0 rows affected (0.00 sec)mysql> delimiter //
mysql> create trigger tr_test
-> before insert
-> on tb_test
-> for each row
-> begin
-> if left(new.re,4)='test' then
-> delete from hehe;
-> end if;
-> end;
-> //
Query OK, 0 rows affected (0.01 sec)mysql> insert into tb_test values(1,'aaa');
-> //
Query OK, 1 row affected (0.05 sec)mysql> delimiter ;
mysql> insert into tb_test values(1,'testbbb');
ERROR 1146 (42S02): Table 'test.hehe' doesn't exist
mysql> select * from tb_test;
+------+--------+
| id | re |
+------+--------+
| 1 | aaa |
+------+--------+
1 row in set (0.00 sec)mysql> insert into tb_test values(1,'ccc');
Query OK, 1 row affected (0.00 sec)mysql> select * from tb_test;
+------+--------+
| id | re |
+------+--------+
| 1 | aaa |
| 1 | ccc |
+------+--------+
2 rows in set (0.00 sec)mysql> insert into tb_test values(1,'test--no');
ERROR 1146 (42S02): Table 'test.hehe' doesn't exist
mysql> select * from tb_test;
+------+--------+
| id | re |
+------+--------+
| 1 | aaa |
| 1 | ccc |
+------+--------+
2 rows in set (0.00 sec)mysql>
for each row
Delete FROM elemen Where id = OLD.e_id;
参考你的写法 建立下面的表 发现失败CREATE TABLE `a1` (
`re` varchar(10) default NULL,
`id` int(11) NOT NULL auto_increment,
`a` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `ids_ab` (`a`,`id`),
KEY `idx_id` (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;DELIMITER ;;
CREATE TRIGGER `Ar_te2st` AFTER INSERT ON `a1` FOR EACH ROW begin
declare v_num int;
if left(new.re,4)='test' then
delete from hehe;
end if;
end;;
DELIMITER ;
insert into a1(a,re) values(1,'ddd')
失败
发现说hehe表不能找到 记录插入不进去
其他版本都是ok 幸亏换了思路 否则走进死胡同