SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Triggers for Detective Work


Using Triggers for Detective Work

Author
Message
Tom Brown
Tom  Brown
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3208 Visits: 1491
Comments posted to this topic are about the item Using Triggers for Detective Work
Vladan
Vladan
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5102 Visits: 761
Hello Tom,
it may have worked in your particular environment, but I don't think it is a good idea to post this trigger in an article... unless it is an article "What to avoid when writing triggers".

Even at first glance, this trigger has a major problem, because it only works if ONE row is updated. What happens if you update 10 or 500 rows at once? Even if your applications always update one row, any triggers should be written so that they can handle any amount of rows in one update. You can never know what will happen in some future version of some of the applications, and of course there can be some "extra" access (like update issued from QA by DBA). Also, you are doing 2 inserts in the trigger, while the same can be accomplished with one.

IMHO, it would be better to write the check this way:

CREATE TABLE [dbo].[Tmp_LogRangeRanges](
-- Copy of the original table, where a column was being truncated, with columns for new and old value
[pKey] [bigint],
[$timeStamp_old] [datetime] NULL,
[fRange_old] [bigint] NULL,
[region_old] [varchar](32) NULL,
[startLoc_old] [int] NULL,
[endLoc_old] [int] NULL,
[project_old] [varchar](300) NULL,
[$timeStamp_new] [datetime] NULL,
[fRange_new] [bigint] NULL,
[region_new] [varchar](32) NULL,
[startLoc_new] [int] NULL,
[endLoc_new] [int] NULL,
[project_new] [varchar](300) NULL,
-- Additional columns for identifying what caused the error
[ChangeDate] datetime Default GetDate(),
[Machine] sysname,
[User] sysname,
[APP] sysname )

CREATE TRIGGER u_LogRangeRanges ON dbo.RangeRanges FOR UPDATE
AS
BEGIN
INSERT INTO Tmp_LogRangeRanges
(pKey, [$timestamp_old], fRange_old, region_old, startLoc_old, endLoc_old, project_old
[$timestamp_new], fRange_new, region_new, startLoc_new, endLoc_new, project_new,
Machine, [User], APP)
SELECT i.pKey, d.[$timestamp], d.fRange, d.region, d.startLoc, d.endLoc, d.project,
i.[$timestamp], i.fRange, i.region, i.startLoc, i.endLoc, i.project,
HOST_NAME(), SUSER_SNAME(), APP_NAME()
FROM DELETED d
JOIN INSERTED i ON i.pKey=d.pKey
WHERE d.Region <> i.Region
END
GO



It is quite probable that others will come up with even better solution, I was just trying to do minimum changes in your code to achieve reliable result. I hope I didn't mistype something, the code is not tested.



Tom Brown
Tom  Brown
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3208 Visits: 1491
Hi Vladen,

You're right its not an ideal way to write a trigger, and it only worked because I knew the way the application code would have updated the table. And I should have written a better introduction, explaining our distributed application - where there were many fat client machines which could have been causing the problem.

My excuse is it was an immediate production issue - which for us (in manufacturing) means we lose £1000's per minute if production stops - we needed something quick. Fortunately the trigger worked and fairly quickly we identified a machine where a recent application code update had failed, so the trigger wasn't in production for long.

I was so proud of finding the problem without having to check all the fat clients individually to see if the update had been applied - I didn't vet my submission for general application.
chaudharyabhijit
chaudharyabhijit
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 252
Hello Vladen,

I was testing your code. Where did you get the Deleted and Inserted tables from?

Thanks!
Tom Brown
Tom  Brown
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3208 Visits: 1491
The DELETED and INSERTED tables are built into SQL Server for use with Triggers.

See this MSDN page
http://msdn.microsoft.com/en-us/library/ms191300.aspx
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