如何在两个表之间编写更新触发器 [英] How can I write an update trigger between two tables

问题描述

我有两个表，一个叫做违约者和用户表.defaulters有mypin和debt_amount，用户有mypin和debtor.i想要一个触发器更新用户表中的debtor如果mypin存在于用户表中，其中debt_amount = 0在违约者中。如果debaulters表中的mypin在debt_amount中的值为零，则触发器将在debtor中输入零值。

数据库链接



我尝试过：

i have two tables one is called defaulters and users table .defaulters has mypin and debt_amount and users has mypin and debtor.i want a trigger that update debtor in users table if mypin exist in users table where debt_amount=0 in defaulters .the trigger will input zero value in debtor if mypin in defaulters table has a zero value in debt_amount.
database link

What I have tried:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT,
    debtor int(10),
    myPIN varchar(255),
    PRIMARY KEY (user_id)
)  ENGINE=INNODB;

CREATE TABLE defaulters (
    defaulters_id INT AUTO_INCREMENT,
    myPIN varchar(255),
    debt_amount varchar(255),
    PRIMARY KEY (defaulters_id)
)  ENGINE=INNODB; 

这是我试过的，它给出了一个错误

this what i have tried and its is giving an error

CREATE TRIGGER `after_update_defaulters` AFTER INSERT ON `defaulters`
AFTER insert on defaulters
FOR EACH ROW 
BEGIN
       IF ( EXISTS (
            SELECT DISTINCT `myPIN` FROM defaulters WHERE `Debt_Amount` = 0;
           )      
    )
    THEN
        UPDATE users 
        SET debtor = 0;
        WHERE myPIN = NEW.myPIN;
     END IF;
END

推荐答案

尝试删除分号; SELECT 语句的结尾：

Try to remove the semicolon ; at the end of the SELECT statement:

IF ( EXISTS (
   SELECT DISTINCT `KRAPIN` FROM defaulters WHERE `Debt_Amount` = 0
)

如果你要在彼此旁边发出几个语句，你只需要分号。这里 SELECT 子句是作为 EXISTS 语句的输入参数。



来源：13.2.11.6带有EXISTS或NOT EXISTS的子查询| MySQL 8.0参考手册 [ ^ ]

You only need semicolons if your are issuing several statements next to each-other. Here the SELECT clause is taken as an input argument to the EXISTS statement.

Source: 13.2.11.6 Subqueries with EXISTS or NOT EXISTS | MySQL 8.0 Reference Manual[^]

这是完美的工作

this is working perfect

DELIMITER //
CREATE TRIGGER `after_update_debt_defaulters` AFTER UPDATE ON `defaulters`
 FOR EACH ROW BEGIN
       IF ( EXISTS (
            SELECT DISTINCT `KRAPIN` FROM defaulters WHERE defaulters.Debt_Amount = '0'
           )   
       )
        THEN
            UPDATE users 
            SET users.debtor = '0'
            WHERE KRAPIN = NEW.KRAPIN;
        END IF;
END

这篇关于如何在两个表之间编写更新触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！