4

I have the following trigger (generated using PHP via mysqli)...

DROP TRIGGER IF EXISTS post_queue_insert;
CREATE TRIGGER post_queue_insert AFTER INSERT ON posts
FOR EACH ROW BEGIN
    IF (NEW.post_type != 'revision' AND NEW.post_status != 'auto-draft') THEN
        INSERT INTO event_queue (action_id, action_do, action_key, action_value, action_event, action_timestamp, sync_complete, SITE_ID) VALUES (NEW.ID, 'post', NEW.post_type, NEW.post_status, 'insert', UNIX_TIMESTAMP(now()), 0, 1);
    END IF;
END;

What I can't figure out is how to check if the 'post_type' value exists in another table and if it doesn't then insert it.

?

3
DROP TRIGGER IF EXISTS post_queue_insert;
CREATE TRIGGER post_queue_insert AFTER INSERT ON posts
FOR EACH ROW BEGIN
    DECLARE found_it INT;
    IF (NEW.post_type != 'revision' AND NEW.post_status != 'auto-draft') THEN
        SELECT COUNT(1) INTO found_it FROM some_other_table
        WHERE post_type = NEW.post_type;
        IF found_it = 0 THEN
            INSERT INTO event_queue (action_id, action_do, action_key, action_value, action_event, action_timestamp, sync_complete, SITE_ID) VALUES (NEW.ID, 'post', NEW.post_type, NEW.post_status, 'insert', UNIX_TIMESTAMP(now()), 0, 1);
        END IF;
    END IF;
END;

If found_it = 0, then the row does not contain a value matching NEW.post_type.

Give it a Try !!!

2
DROP TRIGGER IF EXISTS post_queue_insert;
CREATE TRIGGER post_queue_insert AFTER INSERT ON posts
FOR EACH ROW BEGIN
    IF  ( NEW.post_type != 'revision' 
      AND NEW.post_status != 'auto-draft'
      AND NOT EXISTS
          ( SELECT * 
            FROM other_table
            WHERE post_type = NEW.post_type
          )
        )
    THEN
        INSERT INTO event_queue (action_id, action_do, action_key, action_value, action_event, action_timestamp, sync_complete, SITE_ID) 
        VALUES (NEW.ID, 'post', NEW.post_type, NEW.post_status, 'insert', UNIX_TIMESTAMP(now()), 0, 1);
    END IF;
END;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.