主页 / dba / 问题 / 8416
Accepted
EddyR
Asked: 2011-11-27 01:20:35 +0800 CST

MySQL触发器：比较另一个表中的值

  • 772

我有以下触发器（通过 mysqli 使用 PHP 生成）...

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;

我不知道如何检查“post_type”值是否存在于另一个表中，如果不存在则插入它。

?

mysql trigger

2 个回答

  1. Best Answer
    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;

    如果 found_it = 0，则该行不包含匹配 NEW.post_type 的值。

    试试看 ！！！

    • 4 
  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;
    • 2

相关问题