如果触发器中不存在 [英] IF NOT EXISTS in trigger

问题描述

我有两个表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 ;

我没有使用not exists，只需测试匹配计数是否大于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)

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