Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

More Triggers Expand / Collapse
Author
Message
Posted Friday, November 12, 2010 7:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:33 PM
Points: 327, Visits: 101
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.
Post #1019937
Posted Friday, November 12, 2010 8:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 10:42 AM
Points: 96, 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
Post #1019991
Posted Friday, November 12, 2010 8:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:24 AM
Points: 2,451, Visits: 2,342
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.
Post #1019992
Posted Friday, November 12, 2010 9:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 31, 2014 10:37 PM
Points: 85, Visits: 625
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.
Post #1020052
Posted Friday, November 12, 2010 9:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 3:04 PM
Points: 45, Visits: 97
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.
Post #1020055
Posted Friday, November 12, 2010 10:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 25, 2014 10:27 AM
Points: 71, Visits: 671
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.
Post #1020075
Posted Friday, November 12, 2010 1:23 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
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
Post #1020185
Posted Sunday, November 14, 2010 5:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 5,925, Visits: 8,173
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
Post #1020496
Posted Monday, November 15, 2010 2:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 7, 2014 3:40 AM
Points: 63, Visits: 86
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.
Post #1020614
Posted Thursday, November 18, 2010 1:45 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:45 AM
Points: 2,693, Visits: 897
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.
Post #1023150
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse