One of my team member asked me should I use triggers or stored procedure. Both of them has its usage and needs. I just basically told him few issues with triggers. This is small note about our discussion.
Disadvantages(Problems) of Triggers
- It is easy to view table relationships , constraints, indexes, stored procedure in database but triggers are difficult to view.
- Triggers execute invisible to client-application application. They are not visible or can be traced in debugging code.
- It is hard to follow their logic as it they can be fired before or after the database insert/update happens.
- It is easy to forget about triggers and if there is no documentation it will be difficult to figure out for new developers for their existence.
- Triggers run every time when the database fields are updated and it is overhead on system. It makes system run slower.
I do not use triggers. In my whole career I am able to get my work done using Stored Procedures instead of Triggers. (Implementation and Architecture changes are required if either of is to be used to support business logic).
Reference : Pinal Dave (https://blog.sqlauthority.com)
56 Comments. Leave new
sir plz tel me
what is the difference between after triggers and insted of triggers. and also in which situation we use triggers…plz tel me
Hi Subramanyam,
These two are related to DML triggers.
After triggers are executed after executing the DML events i.e.,the trigger adn the events both are executed.These triggers can be executed only on tables.
Instead of triggers are executed instead of executing the DML events i.e., the trigger is executed without executing the DML event. These triggers can be executed on tables and views.
Hello Subramanyam,
As the name imply the code of AFTER trigger executes after completing the trigger firing statement while code of INSTEAD of tigger executes instead of firing statement.
AFTER triger is used mostly for implementing business logic and auditing while INSTEAD of trigger is used on updatable views.
Regards,
Pinal Dave
any can tell me main disadvantage of sql server.
madhusudan sir,
If MSSQL server has any disadvantges that may cause ant error in compiling microsoft surely couldont produce a new version 2008
are U understand me sir?
Very nice article. I agree whatever u wrote.
use of instead of and after trigger..
after trigger u know already..
but instead of
u can check constraint,suppose foreign key is not their then u can use.
this is old way.
exp:
alter trigger trg1 on emp1 instead of insert
as
declare @empid int;
select @empid=isnumeric(empid) from inserted;
if(@empid=1)
print(‘numeric’);
else
print(‘not numeric’);
Rajneesh Hajela Gwalior (M.P.)
Pinal,
I totally agree with you on this.
To add to your thoughts, triggers are misused most of the times thereby affecting db performance. Also, all app logic should be part of the application process flow to keep things simple and visible like you said, which makes debugging easier as well.
I have trouble explaining this concept so some people.
Thanks for putting it in writing!
your explanation is super.
Dave,
Thanks for the heads up – it sounds like people are using Triggers for things which they probably ought not to… for example cascading foreign key relationships.
I work in an environment where we do lots of bulk imports into clusters of child/parent tables, and if we had triggers going off each time we did anything we would have 1) a very slow environment, and 2) many many erroneous table links.
On the other hand – we also rely extensively on non-clustered indexes in order to make our reporting work in a realistic amount of time.
Is it appropriate to use a trigger to disable all table indexes before committing an UPDATE or INSERT statement, and then to rebuild the indexes AFTER the statement processes?
Thanks
Forrest
It seems that Triggers are being used extensively EXACTLY for foreign key maintenance.
I still think there is wisdom in manually processing table links; especially if you are pruning dupes from the parent table, and don’t want to loose referential integrity with the child tables.
Sir,
How can I maitain Audit Trail without using trigger???
Hello,
How can we replace a trigger by a stored procedure?
A trigger fires on UPDATE / INSERT or DELETE statement, how can we execute the stored procedure after an insert?
Thank you.
You can have it as part of INSERT trigger code
Hello,
How can I we get the values of the records being updated on the Table and not get updated due to Where Condition
CREATE TABLE [dbo].[Test](
[id] [bigint] NULL,
[description] [varchar](50) NULL
)
GO
INSERT INTO [dbo].[Test]([id],[ description])
Values (1,’SQL2005’)
GO
Update [Test] Set [description] = ‘SQL2008’ where [ID] = 3
In above case I need to write a trigger where I can find the Value of Field Description Which is SQL2008,
Instead of Trigger gives the values but only if the Records are updated
Hi Pinal,
Just wanted to know about logging data modification.
I have seen to ways of tracking data change(DML).
1. Using Triggers
2. Keeping columns in same table for Added Date, Added By, Modified Date, Modified By.
Using approach(1), I can write trigger for Insert/Delete/Update on each table to log changes and hence can apply Foreign key relationship and other constraints like Unique key constraints on all the tables as per requirement.
But I didn’t understand how it is possible to apply various constrains using approach(2).
Since I have to make composite unique key and have to consider many more columns.
Is there any design issues in database tables. What is the suggested way for approach(2) to log data.
Which approach is better.
Also I have come to know from some of my collegues that Triggers do no fires on bulk insert queries is it true.
hi
hard to say…
kindly eradicate the application in the below content …
Triggers execute invisible to client-application application. They are not visible or can be traced in debugging code.
Dont mistake me sir
Munirajan.T
Thanks for nice article
if trigger fired more than 1 table?
1. insert data to table1 from program.
2. table1 trigger fired to table2
3. table2 trigger fired to table3
is it possible????
Nice article, your articles always manage to stay current throughout the test of time!
There is one thing I use triggers for and that is when I need exact audit records of every change made to each record in a table – although I hasten to add that what I do within the trigger is get the batch of records which have been changed and call a stored procedure from that record set so as much of my code as possible is easily visible under procedures.
Is there a better way for me to capture audit records or is a DML trigger the right thing to do here? I implemented it in my dev version of a program (as we have a lot of people here who would have access to the DB so I can’t rely on application-side auditing, sadly!) but if I should have taken another approach then I would prefer to implement it before I move to live!!
Thank you once again for yet another great article,
Rose
how to handle more than 8 columns in triggers ….
What is the difficulty you are having with more than 8 columns?
Hi
What are some of the issues that could be encountered when using triggers??
your Last Statement is wrong
“Triggers run every time when the database fields are updated and it is overhead on system. It makes system run slower.”
————————————————————-
i have “emp” table that have id,name columns
i created a trigger that is below-
————————————-
alter trigger trgEmpInsert
on emp after insert
as begin
declare @a int
select @a=id from inserted
insert into emp1(id) values(@a)
end
——————————————-
Here Emp1 is another table that have the same schema as emp
i add a new column in emp table as below-
——————————————————–
alter table emp
add country1 varchar(33)
—————————————-
when i checked the emp1 table after this we does’t get any row f inserted from trigger .
————————————————-
Before Answering any concept please practical it in SQL server .
let me know if i am wrong .
——————————————————————-
all Query which i am used to test the above concept
——————————————————————-
CREATE TABLE emp(
id int (10) ,
name nvarchar(50) ,
)
CREATE TABLE emp1(
id int (10) ,
name nvarchar(50) ,
)
insert into emp(id,name) values(1,’ram’)
go 100
alter trigger abc1
on emp after insert
as begin
declare @a int
select @a=id from inserted
insert into emp1(id) values(@a)
end
alter table emp
add country1 varchar(33)
1. your trigger run when a row is inserted into emp, not when you alter table structure.
2. your trigger doesn’t support bulk insert, to support bulk insert you can use this :
alter trigger abc1
on emp after insert as
begin
insert into emp1(id)
select id from inserted
end