CREATE TRIGGER TD_Customer ON customer FOR DELETE AS IF EXISTS ( SELECT * FROM orders INNER JOIN deleted ON orders.custid = deleted.custid) BEGIN RAISERROR('Customer has orders and cannot be removed', 11, 1) ROLLBACK ENDThe RAISERROR command will return the message back to the client with an error number of 50000. It's also possible to create your own error messages using the SEM or the sp_addmessage system stored procedure.