SQL触发器导致错误 [英] SQL triggers cause errors

问题描述

让我们创建一个TestParent表，该表将num列的总值缓存在TestChild表中，并插入一些行.

Let's create a TestParent table which caches the total value of the num column in a TestChild table, and insert some rows.

CREATE TABLE TestParent (
id INT NOT NULL PRIMARY KEY,
total INT NOT NULL DEFAULT 0);

CREATE TABLE TestChild (
parent_id INT NOT NULL,
num INT NOT NULL);

INSERT INTO TestParent (id) VALUES (123);
INSERT INTO TestChild (parent_id, num) VALUES (123, 1);

CREATE PROCEDURE Sync (IN parent INT)
    UPDATE TestParent SET total = (
        SELECT SUM(num) FROM TestChild WHERE parent_id=parent)
    WHERE id=parent;

CALL Sync (123);

到目前为止，太好了.现在，我想自动调用Sync ...

So far, so good. Now, I want Sync to be called automatically...

CREATE TRIGGER TestInsert
AFTER INSERT ON TestChild
FOR EACH ROW CALL Sync (parent_id);

这也可以.现在，

INSERT INTO TestChild (parent_id, num) VALUES (123, 1);

给予

#1054 - Unknown column 'parent_id' in 'field list'

插入已发生，但尚未调用存储过程.发生了什么事?

The insert has happened, but the stored procedure hasn't been called. What's going on?

推荐答案

您需要使用NEW.parent_id来引用刚刚插入的值

You need to use a NEW.parent_id to refer to a value that just got inserted

CREATE TRIGGER TestInsert
AFTER INSERT ON TestChild
FOR EACH ROW CALL Sync (NEW.parent_id);

这篇关于SQL触发器导致错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！