更新时间:2023-11-14 19:35:16
是的,但是如何实现取决于您的版本.
Yes you can, but how you do it depends on your version.
首先,触发器本身就是事务性的;根据您的情况,您有一个插入触发器,可以执行另外两次插入.如果其中之一失败,您将获得理想的效果.
First of all, triggers are themselves transactional; in your situation, you have an insert trigger that performs two further inserts. If one of those fails, you will get your desired effect.
请考虑以下示例:
CREATE TABLE a (colA INT);
CREATE TABLE b (colB INT);
CREATE TABLE c (colC INT);
delimiter :
CREATE TRIGGER testtrig BEFORE INSERT ON a
FOR EACH ROW BEGIN
INSERT INTO b(colB) VALUES(NEW.colA);
INSERT INTO c(banana) VALUES (NEW.colA); -- note the faulty column name
END;:
delimiter ;
现在,当我运行失败的插入操作时,会发生这种情况:
Now, when I run an insert that fails, this happens:
mysql> INSERT INTO a VALUES (5);
ERROR 1054 (42S22): Unknown column 'banana' in 'field list'
mysql> SELECT * FROM a;
Empty set (0.00 sec)
这符合您想要的结果.
更一般而言,如果您有逻辑,可以在尝试插入之前用来验证数据,则可以通过以下不同方式使触发器失败:
More generally, if you have logic you can use to validate your data before attempting the insert, you can fail the trigger in different ways:
我猜您正在使用问题链接中的5.0,因此,如果需要,您可以执行故意的错误(例如,故意插入无效列中),以使触发器失败.但是,您在问题中描述的情况已经按照我的答案开头所述进行了事务处理.
I'm guessing you're using 5.0 from the link in your question, so if you need to, you can perform a deliberate error, for example deliberately insert into an invalid column, to fail a trigger. However, the situation you describe in your question is already handled transactionally, as described at the start of my answer.