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
Hello,
I agree with your list… I find Triggers very annoying in a Database. You have no clear view of whats going on under the hood. But i think the last point of triggers is the only supporting argument, and not one against Triggers.
If you need to ensure that something happens EVERYTIME a field is updated, then you should use a trigger! Thats there only “purpose in live”. So you either need to close down your DB so noone can access the tabels directly, or you need to implement a trigger. Thats the only way you can verify that a certain rule will be obeyed. The performance argument is still valid. You need to make sure that your trigger only fiers when the field is actually updated (Hint: update foo set bar = 1 where bar = 1 is an update on foo.bar… so compare the values).
Hi,
Its excellent comments on triggers.
Thanks for it.
How to handled without triggers
Bcoz sql server 2005 its a big part using triggers
I mean CLR triggers
Pls Explain
Hi,
One of my tables has UPDATE trigger with thousands lines of statements and using “IF UPDATE(column)” to bypass unnecessary running of the Trigger for certain field updates. What I experience is that even though the trigger bypasses the lines when it encounters updates to those specified fields, the response is taking long which I presume is caused by trigger compilation. If I disable the trigger, the response is fast.
Can you tell me is it a good practice for a trigger to contain tousands of codes. Or what do I do in order to make updates on the specified columns to run fast?
Thanks in advance,
Tet
BTW, I am using SQL 2000 and the database is set to SQL 6.5 Compatibility Level.
Thanks,
Tet
hello,
how to trace trigger when i run SQL server profiler? if i want to see some values.
thanks
Hi,
I have created one After Update Trigger in Sql server 2005.
But My Problem is When I am Going to Update more then one column at a time Trigger given me error message while this trigger is working fine for one record updation
Trigger is Follow.
I have create trigger for when I update Test5 table at the same time entry will be done in Test1 table.
Create trigger [trTest1Update] on [Test5]
After Update
as
DECLARE @OldValue VARCHAR(50)
DECLARE @NewValue VARCHAR(50)
IF NOT UPDATE(Old_Value) AND NOT UPDATE(New_Value)
BEGIN
RETURN
END
SELECT @OldValue = (SELECT Old_Value + ‘ ‘ + New_Value FROM deleted)
SELECT @NewValue = (SELECT Old_Value + ‘ ‘ + New_Value FROM inserted)
insert into Test1(Old_Value,New_Value)
values(@OldValue,@NewValue)
Can U Help Out Me.
Its Argent..
THanks in Advance.
Triggers can now be selectively disabled giving the developer more control over how the triggers are used. Triggers can be disabled for a single user, all database users, a single trigger or for a single table. Changing the state of a trigger will take place immediately and in most cases will be persisted until explicitly changed. The exception to this is when disabling triggers for a specific user, in this case the disabled setting will only apply until the user disconnects.
This functionality can be very useful when doing bulk INSERT, UPDATE or DELETE operations where triggers could potentially affect performance. This may also be used for the ADSSYS or other system users to avoid audit type triggers during automated data changes.
can u tell me what is more benfit if we use sql server than other databases…..
Please give more details about triggers for SQL Server 2008
Refer SQL Server help file. It has lot of useful information
Create trigger [trTest1Update] on [Test5]
After Update
as
insert into Test1(Old_Value,New_Value)
select d.Col1, i.Col1
FROM Inserted as i
inner join deleted as d on d.pkCol = i.pkCol
where i.col1 d.col1
Such all think is ok with Trigger but according to me the maser drow back of Trigger ( or all the data base object ) is when we swich over one data base to another data base than it need to re-create. If such all the logic is written in front hand than there is no need to write Trigger logic in other data base.
EX:- Suppose current my project is running with ASP.net and using Oracle as Back end if i some of other client need that project in Sql Server as back end than all the DB object must be re-write in Sql server.
I used triggers in one of my project to maintain the logs.. I can keep track when and who has updated/inserted the values in tables. Secondly what values are updated to what in cases of updated triggers can be easily traced.
Hi people,
I have a problem implementing two levels of a hierarchy on SQL Server 2005. In my data base, I have a table called PRODUCT and its soons (about 14), specifying the kind of product (see diagram).
___________
| PRODUTO |
|__________|
|
( d )
/ \ … \
__/_ _\__ \__
| A | | B | | N |
|___| |___| |__|
I have to ensure the totality of the relationship, i.e. each product may have just one specialty associated. A product can not be A and B in the same time.
To solve this problem I am trying to implement a trigger in SQL, because SQL Server 2005 do not implement ASSERTION statement.
Do you kwon another solution for this problem? I have understood the problems related to trigger using, but I have another better way to solve the problem?
Thanks,
Leandro
Hi
Mr Pinal Dave
I Have problem in SQL Server 2005
I Have 3 tables for Example tblA , tblB And tblUser
tblA have Relation with tblB and he is perent for tblB
and tblUser have relation with 2 table tblA and tblB
and he is parent for tblA and tblB
when I want to create Relation SQL Server 2005 trow
error with this comment :
Introducing FOREIGN KEY constraint ‘FK_tblB_tblUser’ on table ‘tblB’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.
if i Set ‘Cascade Rule’ to ‘NoAction’ and Set ‘Enforce Foriegn Constraint’ to ‘No’ then for this reason i have 2 solve
1- use transaction to update
2- use Trigger to Update
please tel me wich one is better
tblA and tblB grow 1 million record per year
Thank U
plz send your idea to my mail
my Email : Ali.Salamat2@Gmail.com
hi
pls tell me what is use/advantages of using triggers on table
Thanks in advance
@Vidit
There are two kind of Triggers in SQL Server 2005 and Higher…
1. DML – Data Manipulation Language – INSERT, UPDATE, DELETE
2. DDL – Data Definition Language – CREATE, ALTER, DROP…. many more…
In SQL Server 2000 and lower, we had only one type of trigger, DML Triggers.
Look at these links
For DML Triggers :
For DDL Triggers :
Google on key word : DDL Triggers in SQL Server or DML Triggers in SQL Server, you will get plenty of useful links to visit.
Take time to understand the logic, rather than remembering the syntax.
~ IM.
I have create trigger for when I update tbl_personal table at the same time entry will be done in tbl_general table.
CREATE TRIGGER trig_personals
ON tbl_personal
FOR UPDATE
AS
DECLARE @Flag VARCHAR(100)
DECLARE @Reg numeric(9)
IF NOT UPDATE(Flag)
BEGIN
Update tbl_general set Flag=’A’ where Reg=@Reg
END
Can u help Me.
It’s very urgent
Thanks in Advance
You Explained disadvantages of Triggers. Could you explain advantages of triggers?
@Bhadri.
Trigger is very powerful TSQL Command and should be used very carefully.
You cant implement Parent-Child relation with foreign key constraint, if you have your parent table in one database and your child table in other database. In this case, you can use triggers to implement foreign key relation ship.
You cant have a check constraint on a table, if you want to check a value that refers to two other tables. You can implement the same with Triggers. (You can also use a combination of Check Constraint and a function to implement the same)
You can actually start a Job inside a Trigger. If any event occurs, and you want to set a response to that event, you can set it through a trigger.
There are many more advantages of Triggers.
Starting from SQL Server 2005, We also DDL Triggers, there are NO if and No Buts to not use them… they are very very helpful… just take a look or google (DDL Triggers in SQL Server 2005) on web to know more about them.
~ IM.
In the event where a primary needs to be generated on the fly, I can’t see how can you do it without a trigger, unless you reove the primary key and run a stored rpc every time you insert anything, but it leaves us with the same scenario. I would think the greatest disadvantage is the difficulty to track or not “forget” that there is a little detail running behind the scenes…