如果触发器中不存在

Question

我有两个表concept_access 和concept_access_log。我想创建一个触发器，每次从concept_access 中删除某些内容时都会起作用，检查日志表中是否有类似的记录，如果没有，则在从concept_access 中删除它之前插入新记录。

我修改了触发器，现在它看起来像这样：

DROP TRIGGER IF EXISTS before_delete_concept_access;
DELIMITER //
CREATE TRIGGER before_delete_concept_access
    BEFORE DELETE ON `concept_access` FOR EACH ROW
    BEGIN
        IF (SELECT 1 FROM concept_access_log WHERE map=OLD.map 
                          AND accesstype=OLD.accesstype AND startdate=OLD.startdate AND stopdate=OLD.stopdate) IS NULL THEN
            INSERT INTO concept_access_log (map, accesstype, startdate, stopdate)
            VALUES (OLD.map, OLD.accesstype, OLD.startdate, OLD.stopdate);
        END IF;
    END//
DELIMITER ;

删除之前在concept_access 中的示例数据：

map accesstype  startdate   stopdate
1   public      NULL        NULL    
1   loggedin    2011-05-11  NULL    
1   friends     NULL        NULL    

日志表已经有前 2 行。它们与concept_access 中的完全相同。当我从 Concept_access 表中删除第一行时，我在日志表中得到以下内容：

map accesstype  startdate   stopdate
1   public      NULL        NULL    
1   loggedin    2011-05-11  NULL    
1   friends     NULL        NULL    
1   public      NULL        NULL    

虽然不应该插入任何内容，因为 (1,public,null,null) 已经存在于其中。

该表没有主键。我没有创建结构，所以不要问我为什么。更改它会破坏许多现有的功能。我只需要记录从表 concept_access 中删除的内容并将其存储在日志中而不重复。

如果有人能弄清楚出了什么问题，我将非常感激。

Answer 1

DROP TRIGGER IF EXISTS before_delete_concept_access;
DELIMITER //
CREATE TRIGGER before_delete_concept_access
    BEFORE DELETE ON `concept_access` FOR EACH ROW
    BEGIN
        IF (SELECT COUNT(*) FROM concept_access_log WHERE map=OLD.map 
                          AND accesstype=OLD.accesstype AND startdate=OLD.startdate AND stopdate=OLD.stopdate) = 0 THEN
            INSERT INTO concept_access_log (map, accesstype, startdate, stopdate)
            VALUES (OLD.map, OLD.accesstype, OLD.startdate, OLD.stopdate);
        END IF;
    END//
DELIMITER ;

我没有使用不存在，只是测试匹配计数是否大于0

您的代码在我的机器上运行良好，您的MySQL版本是什么？

mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.56-log |
+------------+
1 row in set (0.00 sec)

Answer 2

而不是

... WHERE map = OLD.map ...

--  map   OLD.map  bool
--  ---   -------  ----
--  'a'    'a'     TRUE
--  'a'    'b'     FALSE
--  'a'    NULL    FALSE
--  NULL   'b'     FALSE
--  NULL   NULL    FALSE  <-- Undesired! We want this to be TRUE

尝试 <=>，它是 MySQL 对 SQL 标准的回答 IS (NOT) DISTINCT FROM 谓词：

... WHERE map <=> OLD.map ...
--  TRUE when both have the same non-NULL value or when both are NULL
--  Equivalents:
--    WHERE (map = OLD.map OR (map IS NULL AND OLD.map IS NULL))
--    WHERE (map = OLD.map OR COALESCE(map, OLD.map) IS NULL)