如何为嵌套表创建触发器

I'm trying to create trigger for table3 to insert new row each I have basics information about triggers this is what I was trying to do however I got error execution interrupted

我正在尝试为table3创建触发器以插入新行每个我有关于触发器的基本信息这是我试图做的但是我的错误执行中断

delimiter #
create trigger TABLE3_INSERT_TRIGGER  after insert on table2
for each row
begin
insert into table3 (tableZ_ID, table2_ID) values (new.tableZ_ID, new.table2_ID);
end#

delimiter ;

        +------------+
        | tableZero  |
        +------------+
        |tableZ_ID(PK|
        +------------+
        |tableZ_Name |
        +------------+
        |table1_ID(FK|
        +------------+

        +------------+
        | table1     |
        +------------+
        |table1_ID(PK|
        +------------+
        |table1_Name |
        +------------+

        +------------+
        | table2     |
        +------------+
        |table2(PK)  |
        +------------+
        |table1 (FK) |
        +------------+
        |table2Info  |
        +------------+


        +------------+
        | table3     |
        +------------+
        |tableZ_ID(FK|
        +------------+
        |table2_ID(FK|
        +------------+

2 个解决方案

#1

according to your table design there must be data available in tableZero otherwise it will give error related to reference key.

根据您的表格设计，必须在tableZero中提供数据，否则它将提供与参考密钥相关的错误。

table 1 -> table 0

表1 - >表0

let me know if want more info

如果想了解更多信息请告诉我

#2

In your trigger for table 2 you are referencing new.tableZ_ID, but there is not a tableZ_ID column in table2.

在表2的触发器中，您引用的是new.tableZ_ID，但table2中没有tableZ_ID列。

In order to get tableZ_ID, you have to join your table2 values back to the table that contains the tableZ_ID column, which is tableZero based on your schema. Given your FK relationships, in order to get from table2 to tableZero, you have to go through table1. The trigger would look something like this:

为了获取tableZ_ID，您必须将table2值连接回包含tableZ_ID列的表，该列是基于您的架构的tableZero。鉴于你的FK关系，为了从table2到tableZero，你必须通过table1。触发器看起来像这样：

delimiter #
create trigger TABLE3_INSERT_TRIGGER  after insert on table2
for each row
begin
INSERT INTO table3 (tableZ_ID, table2_ID) 
SELECT t0.tableZ_ID, new.table2_ID
FROM table1 t1 
INNER JOIN tableZero t0 on t1.table1_ID = t0.table1_ID
WHERE t1.table1_ID = new.table1_ID;
end#

delimiter ;

#1