当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - A trigger to prevent deletion upon condition in MySQL

问题描述:

Have a table with this schema

Performers (PerformerID, PerformerName, Street, City, State, Zip, ActivityID)

I need to define a trigger to prevent deletion if the ActivityID of the deletion is unique for the table. In other words, if the someone try to delete a Performer when he/she is the only one with a certain ActivityID for the entire table, trigger should fire and avoid the deletion. Otherwise trigger shouldn't interrupt.

I tried following code but it gives me a syntax error.

CREATE TRIGGER deletePerformer BEFORE DELETE ON Performers

FOR EACH ROW

BEGIN

If (Performers.ActivityID FROM INSERTED != Performers.ActivityID FROM Peformers)

Begin

RAISERROR ('Deletion is Not Allowed!', 16, 1)

Return

End

END;

Any Help is much Appreciated.

网友答案:

The right syntax would look something like this:

DELIMITER $$

CREATE TRIGGER deletePerformer BEFORE DELETE ON Performers 
FOR EACH ROW
BEGIN 
    If (NOT EXISTS (SELECT 1
                    FROM Performers p2
                    WHERE p2.ActivityID = old.ActivityId AND
                          p2.PerformerID <> old.PerformerId
                   )
        ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Deletion is Not Allowed!';
    End if;
END

$$

DELIMITER ;

I am not, however, sure that this is the best approach for enforcing the rules you want to enforce.

分享给朋友:
您可能感兴趣的文章:
随机阅读: