November 12, 2010 at 10:09 am
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.
November 12, 2010 at 1:23 pm
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
November 14, 2010 at 5:26 pm
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.
November 15, 2010 at 2:30 am
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.
November 18, 2010 at 1:45 pm
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.
November 20, 2010 at 7:39 pm
Triggers were originally created in the physical by RDBMS vendors to enforce business logic a very long while back before added to ANSI SQL. Then software development changed business logic is now in the object layer and then most of the same RDBMS vendors implemented DRI(declarative referential integrity) which is one of the most important algebras of the 20th century (if a references b, then b must exist) which made most triggers obsolete. I have read here some people still use triggers for DRI.
I think triggers can be used for audit in older SQL Server 2005 and down and keeping track of changed data, however in 2008 and up there are very few valid uses of trigger if you are a SQL Server user in the logical.
Kind regards,
Gift Peddie
November 22, 2010 at 2:16 am
I generally try to avoid triggers which modify data. Triggers that modify data can make it very dfficult to perform maintenance and development and they obscure code that ought to be called explicitly. Triggers can always be replaced by regular stored procedures for data modification statements - or alternatively use an INSTEAD OF trigger on a view rather than a base table.
A sensible use of a trigger in my opinion is to enforce integrity rules that cannot be implemented declaratively. I think it's much too optimistic to think that DRI support in SQL has made triggers obsolete. The support that SQL provides for DRI and declarative integrity in general is truly lousy. SQL almost completely fails to provide even the most basic data integrity features that customers should expect from a DBMS. SQL's failure in this respect is a major reason why poor data integrity is such a huge problem in our industry.
As a very simple example try creating a DRI constraint in SQL to enforce the rule that every order must include at least one product. If SQL cannot implement a simple inclusion dependency through DRI then I think we must conclude that SQL is a failure as a data integrity tool. Hence triggers, and hence application developers who implement such constraints elsewhere - with all the consequences and costs that implies.
November 22, 2010 at 6:16 pm
For me, the most common application is a fashion of auditing what has happened in a table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply