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
If (Performers.ActivityID FROM INSERTED != Performers.ActivityID FROM Peformers)
RAISERROR ('Deletion is Not Allowed!', 16, 1)
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.