IF NOT EXISTS 在触发器中

问题描述

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

I have tow tables concept_access and concept_access_log. I want to create a trigger that works every time something is deleted from concept_access, check if there is similar record in log table and if not, inserts new one before it is deleted from concept_access.

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

I modified trigger and now it looks like this:

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中的样本数据:

Sample data in concept_access before delete:

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

日志表已经有前 2 行.它们与concept_access中的完全相同.当我从concept_access表中删除第一行时，我在日志表中得到了这个:

Log table already has first 2 rows. And they are exactly the same as in concept_access. When I delete first row from concept_access table, I get this in log table:

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) 已经存在于那里.

While it is not supposed to insert anything because (1,public,null,null) already exists there.

这个表没有主键.我不是在创建结构，所以不要问我为什么.改变它会破坏很多已经存在的功能.我只需要记录从 concept_access 表中删除的内容，并将其存储在日志中，不要重复.

This table has no primary key. I was not creating structure, so don't ask me why. Changing it will ruin a lot of already existing functionality. I just need to keep log of what was removed from table concept_access and store it in log without duplicates.

如果有人能找出问题所在，我将不胜感激.

I would really appreciate, if anyone can figure out what is going wrong.

推荐答案

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

I am not using not exists, just test if the match count greater than 0

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

your code runs well on my machine, what's your MySQL version?

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

这篇关于IF NOT EXISTS 在触发器中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！