mySQL compare two different column values in trigger

user_16554929 0
Viewed 202

I am trying to have a trigger check if two values in a table are the same and if so insert the information into another table. My code is below.

user_id is a INT(10) post_author_id is a 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

Any help is appreciated!

phpmyadmin triggers mysql
1 Answers

You can use EXISTS to check if the user already existst

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<>fiddle here

user_51935360
Related