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 ««12345»»»

Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008) Expand / Collapse
Author
Message
Posted Sunday, November 9, 2008 1:10 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:05 PM
Points: 628, Visits: 1,602
The main reason developers want the database to be a "dumb" data store is for portability and costs. If you use all the features of SQL Server, you are "locked in" and on the upgrade path for a long time. Smaller clients may not want to pay the cost or have another DBMS other than SQL Server.
Post #599594
Posted Sunday, November 9, 2008 4:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 21, 2010 10:22 PM
Points: 5, Visits: 38
I like your article but there is one place where we've found triggers invaluable: Where you have a third-party system (eg Great Plains) that you must integrate with. In that kind of a situation, business rules may call for certain data changes to be "caught" and further processing to be performed at that point. The native stored procs that come with the product may be encrypted (as are some in GP) or you may simply not want to mess with them for fear of ruining your upgrade path. In this situation, triggers make perfect sense. But I agree you wouldn't normally be relying on them.
Post #599608
Posted Monday, November 10, 2008 1:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 8:00 AM
Points: 12, Visits: 27
They are the only sensible solution for auditing. They cannot easily be bypassed even when modifying data directly with interactive SQL and I would not design a system without auditing using triggers. This type of design has saved us many times.
Post #599676
Posted Monday, November 10, 2008 1:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:18 AM
Points: 559, Visits: 1,159
As well as being a developer I have a number of bought-in packages to DBA and they all use dumb database designs despite only being available with SQL Server database engine!
This causes slower and more convoluted processing and there are some fine examples of bad design but we have to live with it. It's on these that I have used triggers to monitor e.g. where are blank data fields being added to the table or what is deleting records...
For our own in-house software there's no need - the stored procedures can handle auditing as there is no direct updating.
Post #599685
Posted Monday, November 10, 2008 6:47 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:19 AM
Points: 250, Visits: 537
The answer to the question, are triggers useful, is as always it depends. I currently work with an application riddled with triggers (some with RBAR) and in an earlier contract I also used triggers. In that application, a user had various permissions and capabilities managed through part of an application and stored in a SQL table. When a user was added her permissions were added (set to off) and when she was deleted from the system all her permissions had to be deleted. When a new page was added permissions for all known users had to be added - all of this was done through triggers.

Also, when a table is maintained from several sources - online via a web site, through a load process, etc, and a dependent table, let's say the inventory record for a product record needs to be added then triggers are the most fail-safe method.
Post #599827
Posted Monday, November 10, 2008 8:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:42 PM
Points: 165, Visits: 267
Triggers may have uses, but they are verboten in my shop because of the maintenance difficulties you mention. We are a small dev shop with no DBA. Somebody mentioned data-specific uses such as auditing. If we had those requirements we would use a third party tool.

As for the "dumb database" issue, I am certainly an advocate, especially for a dev team such as ours. There are many whiz-bang advanced features of SQL Server, but I do not consider anyone in my company competent to implement or support them. I also agree that systems where the business logic is in the middle tier make them more scalable, but I also think it makes them more maintainable. I have worked on both kinds, and dumb database systems are less atomic and easier to understand.

However, I also believe that dumb databases should have lots of standard and normal constraints. Databases without key and foreign key constraints are not dumb databases, they are stupid databases
Post #599917
Posted Monday, November 10, 2008 10:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 10, 2014 6:56 AM
Points: 1,176, Visits: 878
Legacy? No way. Without triggers some features in our software would not be possible. I like and use triggers a lot. Where necessary, of course.
Otherwise users must not have access to tables and all updates must be performed with stored procedures, or use a midware that does the job, both are IMHO maintenance nightmare.

Dumb databases? I hate that. It's all in advertising and marketing muscle. Some systems use database as storage only. No constraints, no procedures, no views, no triggers. This is silly. And they claim 100% portability to any sql server. You don't an sql server at all for such purpose. Constraints are not fully portable, so they're not used.

Most of people that hate triggers, hate them for at least one of few reasons:
1. they don't know how to properly use, design and maintain database logic
2. they inherited a nightmare of a database from someone who doesn't know how to properly use, design and maintain database logic :)
3. hmmm

The problem with triggers is that they have vendor specific features and syntax, so if you work with multiple servers, you have to keep knowledge about each separately, but the same problem is with stored procedures too.


Post #599991
Posted Monday, November 10, 2008 11:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:42 PM
Points: 165, Visits: 267
Dumb databases? I hate that. It's all in advertising and marketing muscle. Some systems use database as storage only. No constraints, no procedures, no views, no triggers. This is silly. And they claim 100% portability to any sql server. You don't an sql server at all for such purpose. Constraints are not fully portable, so they're not used.


I could not agree with this more. Portability to multiple databases is almost never an advantage for business software. Most customers in most verticles don't care. Most such claims are hype with no real requirements behind them (and they probably don't work very well, either). And no constraints? At all? Once again, this is not dumb database, it is stupid database.
Post #600067
Posted Monday, November 10, 2008 12:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 12, 2008 9:50 AM
Points: 1, Visits: 17
I definitely fall on the "Constraint Yourself" side of the argument. I've been designing databases for 15 years now and am always amazed at the number of people in this business who profess to use Relational Databases but are vehemently opposed to constraints. Constraints are an integral part of what a RDBMS is---and that includes column constraints, table constraints, null constraints, domain constraints, PK constraints, unique constraints, FK constraints AND TRIGGERS. Triggers are part of how an RDBMS enforces data integrity; they are not there to perform business processes.

Support for stored procedures and triggers that implement business process are features that DBMS vendors include in their products to sell their product, but they are not part of the features and requirements that define what an RDBMS is.

Using an RDBMS without including the appropriate constraints is like a town building a firestation but not buying any trucks or hiring any firefighters and then claiming to have a Fire Department. All you've got is a shell with potential that is not being used and cannot possibly be expected to perform the function it was designed for.

We had "dumb databases" in the 60's. That is one of the main reasons that Codd theorized and defined the RDBMS.
Post #600081
Posted Monday, November 10, 2008 12:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:48 AM
Points: 33,089, Visits: 15,198
With 2005 and 2008, they aren't necessarily required for auditing. Actually you could argue that Profiler/trace means they weren't needed previously either. They do many auditing easier in many cases, but they aren't needed.

I rarely use triggers. It's too easy to think that something ALWAYS needs to happen when it mostly needs to happen. I think that you should use triggers when you absolutely need to make sure some data modification needs to force an action INSIDE the transaction. When you can't be sure an application will do this.

Sending emails, alerting, other things aren't necessarily the items for triggers. Business logic not usually unless it's core to the data, not the process.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #600111
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse