CODEWITHSUNDEEP
mysqltriggers
user1094081
user1094081

Reputation:

MySQL trigger and conditional update

I want to create a MySQL trigger on INSERT: when a new record is inserted in the wp_posts table, I want to update wp_posts.post_author retrieving the data from other two tables, using two subqueries (I suppose this can be done better).

Anyway, the update should be "conditional", that is the row must be updated only if wp_posts.post_title value is "A", "B" or "C". Is this possible?

This is what I tried: 

CREATE TRIGGER updatePostIDonInsert
AFTER INSERT
   ON wp_posts FOR EACH ROW

BEGIN

   -- variable declarations

   -- trigger code
// ? (pseudo: if wp_posts.post_title IN ("A","B","C") then...)

UPDATE wp_posts SET post_author = 
(SELECT ID from wp_users WHERE user_email = 
(SELECT meta_value FROM `wp_postmeta` WHERE post_id = wp_posts.ID AND meta_key = "_from_email"))

END;

Any help is appreciated

[UPDATE] the latest query I tried:

CREATE TRIGGER updatePostIDonInsert
    BEFORE INSERT
       ON wp_posts FOR EACH ROW
    BEGIN

    IF FIND_IN_SET(NEW.post_title,'General contact,Website contact,Master contact') > 0 AND NEW.post_type ='inbound_com' THEN
    SET NEW.post_author = (SELECT ID from wp_users WHERE user_email = (SELECT meta_value FROM wp_postmeta WHERE post_id = NEW.ID AND meta_key = '_from_email'));

    END IF;

    END;

(this works if inserted via PhpMyAdmin trigger section, but without the final END and starting from IF)

Upvotes: 0

Views: 2318

Answers (2)

Learning
Learning

Reputation: 163

Something like this. Instead of triggering the AFTER INSERT take advantage of the BEFORE INSERT TRIGGER

    CREATE TRIGGER updatePostIDonInsert
    BEFORE INSERT
       ON wp_posts FOR EACH ROW
    BEGIN
        // add this condition    
    IF FIND_IN_SET(NEW.post_title,'A,B,C') > 0 THEN

            -- variable declarations

            -- trigger code
            SET NEW.post_author = (SELECT ID from wp_users WHERE user_email =
(SELECT meta_value FROM `wp_postmeta` WHERE post_id = NEW.ID AND meta_key = "_from_email") LIMIT 1);
        END IF;

    END;

You need to make sure that there is only single row that will be returned in your sub query. I'm not familiar with your database structure so I cannot really tell you how you can optimize the sub query for your post_author.

The rest is up to you. You can use FIND_IN_SET whenever you need it.

Upvotes: 1

Rahul Verma
Rahul Verma

Reputation: 398

Is this you need?

Here trigger will only do something if newly inserted post_title is in ("A","B","C")

CREATE TRIGGER updatePostIDonInsert
AFTER INSERT
   ON wp_posts FOR EACH ROW
BEGIN
    // add this condition
    IF (NEW.post_title IN ("A","B","C")) THEN

        -- variable declarations

        -- trigger code
        // ? (pseudo: if wp_posts.post_title IN ("A","B","C") then...)

        UPDATE wp_posts SET post_author = 
        (SELECT ID from wp_users WHERE user_email = 
        (SELECT meta_value FROM `wp_postmeta` WHERE post_id = wp_posts.ID AND meta_key = "_from_email"))
    END IF;

END;

Upvotes: 0

Related Questions