Magic Tables
There are two tables Inserted and deleted in the SQL Server, which are popularly known as the Magic tables. These are not the physical tables but the SQL Server internal tables usually used with the triggers to retrieve the inserted, deleted or updated rows. These tables contain the information about inserted rows, deleted rows and the updated rows. This information can be summarized as follows:
| Action | Inserted | Deleted |
| Insert | Table contains all the inserted rows | Table contains no row |
| Delete | Table contains no rows | Table contains all the deleted rows |
| Update | Table contains rows after update | Table contains all the rows before update |
Difference between Stored Procedure and Trigger
1) We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete and update) is fired on the table on which the trigger is defined.
2) We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which action (insert, delete and update) defined within a trigger can initiate the execution of another trigger defined on the same table or different table.
3) Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.
4) Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.
5) Stored procedures can return values but a trigger cannot return a value.
6) We can use the Print commands inside the stored procedure to debug purpose but we can't use the print command inside a trigger.
7) We can use the transaction statements like begin transaction, commit transaction and rollback inside a stored procedure but we can't use the transaction statements inside a trigger.
8) We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.
DML Triggers
Types of trigger
In SQL Server, there are two types of triggers which are given below:-
- After Triggers
- Instead of Triggers
In this article, we will use three tables named customer, customerTransaction and Custmail whose structure is given below:-
Create table customer (customerid int identity (1, 1) primary key,Custnumber nvarchar(100), custFname nvarchar(100), CustEnamn nvarchar(100), email nvarchar(100), Amount int, regdate datetime)
Create table customerTransaction(Transactionid int identity(1,1)primary key,custid int,Transactionamt int, mode nvarchar, trandate datetime)
Create table Custmail (Custmailid int identity (1, 1) primary key, custid int, Amt int, Mailreason nvarchar(1000))
After Triggers:-
"After Triggers" are executed after the data modification action (Insert, Delete, Update) is executed on the respective tables. A table can have multiple triggers defined on it.
Syntax of the After trigger
Create Trigger trigger_name
On Table name
For Insert/Delete/update
As
Begin
//SQL Statements
End
Example of After Trigger for Insert
Suppose we have a requirement that whenever a new customer is added then automatically its corresponding value must be inserted into the table Custmail so that an email can be send to the customer and an authorized person in the Bank. To solve this problem we can create a After Insert trigger on the table customer whose syntax is given below:-
Create Trigger trig_custadd on Customer
For Insert
As
Begin
Declare @Custnumber as nvarchar(100)
Declare @amount as int
Declare @custid as int
Select @Custnumber=Custnumber, @amount=Amount
From inserted
Select @custid=customerid
From customer
Where Custnumber =@Custnumber
Insert Into Custmail (custid,Amt,Mailreason)
Values (@custid,@amount,'New Customer')
End
This trigger will be fired, whenever a new Customer is added to the bank and the corresponding entry is inserted into the table Custmail. The mail functionality will use the entries from the table custmail to send the mail to the Customer.
Example of After Trigger for Delete
Suppose, there is an another requirement that whenever a customer is deleted from the system, a mail is send to the customer containing the notification about deletion.To sends the mail, we need to insert an entry of the customer in the table custmail, whenever a customer is deleted from the master table customer. To achieve this we will use the after trigger for deletion. In the example given below, we will use the magic table Deleted.
Create trigger trig_custdelete
on customer
for delete
as
begin
Declare @Custnumber as nvarchar(100)
Declare @custid as int
select @Custnumber=Custnumber from deleted
select @custid=customerid from customer where Custnumber =@Custnumber
delete from customerTransaction where custid=@custid
insert into Custmail
values(@custid,0,'Customer delete')
end
Example of After Trigger for Update
Suppose, we have also a requirement that whenever a client credit his account or updated his name (first name as well as last name), a mail should be send to the customer containing this information. In this case, we can use the After trigger for update. In this example,we are going to use the Magic table Inserted.
create trigger trig_Custupdate
on customer
for update
as
begin
declare @Custnumber as nvarchar(100)
declare @amount as int
Declare @custid as int
if update(amount)
begin
select @Custnumber=Custnumber, @amount=Amount from inserted
select @custid=customerid from customer where Custnumber =@Custnumber
insert into Custmail
values(@custid,@amount,'Customer Amount Update')
end
if update(custFname)or update(CustEnamn)
begin
insert into Custmail
values(@custid,0,'Customer Name Update')
end
endIn the above example, we used the Update function on the columns amount, custfname and custEname which initiates the update trigger on modification of these columns.
Instead of Triggers
Instead of trigger is used when we want to perform another action instead of the action which causes the trigger to fire. Instead of trigger can be defined in case of Insert, Delete and Update. For example, suppose we have a condition that in a single transaction a user could not be able to debit more than $15000. We can use the Instead of trigger, to implement this constraint. If the user try to debit more than $15000 from his account at a time then error is raised with the message "Cannot Withdraw more than 15000 at a time". In this example we use the magic table Inserted.
Create trigger trigg_insteadofdelete
on customerTransaction
instead of insert
as
begin
declare @Custnumber as nvarchar(100)
declare @amount as int
Declare @custid as int
Declare @mode as nvarchar(10)
select @custid=custid , @amount=Transactionamt,@mode=mode from
inserted
if @mode='c'
begin
update customer set amount=amount+@amount where
customerid=@custid
insert into Custmail
values(@custid,@amount,'Customer Amount Update')
end
if @mode='d'
begin
if @amount<=15000
begin
update customer set amount=amount-@amount where
customerid=@custid
insert into Custmail
values(@custid,@amount,'Customer Amount Update')
end
else
begin
Raiserror ('Cannot Withdraw more than 15000 at a time',16,1)
rollback;
end
end
end
DDL Triggers
DDL Triggers has the similar behavior as the DML triggers to have except that they are fired in response to a DDL type event like Alter command, Drop command and Create commands. In other words, it will fire in response to the events which try to change the schema of the database. Therefore, these triggers are not created for a particular table, but they are applicable to all the tables on the database. Also DDL triggers can be fired only after the commands which make them fire is executed. They can be used for the following purposes:
1) To prevent any changes to the database Schema
2) If we want to store the records of all the events, which change the database schema.
For example, suppose we want to create a table command_log which will store all the user commands for creating tables (Create table) and commands which alter the tables. Also we don't want any table to be dropped. Therefore if any drop table command is fired, a DDL trigger will rollback the command with a message that "You can't drop a table".
The script for the table command_log will be given below:
CREATE TABLE Command_log(id INT identity(1,1), Commandtext NVARCHAR(1000), Commandpurpose nvarchar(50))
For storing the create table command in the table command_log , we first need to create a trigger which will be fired in response to the execution of the Create table command.
CREATE TRIGGER DDL_Createtable
ON database
FOR CREATE_Table
AS
Begin
PRINT 'Table has been successfully created.'
insert into command_log ()
Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText ) [1] ','nvarchar(1000)')
End
This trigger will be fired whenever any command for the table creation is fired and will insert the command into the table command_log and also print the message that "Table has been successfully created".
Note: Eventdata() is a functions which returns information about the server or database events.It returns value of XML type. Read more about Eventdata()
DDL Trigger for Alter_Table
Suppose if we want to store the alter table commands also in the table command_log, we need to make a trigger for Alter_table command.
Create Trigger DDL_Altertable
On Database
for Alter_table
as
begin
declare @coomand as nvarchar(max)
print 'Table has been altered successfully'
insert into command_log(commandtext)
Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)')
end
This trigger will be fired whenever any alter table command is fired on the database and will print the message "Table has been altered successfully."
DDL Trigger for Drop_Table
To stop the user from dropping any table in the database, we need to create a trigger for drop table command.
Create TRIGGER DDL_DropTable
ON database
FOR Drop_table
AS
Begin
PRINT 'Table cannot be dropped.'
INSERT into command_log(commandtext)
Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)')
Rollback;
end
This trigger will not allow any table to be dropped and also print the message the "Table cannot be dropped."
Nested Triggers
Nested Trigger: - In Sql Server, triggers are said to be nested when the action of one trigger initiates another trigger that may be on the same table or on the different table.
For example, suppose there is a trigger t1 defined on the table tbl1 and there is another trigger t2 defined on the table tbl2, if the action of the trigger t1 initiates the trigger t2 then both the triggers are said to be nested. In SQL Server, triggers can be nested up to 32 levels. If the action of nested triggers results in an infinite loop, then after the 32 level, the trigger terminates.
Since the triggers are executed within a transaction, therefore failure at any level of within nested triggers can cancel the entire transaction, and it result in total rollback.
We can also stop the execution of nested triggers through the following SQL Command:
sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO
Recursive triggers
In SQL Server, we can have the recursive triggers where the action of a trigger can initiate itself again. In SQL Server, we have two types of recursion.
- Direct recursion
- Indirect recursion
In Direct recursion, action of a trigger initiates the trigger itself again which results in trigger calling itself recursively.
In Indirect recursion, action on a trigger initiates another trigger and the execution of that trigger again calls the original trigger, and this happen recursively. Both the triggers can be on the same table or created on the different tables.
Please note: Recursive trigger can only be possible when the recursive trigger option is set.
Recursive trigger option can be set using the following SQL Command:
ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF
How to find the Triggers in a database
1) Finding all the triggers defined on whole the database
Suppose we want to get the list of all the triggers and their respective tables name then we can use the following SQL Statement.
select o1.name, o2.name from sys.objects o1 inner join sys.objects o2 on o1.parent_object_id=o2.object_id and o1.type_desc='sql_trigger'
2) Finding all the triggers defined on a particular table
For example if we want to find out all the triggers created on the table Customer then we can use the following SQL Statement:-
sp_helptrigger Tablename
example:-
sp_helptrigger 'Customer'
3) Finding the definition of a trigger
Suppose if we want to find out the definition of the trigger, we can use the following SQL Statement:-
sp_helptext triggername
For example:-
sp_helptext 'trig_custadd'
Result
How to Disable a trigger
DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]
Disabling a DML trigger on a table
DISABLE TRIGGER 'trig_custadd' ON Customer;Disabling a DDL triggerDISABLE TRIGGER 'DDL_Createtable' ON DATABASE;Disabling all triggers that were defined with the same scopeDISABLE Trigger ALL ON ALL SERVER;How to enable a trigger
Enabling a DML trigger on a tableENABLE Trigger 'trig_custadd' ON Customer;
Enabling a DDL trigger
ENABLE TRIGGER 'DDL_Createtable' ON DATABASE;
Enabling all triggers that were defined with the same scope
ENABLE Trigger ALL ON ALL SERVER;
How to drop a trigger.Dropping a DML trigger
DROP TRIGGER trig_custadd ;
Dropping a DDL trigger
DROP TRIGGER DDL_Createtable ON DATABASE;
Real life example
Some weeks ago one of my friends gets a task which needs to be completed on a very old written code. The task includes that a mail should be sent to the user in the following cases:1. The user is added to the system.2. Whenever any information regarding the user is updated or deleted or added.3. A user is deleted.The challenges in this task :-
1. The code is very old and unstructured. Therefore, it has many inline queries written on the various .aspx pages.2. Queries for the insert, delete and update is also written in many stored procedures.So the code doesn't have a common library function or a standard stored procedure which is used throughout the application which can be used to insert, update and delete a user, which is not a good practice. But it happen sometimes with the old code. The required queries are written on many .aspx pages and stored procedures.Possible solutions:
To complete this task, we need to insert an entry into the table tblmail with proper flags indicating the insert, delete and update. A scheduled application built in .net application will read the rows from the table tblmail and send the mails.
Two approaches to insert the rows:1. Find all the places in the .aspx files and the stored procedures where the queries for the insert, delete and update and after these queries, add the insert query for the table tblmail.2. Instead of finding these queries in all the .axps files and stored procedures, create after (insert, update and delete) trigger on the user master table will insert the date in the table tblmail after the execution of the insert, update and delete statement.We used the second approach because of the following 4 reasons:1) It is very difficult to search so many .aspx files and stored procedures to find the required queries.2) It has the risk that a new developer may not know about this requirement of sending mail and forget to add the code for inserting the values in the table tblmail.3) If we need to change anything in the requirement, it has to be changed in all these files and stored procedures.4) With the second approach, we only need to create triggers on the table and the developer, and it will also minimize the risk mention in the three 3 points mention above.Advantages of SQL Triggers
1) It helps in maintaining the integrity constraints in the database tables, especially when the primary key and foreign key constrain are not defined.
2) It sometimes also helps in keeping the SQL codes short and simple as I show in the real-life example.
3) It helps in maintaining the track of all the changes (update, deletion and insertion) occurs in the tables through inserting the changes values in the audits tables.
4) Sometimes if the code is not well managed, then it can help in maintaining the database constraints defined on the tables on which the trigger is defined. For example, suppose if have a situation that there is an online learning system in which a user can register in the multiple course.
Suppose the organization wants to define a constraint is defined that a user cannot be deleted until he/she passed all the course in which he is registered or the user has to first himself from all the incomplete or failed courses.
Since the code is not well managed and the code to delete the user is defined as the inline query in many .net pages and multiple stored procedures (this is not a good thing, but it happens), one has to write the code for enforcing this constraint in to all these .net files and stored procedures, which take so much time and also if the new developer does not this constraint and forgets to include the constrain enforcing code which corrupt the database. In this case, we can defines an instead of trigger on the table which checks every time a user is deleted and if the condition of the above constraint is not met, display the error message instead of deleting user.
Disadvantages of Triggers1) Hard to maintain since this may be a possibility that the new developer doesn't able to know about the trigger defined in the database and wonder how data is inserted, deleted or updated automatically.2) They are hard to debug since they are difficult to view as compared to stored procedures, views, functions, etc.3) Excessive or over use of triggers can slow down the performance of the application since if we defined the triggers in many tables then they kept automatically executing every time data is inserted, deleted or updated in the tables (based on the trigger's definition) and it makes the processing very slow.4) If complex code is written in the triggers, then it will slow down the performance of the applications.5) The cost of creation of triggers can be more on the tables on which frequency of DML (insert, delete and update) operation like bulk insert is high.Conclusion
Trigger is bad or good depends upon its use and its proper documentation. It can be very useful when it is used to maintain the integrity constraints in the database tables in the absence of primary key and foreign key, or it is very useful for the auditing purpose in tracking all the changes. But, if it is used extensively, it can reduce the performance. Also to maintain it and making debugging simple, proper documentation of the Triggers is necessary, which records the trigger name, table name on which it is created, its definition and its purpose.
