A trigger to prevent deletion upon condition in MySQL

Question

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.

Potential duplicate: [How to write a trigger to abort delete in MYSQL?](https://stackoverflow.com/questions/7595714/how-to-write-a-trigger-to-abort-delete-in-mysql) — Stevoisiak, May 18 '21 at 19:44

score 1 · Accepted Answer · edited Sep 26 '16 at 07:22

1

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.

edited Sep 26 '16 at 07:22

Arcane

17
2
11

answered Sep 25 '16 at 13:00

Gordon Linoff

1,242,037
58
646
786

Thanks for your answer. Those semicolons after 'Return' and 'End if' shouldn't be there right? I removed them and tried your code but it still gives me an error on line 11.. – Arcane Sep 25 '16 at 13:06
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('Deletion is Not Allowed', 16, 1) Return End if END' at line 11 – Arcane Sep 25 '16 at 13:11
@Arcane . . . That would make sense, because `RAISEERROR` is what SQL Server uses. MySQL uses `SIGNAL`. – Gordon Linoff Sep 25 '16 at 15:04
@Arcane . . . As for the semicolons, I usually end lines with them. Perhaps you need a `delimiter` statement before the trigger definition. – Gordon Linoff Sep 25 '16 at 15:06
Gordon...Your code works. :) But that state should change to '45000' instead of '4500' and 'Begin' and 'Return' before and after that line should be removed.. With those changes, your code works perfectly fine. Thanks for your help :) – Arcane Sep 25 '16 at 15:14

A trigger to prevent deletion upon condition in MySQL

1 Answers1