SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


More Triggers


More Triggers

Author
Message
Jeff Kunkel-812485
Jeff Kunkel-812485
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 112
Happy Friday All!!!

I prefer the separation of triggers for auditing and business rules (1 or more). However, as I mostly support another vendors databases, I am seeing more developers move the business logic into the application code and out of stored procedures and triggers in the database.
amenjonathan
amenjonathan
Mr or Mrs. 500
Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)

Group: General Forum Members
Points: 542 Visits: 434
I prefer not to use triggers. IMO business logic is better stored elsewhere and in a less global fashion. Triggers are very heavy handed. Auditing triggers on a dev environment are acceptable, but again I would prefer a different auditing solution.

I've worked on a system that was all triggers. There were no procedures or other logic. Each different action an app this database fed would perform would fire off 5 or 6 different triggers in tables. It was a nightmare to map processes, and figure out how to adjust the system without screwing up all the other triggers. I would much rather have all that logic in a procedure, not only because it is all contained in one location but also because it's execution can be controlled much easier and protected from mixing with other processes.

Kind of the same policy I have about cursors. Don't do it!

-------------------------------------------------------------------------------------------------
My SQL Server Blog
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7628 Visits: 3401
Jeff Kunkel-812485 (11/12/2010)
Happy Friday All!!!

However, as I mostly support another vendors databases, I am seeing more developers move the business logic into the application code and out of stored procedures and triggers in the database.

Unfortunately, we are installing a new ERP that all check, logic, auditing etc. are in the app. Triggers, stored procs, constraints, primary key, clustered index are just a dream.

I run on tuttopodismo
Dean Cochrane
Dean Cochrane
SSC Veteran
SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)

Group: General Forum Members
Points: 298 Visits: 641
Triggers, in my experience, are best used sparingly and with careful forethought. Triggers that reference other database objects add complexity to query plans.

I worked on a system (in DB2) that had huge numbers of triggers on improperly designed tables, and which had multiple schemas for stupid business reasons. You'd have a main order table with about 20 triggers on it - changes to this table cascaded through those triggers to probably 10, 12 tables, each of which had multiple triggers that cascaded change to tables that had triggers that cascaded change... to make matters worse, if you have multiple schemas with identical tables, the optimizer can't determine which one will be affected and so the plan has to take all of them into account, each with THEIR triggers.

We actually had developers write stored procedures that couldn't be compiled because there wasn't enough memory on the server to prepare the plan.

Triggers are indispensable in some (relatively rare) cases, and should be avoided at all other times.
rbartram-847800
rbartram-847800
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 107
I used to manage a huge casino management system and was forced to use triggers to audit tables that were updated by stored procedures. Altering vendor code is never a good idea, so placing a trigger on the table was a great work around. My first choice however, would be to modify the stored procedures that input the data and add a audit task instead. Triggers are tricky and can lockup tables if not written carefully for the system applied to it. Table locks are your worst nightmare in a high transaction live entertainment environment. ;-)
Dizzy Desi
Dizzy Desi
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 687
While triggers are not my favorite, I would like to bring up the question of how business rules for the data are enforced when someone makes changes directly in the database, rather than through the application.

Granted, it's always preferable to use the application, but what if you have two applications that need to import/export data between each other?

As a developer, I absolutely cringe when I need to pass data to another system and am told "you have to go through the application, because all of the business rules are there". Robot-like macros can get the job done, but are very tedious, especially when the formatting of the application screens are changed. It would be so much more seamless to be able to pass data between the databases.

I've often wondered about the separation of data layer, business layer and user interface layer. Maybe we need a tool (aside from triggers) that can be used in both the data layer (database) and the user interface layer (application) so that business rules are enforced no matter how the data is coming in.
tfifield
tfifield
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2567 Visits: 2890
I deal with a proprietary POS system where the Delphi code is not touchable and so to get some things done I have to resort to triggers - even though I'm not a huge fan of them. I worked on one system where there were 8 insert/update triggers on the main item table. It took 8 seconds to do a simple update of an item. I stuck code in each of the triggers to dump to a table each time it was called. I found over 27 calls to triggers for 1 single update.

Now this sort of thing can be handled by checking nest level, but there are some valid reasons why you wouldn't want to do this, such as an update trigger in another table like Vendor Item updating the main Item table.

I consolidated all triggers into 1 insert/update trigger and an item update was instantaneous.

The only way I would have multiple triggers on the same table would be to have a separate audit action, which would not affect anything else in the database.

Todd Fifield
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19131 Visits: 12426
I don't like having multiple triggers for the same operation, so I prefer at most one for each action (insert, update, delete). I rarely write triggers for more than one action - there is always some code that needs to know if the action was insert, update, or delete' with one trigger per action, that's not a problem.

I can see some benefit of seperating auditing (especially if you use some code to generate these triggers) from other trigger actions/checks. But definitely not more - I would lose oversight of what happens when I change data in a table, and I have too little control over the order triggers fire.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Bill Strachan
Bill Strachan
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 90
I too favour separate triggers for Audit and business logic/other.

Also, at one firm, we had a third party case management system which had no auditing, an ineffective control mechanism for granular control of data changes and most of the SQL statements were ad-hoc and constructed in the compiled front-end app plus the supplier was slow/resistant to implement changes that we required. If they'd used stored procedures more widely then we'd at least have had a mechanism for implementing the changes ourselves.

Triggers came to the rescue on numerous occasions, and although we accepted that they wouldn't have been our preferred choice, we didn't have any other option.

It did mean that we ended up with a large number of triggers and it was far easier to have separate triggers (sometimes multiple triggers for each DML operation) to encapsulate each bit of logic that we needed to implement.
jcb
jcb
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3388 Visits: 998
I dont like triggers but they are usefull for auditing, for example. I prefer to maintain only one trigger/table where the triggers exists just to call a bunch of SP. Its more easy to put a order in the SPs execution and track recursive triggers that way. Also a SP is easier than a trigger to isolate and debug.
I however prefer to let the busines logic in the busines layer and force any data access to be done exclusively trough stored procedures, also avoiding dynamic sql with the SP.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search