mysql - MSQ 无法从它选择的同一表格中删除 - 需要帮助解决我的删除触发器中的问题

Question

如果重复出现，我不想让触发器删除 ID 最低的行。通过重复，我的意思是两行中的号码和用户名相同。

例如

ROW1: ID: 1 , Nr: 1 , UN: MVJ 和

第 2 行:ID:2，编号:1，UN:MVJ

这些是重复出现的，但如果“Nr”或“UN”不同，它们就不会出现。

但正如标题所解释的，MYSQL 无法做到这一点，至少以我编写 SQL 语句的方式是这样。我愿意以完全不同的方式解决问题。

  Drop trigger no_double_reservations;
    DELIMITER !!
    CREATE TRIGGER no_double_reservations 
    AFTER INSERT ON tilmeldte 
    FOR EACH ROW BEGIN
    IF(
        SELECT COUNT(*) 
        FROM tilmeldte 
        WHERE (kursus_nr, username) 
        IN (
            SELECT kursus_nr, username 
            FROM tilmeldte 
            GROUP BY kursus_nr, username 
            HAVING COUNT(*) = 2 
            ) = 2
        )
    THEN
    DELETE FROM tilmeldte 
    Where tilmeldingsid = (
        Select MIN(`tilmeldingsid`) 
        FROM tilmeldte 
        WHERE (kursus_nr, username) 
        IN (
            SELECT MIN(kursus_nr), username 
            FROM tilmeldte 
            GROUP BY kursus_nr, username 
            HAVING COUNT(*) = 2 
            )
                          );
    END IF;
    END!!
    DELIMITER ;

希望能帮到你。

Answer 1

由于您没有给出任何表定义，因此这里是如何在 MySQL 中完成此操作的逻辑。

创建使用您的用户名和 kursus_id 的存储过程检查用户名是否已经存在如果是>删除并插入新的id如果没有>只需插入新记录并发回消息

在代码中它会是这样的。

CREATE PROCEDURE `SP_NEW_RESERVATION` (in iUsername varchar(50), in iKursus_id int)
BEGIN

    DECLARE EXIT HANDLER for sqlexception
    BEGIN
        rollback;
        SELECT 'False' as Result, 'Some error description or diagnose description from mysql' as `Description`;
    END;

    Start Transaction;
        SET @mExists := (SELECT ID from tbl_yourTable where ((kursus_id = iKursus_id) and (username like iUsername)));
        if (length(coalesce(@mExists,'')) <> 0) then
            -- username does not exists so you can continue with insert a new reservation
            insert into tbl_yourTable and continue here

            -- send back message 
            SELECT 'True' as Result, 'New record inserted')  as `Description`;
        ELSE
            -- username exists already and the id would be saved in mExists so delete the id and continue with your insert_method
            Delete from Tbl_yourtable where id = @mExists something ligke that;
            insert into tbl_yourtable .....

            -- you can send back a message like
            SELECT 'True' as Result, concat('Old record(', @mExists, ') Deleted and new record inserted')  as `Description`;
        end if;
    Commit;

END

以上代码不完整。要执行此代码，您需要更正插入和删除命令。然后只需调用该过程即可。

call SP_NEW_RESERVATION('KrishKm', 2);

如果用户名“KrishKm”已经存在，您将收到如下消息:

True, Old record(10) Deleted and new record inserted.

如果没有，您会收到回复消息:

True, New record inserted.

如果出现任何问题，您会收到消息:

False, 'Some error description or diagnose description from mysql'

祝你好运。