如何执行实现此条件的触发器？ [英] How to do trigger that implements this condition?

问题描述

我需要实现此过程的触发器：

I need trigger that implements this process:

我应该+向该用户添加一些数字50到表 Money 在表 paym 中的两列 table1 和 table2 不为空。

I should +add some number 50 to that user to table money where in table paym both columns table1 and table2 are not empty.

例如：用户'John'的两列均不为空，并且向他添加了<$表 money 中的c $ c> 50 。

For example: User 'John' has both columns not empty and to him added 50 in table money.

表中的示例下方：

表： paym

table: paym

 ID        username        table1        Table2  
+-------+-------------+-------------+-----------+
|   1   |  John       |  Value      |    Value  |
+-------+-------------+-------------+-----------+
|   2   |  Alex       |  Null       |    Null   |
+-------+-------------+-------------+-----------+

表： money

Table: money

 ID      username      total_money      
+-------+-------------+-------------+
|   1   |  John       |     50      | 
+-------+-------------+-------------+
|   2   |  Alex       |     0       |
+-------+-------------+-------------+

查询：（不确定是否正确）

UPDATE 
 money
SET 
 money.total_money = money.total_money + 50
INNER JOIN 
 paym
ON
 money.username = paym.username
WHERE
   (paym.Table1 IS NOT NULL OR paym.Table1 <> '')
 AND
   (paym.Table2 IS NOT NULL OR paym.Table2 <> '')

推荐答案

以下是为此目的的触发器：

Here is a trigger for that purpose:

DELIMITER $$
CREATE trigger update_money_after_paym
AFTER INSERT ON paym
FOR EACH ROW
BEGIN
    IF (NEW.table1 IS NOT NULL AND NEW.table2 IS NOT NULL) THEN
        UPDATE money SET total_money = total_money + 50 WHERE username = NEW.username;
    END IF;
END;
$$
DELIMITER;

代码将在表 paym 。如果新插入的记录的两个列 table1 和 table2 都设置为非 NULL 值，然后触发器运行 UPDATE 查询，向表<$中的列 total_money 添加50 用户名与 paym money $ c>。

The code will run after each insert on table paym. If the newly inserted record has both columns table1 and table2 set to non-NULL values, then the trigger runs an UPDATE query that adds 50 to column total_money in table money for the record that has the same username as the newly inserted record in paym.

这篇关于如何执行实现此条件的触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！