Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Are Triggers a "legacy" Feature?...

68 posts, Page 5 of 7

«««3 4 5 6 7 »»»

Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008)

Rate Topic

Display Mode

Topic Options

Author

Message

peter-757102

Posted Tuesday, February 19, 2013 11:50 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 288, Visits: 1,910

jfogel (2/19/2013)

... because far too often they are doing things others are not aware of ...

But that is the whole point of a trigger!

To implement some feature that works transparent and automatic, on top of and without complicating, all those other things going on! For this you should love triggers that do not have unrelated side-effects. I do agree however to keep usage of triggers to what is absolutely nececary. If a feature can be done declarative or in a procedure where said transparancy is no requirement, go for that instead!

Unfortunatly since 2005 no trigger is guaranteed transparent due to the introduced output clause (bad implementation). Anyone using triggers to audit, log or debug using triggers for an application they do not know in full, might break the application doing so unknowingly!

The horror for me is to see everything (RI and interaction), wrapped in stored procedures as then the database is used as dumb storage (in an SQL way). With the same inflexibility and torrents of code that come with stuffing everything in a middle tier. Need to do some reporting...well, add yet anoter procedure. Need another filter parameter for some listview,...complicate things further in a procedure or add another specialised one. It is the path that never ends to generate more complexity and never leverages the concept of the relational database.

Post #1421797

jfogel

Posted Tuesday, February 19, 2013 12:29 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:35 PM
Points: 319, Visits: 726

I get that but my point is that sometimes you don't want this and if a person is performing an action without knowing or forgetting there is a trigger they run the risk of triggering (pun intended) other processes they had no intention of kicking off such as 10,000 emails were sent because they inserted that many records. Of course things like that are rare but once is enough. Yes, triggers can be useful but in my experience they are trouble when others don't realize they are there.

Cheers

Post #1421805

Miles Neale

Posted Tuesday, February 19, 2013 12:39 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 5:57 PM
Points: 1,899, Visits: 949

Eric M Russell (2/19/2013) I think there is more to be gained by avoiding them than advocating them.

Complete agreement here. I think I have written only a few in my career. For the most part i have done everything I could to avoid them and have been successful. There are better ways of doing most things.

Not all gray hairs are Dinosaurs!

Post #1421808

Lynn Pettis

Posted Tuesday, February 19, 2013 12:41 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 21,832, Visits: 27,845

If you don't know the trigger is there, it is probably due to poor, missing, or incomplete application/database documentation.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1421809

jfogel

Posted Tuesday, February 19, 2013 12:47 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:35 PM
Points: 319, Visits: 726

You assume people would bother to read such things. We all know everyone and every level reads the docs :)

Cheers

Post #1421811

Lynn Pettis

Posted Tuesday, February 19, 2013 12:56 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 21,832, Visits: 27,845

Well, if it is poor, missing, or incomplete there really isn't much reason to read it, is there. w00t

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1421815

jfogel

Posted Tuesday, February 19, 2013 1:06 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:35 PM
Points: 319, Visits: 726

It could be a text book and people will refuse to consult it. Apparently you've only worked with people who read everything before doing anything. I and I suspect most of us here wish we could claim the same but can't. Why you insist that the cause of these things are always due to bad docs is lost on me. Its the whole leading a horse to water thing. Cant make it drink and if people refuse to read then usually many pay a price.

Cheers

Post #1421820

Lynn Pettis

Posted Tuesday, February 19, 2013 1:12 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 21,832, Visits: 27,845

If documentation exists and you fail to read it, you have only yourself to blame. Just pointing out one of the possible reasons you may not know why a trigger exists.

A properly designed database, with proper documentation will provide that information if one avails themselves to read it.

Oh, someone once told me that yes, you can lead a horse to water and make them drink. Person who told me this happens to own horses.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1421824

Eric M Russell

Posted Tuesday, February 19, 2013 2:02 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 1,184, Visits: 3,414

Lynn Pettis (2/19/2013)

If you don't know the trigger is there, it is probably due to poor, missing, or incomplete application/database documentation.

Fortunately SQL Server is self documenting, at least in terms of cataloging what objects are in the database, their relationsip, usage stats, and etc. I occasionally find myself in situations where I must familiarize myself with an undocumented legacy database. Fortunately, the databases are the end of their lifecycle and my job is to either develope a replacement or at least ETL the data into a new database before it retires.
I have a collection of scripts that query configuration settings, jobs, triggers, referential constraints, object dependencies, most frequently read/written objects, obsolete objects (never read/written), etc. and then prints out a report. This will more reliably tell me what going on than what I can get out of the last guy who touched the database.

"Winter Is Coming"

Post #1421842

jfogel

Posted Tuesday, February 19, 2013 2:19 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:35 PM
Points: 319, Visits: 726

Lynn Pettis (2/19/2013)

You keep using the word 'you' as if it were I that was unaware of the trigger. I assure you this isn't the case. In fact, the person who caused the problem was also the very same person who brought the initial issue to use that prompted the creation of the trigger to begin with. They were very much aware of its existence, how and why it worked, etc. They just plain forgot and the worst part is that this happened not years after the trigger was implemented but something like two days later. So, please, tone down the 'you' and perhaps replace it with 'they'.

As to the horses.. I don't care.

Cheers

Post #1421850

« Prev Topic | Next Topic »

68 posts, Page 5 of 7

«««3 4 5 6 7 »»»

Permissions