我想写一个delete 触发器,如:当删除id=1 这条记录的时候提示不让删除,并回滚事务
一下是我写的,总是报语法错误,帮帮我呵呵CREATE TABLE tb(id INT,NAME VARCHAR(10))
INSERT INTO tb VALUES(1,'张三'),(2,'李四'),(3,'王五')DELIMITER$$
CREATE TRIGGER `trTemplate` BEFORE DELETE ON `tb`
FOR EACH ROW BEGIN
IF old.id=1 THEN
ROLLBACK;
END IF;
END$$DELIMITER;
DELETE FROM tb WHERE id=1DROP TRIGGER trTemplate
一下是我写的,总是报语法错误,帮帮我呵呵CREATE TABLE tb(id INT,NAME VARCHAR(10))
INSERT INTO tb VALUES(1,'张三'),(2,'李四'),(3,'王五')DELIMITER$$
CREATE TRIGGER `trTemplate` BEFORE DELETE ON `tb`
FOR EACH ROW BEGIN
IF old.id=1 THEN
ROLLBACK;
END IF;
END$$DELIMITER;
DELETE FROM tb WHERE id=1DROP TRIGGER trTemplate
Explicit or implicit commit is not allowed in stored function or trigger.Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1
帮我写个这种需求的触发器呵,谢了
http://blog.csdn.net/ACMAIN_CHM/archive/2009/07/25/4380183.aspx
错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1
解释:表示DELIMITER使用错误,应在其后使用空格。DELIMITER $$。你没有加空格。错误码: 1422
Explicit or implicit commit is not allowed in stored function or trigger.解释:首先你建立的BEFORE触发器,这样的话,基本思路就错误了。假如你要用ROLLBACK,那么回滚什么呢在这里?!改为AFTER还有点说的过去,把刚刚删除的回滚。但是mysql不可以给触发器显式或隐式方式开始或结束事务的语句的语句,比如COMMIT,START TRANSACTION,ROLLBACK。
CREATE TRIGGER `trTemplate` AFTER DELETE ON `tb`
FOR EACH ROW BEGIN
IF old.id=1 THEN
INSERT INTO `tb` VALUES (old.id,old.name);
END IF;
;
$$
DELIMITER ;删除之后立即再插入,哈哈。是不是很变态啊。
少了个END
其实和4楼的意思是一样的。
DELIMITER $$
CREATE TRIGGER `trTemplate` AFTER DELETE ON `tb`
FOR EACH ROW BEGIN
IF old.id=1 THEN
INSERT INTO `tb` VALUES (old.id,old.name);
END IF;
END ;
$$
DELIMITER ;
加上END了这次。
呵呵