Arcane Arcane - 2 months ago 17
MySQL Question

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.

Answer

The right syntax would look something like this:

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
    Begin
        SIGNAL SQLSTATE '4500' SET MESSAGE_TEXT = 'Deletion is Not Allowed!';
        Return;
    End if;
END;

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