Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQLServerCentral.com » Editorials » Are Triggers a "legacy" Feature?...
Add to briefcase
68 posts, Page 6 of 7
«««34567»»

Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008) Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Lynn Pettis
Posted Tuesday, February 19, 2013 2:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 21,832, Visits: 27,845
I apologize if you are taking the use of the word "you" personally. It was meant in a general third person manner.


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 #1421851
Lynn Pettis
Posted Tuesday, February 19, 2013 2:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 21,832, Visits: 27,845
jfogel (2/19/2013)
Lynn Pettis (2/19/2013)
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.


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.


As for horses, you did mention them first.


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 #1421852
Miles Neale
Posted Tuesday, February 19, 2013 2:34 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:57 PM
Points: 1,899, Visits: 949
Lynn Pettis (2/19/2013)
[quote]As for horses, you did mention them first.



I guess this indicates that you can lead a horse to water, but you cannot teach him to like triggers. Although some horses did like Trigger.

Not all gray hairs are Dinosaurs!
Post #1421853
Sergiy
Posted Tuesday, February 19, 2013 6:04 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:22 PM
Points: 4,557, Visits: 8,234
jfogel (2/19/2013)
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.


That's exactly the point of having a trigger!
If the logic of the system (or audit spec) require report every addition to the data via email then - sorry - you should obey.
And if a bugger is trying to sneak around and bypass some required steps - triggers are the best tool to prevent such a breach.

But if you need to email only new data added via UI or some other specific interface - then emailing call must be implemented in that interface, not in the trigger on the base table.

It again comes to incorrect usage, not to any kind of problem with triggers.
Post #1421904
Sergiy
Posted Tuesday, February 19, 2013 6:16 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:22 PM
Points: 4,557, Visits: 8,234
[b]
My argument was based on the use of triggers to implement business logic and to do the sort of data integrity checking that should be enforced by keys and constraints. I should have made that a bitmore explicit.

Cheers,

Tony.


That was an inappropriate use of triggers from the very beginning.
It was a "legacy" from the day triggers were introduced.

The closest point where triggers should be coming to constraints is preventing run-time errors when a constraint is violated by some change in data and divert the data set causing such a violation to some "data revision" process.
Post #1421905
FunkyDexter
Posted Wednesday, February 20, 2013 5:37 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, June 06, 2013 4:34 AM
Points: 102, Visits: 415
Speaking as a Dev rather than a DBA I personally think triggers are the work of the devil. Mind you, that opinion's probably utterly unfair and mostly informed by the fact that I've worked on way too many systems where triggers were used to implement business logic. In terms of business logic I want the database to be as dumb as a plank.

I do believe, on the other hand, that the database should be responsible for retaining it's own integrity so if triggers are necessary for that (I'm not 100% convinced that they really are but there's always an exception) then fill your boots.

On auditing, I think it's a grey area and really depends on the nature of the audit. If it's about detecting and tracking change at the database level then a trigger's probably the best way to go. If it's about producing a more "Businessy" audit that's likely to be consumed by users on a regular basis, then I think I'd probably argue for it to be implemented in a separate Business Logic Layer.

All I'd really ask is this: if you're going to implement a trigger, then do so in such a way that I can do anything I could reasonably expect to as a dev (including directly updating data when the marketting department are breaking down in tears becasue they accidentally imported 1000 new customers under the wrong reference), in total ignorance of the trigger's existence, without accidentally bringing the world down around my ears. Preventing me from carrying out an erroneous action is fine. Allowing me to carry it out and then generating a bunch of new records and updating some aggregated values to a comletely garbage value isn't. I have alot of sympathy for the "you should have read the docs" argument but I can only retain so much knowledge and I've often found myself in situations where the time to check the documentation is a luxury that is unavailable to me for reasons that are out of my control. I'm not dumb or lazy... but it's probably a good idea to assume that I am.
Post #1422036
crazyEmu
Posted Thursday, February 21, 2013 11:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:03 PM
Points: 39, Visits: 598
Was confronted by a trigger just yesterday, wonderful use of nested cursors. Just had to post it as an example.

It is supplied by a vendor (Sage \ SalesLogix) so dont blame me.


ALTER TRIGGER [sysdba].[ADDRESS_RECCHANGE] ON [sysdba].[ADDRESS]
FOR INSERT, UPDATE
AS
BEGIN

DECLARE @recid char(12)
DECLARE @conid char(12)
DECLARE @reccount integer

DECLARE tblCursor CURSOR FOR SELECT ADDRESSID FROM INSERTED

OPEN tblCursor
FETCH NEXT from tblCursor INTO @recid
WHILE (@@FETCH_STATUS = 0)
BEGIN

DECLARE tblAddr CURSOR FOR SELECT ENTITYID FROM INSERTED
OPEN tblAddr
FETCH NEXT from tblAddr INTO @conid
WHILE (@@FETCH_STATUS = 0)
BEGIN

-- delete the old row for this record
SELECT @reccount = count(1) FROM sysdba.SLXRECCHANGE WHERE (TABLENAME = 'CONTACT') and (ENTITYID = @conid)
IF (@reccount > 0)
BEGIN
DELETE sysdba.SLXRECCHANGE WHERE (TABLENAME = 'CONTACT') and (ENTITYID = @conid)
END

-- insert new record into SLXRECCHANGE table
INSERT INTO sysdba.SLXRECCHANGE (TABLENAME, ENTITYID, MODIFYDATE, CHANGETYPE) VALUES ('CONTACT', @conid, getutcdate(), 'C')

FETCH NEXT from tblAddr INTO @conid

END --while

CLOSE tblAddr
DEALLOCATE tblAddr

FETCH NEXT from tblCursor INTO @recid

END -- while

CLOSE tblCursor
DEALLOCATE tblCursor

END

Blog: http://crazyemu.wordpress.com/
Twit: @crazySQL
Post #1422889
Eric M Russell
Posted Friday, February 22, 2013 6:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 1,184, Visits: 3,414
crazyEmu (2/21/2013)
Was confronted by a trigger just yesterday, wonderful use of nested cursors. Just had to post it as an example.

It is supplied by a vendor (Sage \ SalesLogix) so dont blame me.

They could have avoided use of cursors by doing a simple MERGE between the INSERTED virtual table and the audit table. The key column on their table is called 'ENTITYID', so you make some pretty accurate assumptions about their T-SQL coding skills ...

"Winter Is Coming"
Post #1423037
Lynn Pettis
Posted Friday, February 22, 2013 7:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 21,832, Visits: 27,845
crazyEmu (2/21/2013)
Was confronted by a trigger just yesterday, wonderful use of nested cursors. Just had to post it as an example.


Would not be surprised if this was a port from running on Oracle.


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 #1423043
quackhandle1975
Posted Friday, April 05, 2013 8:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 9:41 AM
Points: 1,622, Visits: 592
↑↑↑↑↑↑↑↑ Wow, that looks nasty and no surprise it is Sage related (or how not to use SQL Server properly).

I have seen Cursors in triggers bring systems to a complete halt (I know as I had to debug it!). Best avoided IMHO.

qh

SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1439249
« Prev Topic | Next Topic »

Add to briefcase
68 posts, Page 6 of 7
«««34567»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.