如何执行实现此条件的触发器? [英] 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 $ c $中的每次插入之后运行c>。如果新插入的记录的两个列
table1
和 table2
都设置为非 NULL
值,然后触发器运行 UPDATE
查询,向表<$中的列 total_money
添加50 用户名
与 paym $ c中新插入的记录相同的记录的c $ c> 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屋!