August 21, 2012 at 2:55 pm
I have a trigger that is running on one of our production systems. from the time that it was enabled we noticed some data inconsistencies. in particular a stored procedure that executes an update on the table that the trigger executes on. I was wondering if it is possible that the trigger some how caused the stored procedure to fail?
/****** Object: Trigger [dbo].[trg_tblOptionFills] Script Date: 08/21/2012 14:26:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_tblOptionFills]
ON [dbo].[tblOptionFills] AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @oldPrice money;
DECLARE @newPrice money;
DECLARE @oldq int;
DECLARE @newq int;
SET @oldPrice = (select Premium from deleted)
SET @newprice = (select Premium from inserted)
SET @oldq = (select quantity from deleted)
SET @newq = (select quantity from inserted)
IF @oldPrice = @newPrice AND @oldq = @newq
RETURN
IF UPDATE (Premium) OR UPDATE (Quantity)
BEGIN
INSERT INTO dbo.tblOptionFills_audit([OptionFillID],[TicketID],[ExchangeSymbol],[OldPremium], [NewPremium], [OldQuantity], [NewQuantity], [Updatetime], [UpdateDate], [User_name], [APP_NAME], [Host name])
SELECT
inserted.OptionfillID,
inserted.TicketID,
inserted.ExchangeSymbol,
deleted.Premium,
inserted.Premium,
deleted.Quantity,
inserted.Quantity,
CURRENT_TIMESTAMP,
getdate(),
sUSER_NAME(),
APP_NAME(),
HOST_NAME()
FROM inserted
JOIN deleted
ON inserted.OptionfillID = deleted.OptionfillID --need a primary key for the join
END
GO
August 21, 2012 at 3:23 pm
jfriedl (8/21/2012)
I have a trigger that is running on one of our production systems. from the time that it was enabled we noticed some data inconsistencies. in particular a stored procedure that executes an update on the table that the trigger executes on. I was wondering if it is possible that the trigger some how caused the stored procedure to fail?
/****** Object: Trigger [dbo].[trg_tblOptionFills] Script Date: 08/21/2012 14:26:28 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_tblOptionFills]
ON [dbo].[tblOptionFills] AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @oldPrice money;
DECLARE @newPrice money;
DECLARE @oldq int;
DECLARE @newq int;
SET @oldPrice = (select Premium from deleted)
SET @newprice = (select Premium from inserted)
SET @oldq = (select quantity from deleted)
SET @newq = (select quantity from inserted)
IF @oldPrice = @newPrice AND @oldq = @newq
RETURN
IF UPDATE (Premium) OR UPDATE (Quantity)
BEGIN
INSERT INTO dbo.tblOptionFills_audit([OptionFillID],[TicketID],[ExchangeSymbol],[OldPremium], [NewPremium], [OldQuantity], [NewQuantity], [Updatetime], [UpdateDate], [User_name], [APP_NAME], [Host name])
SELECT
inserted.OptionfillID,
inserted.TicketID,
inserted.ExchangeSymbol,
deleted.Premium,
inserted.Premium,
deleted.Quantity,
inserted.Quantity,
CURRENT_TIMESTAMP,
getdate(),
sUSER_NAME(),
APP_NAME(),
HOST_NAME()
FROM inserted
JOIN deleted
ON inserted.OptionfillID = deleted.OptionfillID --need a primary key for the join
END
GO
Thanks for the trigger code. That helps understand what the trigger in question looks like. Unfortunately you didn't describe the problem. You say you have data inconsistencies...what does that mean? do you have missing data? extra data? misbehaving data? Your data has morphed into a bacon and egg breakfast? yumm bacon....but I digress.
It seems you are pretty new around here so I would suggest reading the first link in my signature about best practices when posting questions. Keep in mind we can't see your screen, we don't know what your process is doing, what your stored proc does or what the actual issue it.
I can answer your only question...
I was wondering if it is possible that the trigger some how caused the stored procedure to fail?
No the trigger cannot cause a proc to fail. It can however raise an error that if not handled by the proc can cause the error to bubble up. So if your proc does not handle an error, yes it can fail. But if it is coded to handle errors (try-catch) then it will not.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2012 at 3:23 pm
the first part of your trigger is not a valid test; it's grabbing an aribtrary values, instead of testing ifpremium or quantity changed;
so on a multi row update, the test could be invalid, and might bail out even though tehre were changes that you would have wanted logged.
I'd change it to this:
/****** Object: Trigger [dbo].[trg_tblOptionFills] Script Date: 08/21/2012 14:26:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_tblOptionFills]
ON [dbo].[tblOptionFills] AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT 1
FROM inserted
INNER JOIN deleted
ON inserted.OptionfillID = deleted.OptionfillID
WHERE inserted.Premium <> deleted.Premium
OR inserted.quantity <> deleted.quantity)
RETURN
--
IF UPDATE (Premium) OR UPDATE (Quantity)
BEGIN
INSERT INTO dbo.tblOptionFills_audit([OptionFillID],[TicketID],[ExchangeSymbol],[OldPremium], [NewPremium], [OldQuantity], [NewQuantity], [Updatetime], [UpdateDate], [User_name], [APP_NAME], [Host name])
SELECT
inserted.OptionfillID,
inserted.TicketID,
inserted.ExchangeSymbol,
deleted.Premium,
inserted.Premium,
deleted.Quantity,
inserted.Quantity,
CURRENT_TIMESTAMP,
getdate(),
sUSER_NAME(),
APP_NAME(),
HOST_NAME()
FROM inserted
JOIN deleted
ON inserted.OptionfillID = deleted.OptionfillID --need a primary key for the join
END
GO
Lowell
August 21, 2012 at 3:27 pm
also, my test as it sits assumes both Premium and Quantity can never be passed NULL; if it's possible, you might want to change the comparison to ISNULL(inserted.Premium,0) <> ISNULL(etc etc
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply