Creating triggers in SQL Server with T-SQL is both easy to be done and efficient 🙂 Thus, sometimes DB developers tend to write more triggers than needed and the efficiency sinks a bit. In this article I will simply write a trigger with T-SQL.
The idea of the trigger is to work instead of delete. Thus, when the delete is called over a specific table (in my case users) the trigger would run and a message would show up. Like this:
The trigger creation is pretty self-explanatory from the code (that is the bonus of SQL). However, after the first begin I make a check, whether there are any rows affected and if the answer is no, then the trigger is not ran.
Pretty much that is all. Here comes the code:
if OBJECT_ID('[dbo].[del_users]','TR') is not null
drop trigger [dbo].[del_users]
create trigger [dbo].[del_users] on [dbo].[users]
instead of delete
declare @Count int;
set @Count = @@ROWCOUNT
if @Count = 0
set nocount on;
(N' You cannot delete users like this...',10,1)
if @@TRANCOUNT > 0
Yup. Enjoy it! 🙂