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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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! 🙂