Omid

see the link
http://blog.sqlauthority.com/2007/05/24/sql-server-disadvantages-problems-of-tri
ggers/

vinu

There are two basic things you can use triggers for:
1) Enforce business rules, and rollback in case of errors.
2) Perform cascading updates. (Which includes auditing, signaling to other
  systems.)

The major problems with triggers is that if someone accidently drops a
trigger, there will not be any error, but the system will job along happily.
On the other hand, if someone deletes a stored procedure, error messages
will appear all over the place.

If the dropped trigger only perform checking actions the damage is limited,
as long as the application performs its job. But if the trigger was supposed
to perform updates, we now have a database that is corrupt on application
level.

So seen from this point of view, it's better to put the cascading updates
in the stored procedures. However, this assumes that the application
always calls the stored procedure. And more importantly, it assumes that
no one goes and updates the table directly through an SQL window. Which,
after all, is more likely than the trigger being dropped. On the other
hand a direct SQL update may concern a single row only, so the damage
is limited.

Another issue with triggers is that if you overdo it, you may get
entangled in very complex logic. Triggers tend do be a bit more
difficult to program than stored procedures.

In the end, it's a trade-off. Do it in the trigger or in the stored
procedure? What is the damage if the trigger is dropped? How complex
are the updates? And so on. But to say "never use triggers" is a bad
idea, in my opinion.

In the system I work with, we have actually taken precautions, so we
know which triggers should be there, and the application does not start
if a trigger is missing.

I'll give you some reasons and none of them have anything to do with
normalization or performance.

http://beyondsql.blogspot.com/2008/07/demoralization-by-trigger.html
http://beyondsql.blogspot.com/2007/10/sql-undefined-trigger.html

www.beyondsql.blogspot.com

On Jul 18, 1:36 am, Omid Golban <OmidGol...@discussions.microsoft.com>
wrote:

not completely reliable. My canned answers:

http://www.devx.com/dbzone/Article/31985
http://www.devx.com/dbzone/Article/34479/0
http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/13/be-aware-of-thes
e-loopholes-in-your-referential-integrity.aspx

The problem with triggers is that they are often times overused. Because it
is fired every time a table is modified, a complex trigger can become a
performance or concurrency bottleneck. Also, developers often use triggers
for operations that would best be implemented using a stored procedure, check
constraints, or a scheduled job. For example, if your application or stored
procedure updates the [DBAmount] column, then don't leave it up to a trigger
to update the associated [CRAmount] column.

That said, there are a few situations where triggers are a natural solution.

Referential integrity constraints can only be applied to tables within the
same database, so triggers can be used to implement cross database
referential integrity. For example, the column [Sales].OrderDetail.SKU column
should only contain a value contained in the column [Product].Products.SKU.

Another use for triggers is insert / update / delete auditing.
For example:
...
insert into History.dbo.Customer
(ModDate, ModType, CustomerID, CustomerName, ...)
select getutcdate(), 'I', CustomerID, CustomerName, ... from INSERTED
...

On Jul 18, 2:36 am, Omid Golban <OmidGol...@discussions.microsoft.com>
wrote:

I went to a conference once and heard the presenter describe triggers
as "voodoo programing: you could do an insert against a table and the
lights would go off in the building next door."  I think there are
lots of technical reasons (see all of the responses above) as to why
triggers are discouraged, but I also believe that many SQL programmers
are uncomfortable using them because their main purpose is to be
invisible.  SQL is a very simple language, and it's a bit
disconcerting to attempt to do something simple with that language
(like an INSERT statement) and have something else altogether happen.

Stu

Others have mentioned a lot of disadvantages. However, no one will deny
that triggers have their place!

I think most of the times they are discouraged when compared to Declared
Referential Integrity (DRI). When possible, DRI is the preferred way to
specify integrity rules. When using DRI, the declare integrity rule will
be guaranteed (try implementing a Foreign Key constraint using a
trigger...), have a good and (typically) bugfree implementation, are
sometimes structurally faster than the trigger equivalent (such as a
Unique constraint). Basically, you would be re-inventing the wheel if a
DRI version of your integrity rule is available.

1) The SQL Server syntax is highly proprietary and will not port.  It
is also weak version compared to the Standards and other products.
2) The optimizer cannot use procedural code.
3) 90% of all triggers can now be replaced by DRI actions.

There will be a few times when a trigger is needed, but they are
usually a symptom of a poor design.  You can kludging a design flaw by
jumping into procedural code.