SQL Server – Triggers creation

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:

use VitoshAcademy
go

if OBJECT_ID('[dbo].[del_users]','TR') is not null
	drop trigger [dbo].[del_users]
go

create trigger [dbo].[del_users] on [dbo].[users]
	instead of delete
as

begin
	declare @Count int;
	set @Count = @@ROWCOUNT
	if @Count = 0 
		return;

	set nocount on;

	begin 
		raiserror
			(N' You cannot delete users like this...',10,1)

			if @@TRANCOUNT > 0
			begin
				rollback transaction
		end
	end
end
go

Yup. Enjoy it! 🙂