mySQL在触发器中比较两个不同的列值

Question

我试图有一个触发检查，如果两个值在一个表是相同的，如果是这样，将信息插入到另一个表。我的代码如下:

user_id是INT(10)post_author_id是一个VARCHAR(33) 8utf_unicode_ci

CASE
WHEN (new.user_id = (SELECT CAST(post_author_id AS UNSIGNED INTEGER) FROM Cvr_hooks_501_1000)) 
THEN
INSERT INTO Cvr_1_link_501_1000 (a_hook_id, a_user_id, a_post_id, a_post_author_id)
VALUES (new.hook_id, 
new.user_id, 
new.post_id, 
new.post_author_id);
END CASE

任何帮助都是感激的!

Answer 1

您可以使用EXISTS来检查用户是否已经存在

CREATE tABLE Cvr_hooks_501_1000(post_author_id vaRCHAR(39))

INSERT INTO Cvr_hooks_501_1000 VALUES ("1")

CREATE TABLE account(hook_id int, user_id int(11) ,post_id int, post_author_id int)

CREATE TABLE Cvr_1_link_501_1000 (a_hook_id int, a_user_id int(11) ,a_post_id int, a_post_author_id int)

CREATE TRIGGER upd_check AFTER  INSERT ON account
FOR EACH ROW
BEGIN
IF ( EXISTS(SELECT 1 FROM Cvr_hooks_501_1000 WHERE post_author_id =  new.user_id)) then

INSERT INTO Cvr_1_link_501_1000 (a_hook_id, a_user_id, a_post_id, a_post_author_id)
VALUES (new.hook_id, 
new.user_id, 
new.post_id, 
new.post_author_id);
END IF;
END

INSERT INTO account VALUES(1,1,1,1)

SELECT * FROM Cvr_1_link_501_1000

a_hook_id | a_user_id | a_post_id | a_post_author_id--------: | --------: | --------: | ---------------:1 | 1 | 1 | 1

db<此处小提琴>