Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24180 Visits: 37950
I apologize if you are taking the use of the word "you" personally. It was meant in a general third person manner.

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)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24180 Visits: 37950
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.

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)
Miles Neale
Miles Neale
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2666 Visits: 1694
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!
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5832 Visits: 11403
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.
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5832 Visits: 11403
[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.
FunkyDexter
FunkyDexter
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 1066
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.
crazyEmu
crazyEmu
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 686
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
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4603 Visits: 9538
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 ...


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24180 Visits: 37950
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.

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)
quackhandle1975
quackhandle1975
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2754 Visits: 1227
↑↑↑↑↑↑↑↑ Wow, that looks nasty and no surprise it is Sage related (or how not to use SQL Server properly). w00t

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

qh

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search