February 19, 2013 at 12:56 pm
Well, if it is poor, missing, or incomplete there really isn't much reason to read it, is there. :w00t:
February 19, 2013 at 1:06 pm
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
February 19, 2013 at 1:12 pm
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.
February 19, 2013 at 2:02 pm
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 19, 2013 at 2:19 pm
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.
Cheers
February 19, 2013 at 2:24 pm
I apologize if you are taking the use of the word "you" personally. It was meant in a general third person manner.
February 19, 2013 at 2:25 pm
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.
February 19, 2013 at 2:34 pm
Lynn Pettis (2/19/2013)
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!
February 19, 2013 at 6:05 pm
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.
_____________
Code for TallyGenerator
February 19, 2013 at 6:16 pm
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.
_____________
Code for TallyGenerator
February 20, 2013 at 5:37 am
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.
February 21, 2013 at 11:39 pm
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
February 22, 2013 at 6:54 am
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 ...
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 22, 2013 at 7:01 am
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.
April 5, 2013 at 8:20 am
???????? 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
Viewing 15 posts - 46 through 60 (of 67 total)
You must be logged in to reply to this topic. Login to reply