April 12, 2013 at 12:55 pm
I have a situation where CONTEXT_INFO is currently being used in one area of our PROD instance on a database table that has 5 triggers on it. These 'cascade' as they get hit.
I have another table in the same database that is needing to be able to take advantage of the CONTEXT_INFO Function for a single Stored Procedure against a single Trigger on this other table.
I'm being told that it will break the current process on the other triggers' table, even though the values are different.
I thought the whole idea behind this (when used in a 'Session) was to isolate the CONTEXT_INFO value for that particular object(s) (i.e. Stored Procedure, Trigger) and then reset the value back for other objects to use.
Am I even warm on my understanding here? Or am I so far off that I am no where on the map?
Here is the T-SQL for both scenarios
Scenario 1
CREATE TRIGGER [dbo].[trg_UpdateTradeInStatus]
ON [dbo].[AM_TradeIn]
AFTER UPDATE
AS
SET NOCOUNT ON;
--PRINT 'Trigger: trg_UpdateTradeInStatus has fired';
--DECLARE @a int, @b-2 int, @C int;
--SELECT @a = TradeInStatusID, @b-2 = StatusID, @C = InStoreStatusID FROM inserted;
--PRINT 'INSERTED: TradeInStatusID = ' + CONVERT(varchar, @a) + '; StatusID = ' + CONVERT(varchar, @b-2) + '; InStoreStatusID = ' + CONVERT(varchar, @C)
--SELECT @a = TradeInStatusID, @b-2 = StatusID, @C = InStoreStatusID FROM deleted;
--PRINT 'DELETED: TradeInStatusID = ' + CONVERT(varchar, @a) + '; StatusID = ' + CONVERT(varchar, @b-2) + '; InStoreStatusID = ' + CONVERT(varchar, @C)
IF UPDATE(TradeInStatusID)
BEGIN
DECLARE @BIN VARBINARY(128), @prev VARBINARY(128), @OTHER_TRIGGER VARBINARY(128), @EMPTY VARBINARY(128);
SELECT @BIN = CAST( 'trg_UpdateTradeInStatus' AS VARBINARY(128) );
SELECT @OTHER_TRIGGER = CAST( 'trg_UpdateCompositeStatus' AS VARBINARY(128) );
SELECT @EMPTY = CAST('' AS VARBINARY(128))
SET @prev = ISNULL(CONTEXT_INFO(), @EMPTY);
DECLARE @Allow int;
SET @Allow = 0;
SELECT @Allow = CASE ConfigValue WHEN 'True' THEN 1 ELSE 0 END FROM AM_Configuration WHERE ConfigName = 'AllowInvalidStatusTransitions';
IF CONTEXT_INFO() IS NULL
SET CONTEXT_INFO @EMPTY;
-- *** DEBUG ***
--PRINT 'Entering Trigger trg_UpdateTradeInStatus (Level = ' + CAST(TRIGGER_NESTLEVEL() AS varchar) + ')'
--SELECT CONTEXT_INFO();
--PRINT 'Trigger: trg_UpdateTradeInStatus - Other Trigger = ' + CAST( @OTHER_TRIGGER AS VARCHAR(128) );
--PRINT 'Trigger: trg_UpdateTradeInStatus - Previous = ' + CAST( @prev AS VARCHAR(128) );
--PRINT 'Trigger: trg_UpdateTradeInStatus - Context = ' + CAST( CONTEXT_INFO() AS VARCHAR(128) );
/* Update the Composite status (StatusID and InStoreStatusID) based on the new TradeInStatusID */
IF CONTEXT_INFO() IS NULL OR CONTEXT_INFO() <> @OTHER_TRIGGER
BEGIN
SET CONTEXT_INFO @BIN;
-- *** DEBUG ***
--PRINT 'Trigger: trg_UpdateTradeInStatus is executing';
--PRINT 'Trigger trg_UpdateTradeInStatus - Mapping new status back to old composite';
--SELECT @a = TradeInStatusID, @b-2 = StatusID, @C = InStoreStatusID FROM inserted;
--PRINT 'INSERTED: TradeInStatusID = ' + CONVERT(varchar, @a) + '; StatusID = ' + CONVERT(varchar, @b-2) + '; InStoreStatusID = ' + CONVERT(varchar, @C)
--SELECT @a = TradeInStatusID, @b-2 = StatusID, @C = InStoreStatusID FROM deleted;
--PRINT 'DELETED: TradeInStatusID = ' + CONVERT(varchar, @a) + '; StatusID = ' + CONVERT(varchar, @b-2) + '; InStoreStatusID = ' + CONVERT(varchar, @C)
UPDATE ti
SET ti.StatusID = CASE
WHEN i.ItemDescription = 'Video Games' AND i.StoreID = 0 AND s.StatusCode = 'RCV'
THEN 6
ELSE ISNULL(s.MapStatusID, i.StatusID)
END,
ti.InStoreStatusID = CASE
WHEN i.ItemDescription = 'Video Games' AND i.StoreID = 0 AND s.StatusCode = 'RCV'
THEN NULL
ELSE s.MapInStoreStatusID
END,
ti.PaymentRefID = CASE
WHEN i.ItemDescription = 'Video Games' AND i.StoreID = 0 AND s.StatusCode = 'RCV'
THEN 'VERIFIED'
ELSE i.PaymentRefID
END
FROM
AM_TradeIn ti
INNER JOIN inserted i ON ti.TradeInID = i.TradeInID
INNER JOIN deleted d ON ti.TradeInID = d.TradeInID
INNER JOIN TradeInStatus s ON i.TradeInStatusID = s.TradeInStatusID
WHERE
ISNULL(i.TradeInStatusID, -1) <> ISNULL(d.TradeInStatusID, -1)
AND (@Allow =1 OR dbo.TransitionValidity(d.TradeInStatusID, i.TradeInStatusID, i.StoreID) = 1);
END;
/* Reverse invalid TradeInStatusID transitions if Disallowed by the configuration flag */
IF @Allow = 0
BEGIN
-- *** DEBUG ***
--PRINT 'Reversing Update Trigger trg_UpdateTradeInStatus (Level = ' + CAST(TRIGGER_NESTLEVEL() AS varchar) + ')'
UPDATE t
SET t.TradeInStatusID = d.TradeInStatusID
FROM
AM_TradeIn t
INNER JOIN inserted i ON t.TradeInID = i.TradeInID
INNER JOIN deleted d ON i.TradeInID = d.TradeInID
WHERE
ISNULL(i.TradeInStatusID, -1) <> ISNULL(d.TradeInStatusID, -1)
AND dbo.TransitionValidity(d.TradeInStatusID, i.TradeInStatusID, i.StoreID) = 0;
--PRINT CONVERT(varchar, @@ROWCOUNT) + ' status transitions were reversed.'
END;
/* Insert a new log of the change of status or disposition */
--PRINT 'Trigger: trg_UpdateTradeInStatus - Context = ' + CAST( CONTEXT_INFO() AS VARCHAR(128) );
--PRINT 'Trigger: trg_UpdateTradeInStatus - Other Trigger = ' + CAST( @OTHER_TRIGGER AS VARCHAR(128) );
IF CONTEXT_INFO() <> @OTHER_TRIGGER
BEGIN
--PRINT 'Logging Trigger trg_UpdateTradeInStatus (Level = ' + CAST(TRIGGER_NESTLEVEL() AS varchar) + ')'
INSERT INTO StatusDispositionHistory
(TradeInID,
PreviousStatusID,
NewStatusID,
PreviousDispositionID,
NewDispositionID,
DispositionChangeReasonID,
CustomerCareReasonID,
PreviousTradeInStatusID,
NewTradeInStatusID,
TransitionType,
PreviousInStoreStatusID,
NewInStoreStatusID,
PreviousShrinkTypeID,
NewShrinkTypeID,
PreviousShrinkDate,
NewShrinkDate,
CreatedBy,
UpdatedBy)
SELECT
i.TradeInID,
d.StatusID,
ti.StatusID,
d.DispositionID,
i.DispositionID,
i.DispositionChangeReasonID,
i.CustomerCareReasonID,
d.TradeInStatusID,
i.TradeInStatusID,
CASE WHEN dbo.TransitionValidity(d.TradeInStatusID, i.TradeInStatusID, i.StoreID) = 0
THEN CASE @Allow WHEN 0 THEN 'R' ELSE 'I' END
ELSE t.TransitionType
END,
d.InStoreStatusID,
ti.InStoreStatusID,
d.ShrinkTypeID,
i.ShrinkTypeID,
d.ShrinkDate,
i.ShrinkDate,
0,
0
FROM
inserted i
INNER JOIN deleted d ON i.TradeInID = d.TradeInID
INNER JOIN AM_TradeIn ti ON i.TradeInID = ti.TradeInID
LEFT OUTER JOIN TradeInStatusTransition t
ON d.TradeInStatusID = t.PrevTradeInStatusID AND i.TradeInStatusID = t.NextTradeInStatusID
WHERE ISNULL(i.TradeInStatusID, -1) <> ISNULL(d.TradeInStatusID, -1);
END
SET CONTEXT_INFO @prev;
END
--PRINT 'Trigger: trg_UpdateTradeInStatus - Exiting';
Scenario 2
USE [DETDB001]
GO
/****** Object: Trigger [dbo].[upd_LotStatusID] Script Date: 04/12/2013 11:08:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
<DOC>
<SUMMARY>
This trigger will align the TradeInStatusID of the TradeIn records associated to a Lot when the Lot Status changes.
</SUMMARY>
<REMARKS>
This is part of the ACDS/Brightstar integration project. The new TradeInStatus will be communicated to Brightstar through the DSP interface.
</REMARKS>
<HISTORY author="Anonymous" date="1/17/2013" redmine="8578">Added mapping from Lot Status 4, 5, and 6 to TradeInStatus 17 (ONL)</HISTORY>
<HISTORY author="Anonymous">Added Context_Info bypass to allow for bulk updates.</HISTORY>
</DOC>
*/
ALTER TRIGGER [dbo].[upd_LotStatusID] ON [dbo].[AM_Lots]
FOR UPDATE
AS
-- Add Context_Info bypass to facilitate and streamline bulk updateds. (ANON 03/12/2013)
DECLARE @Cinfo VARBINARY(128); -- ContextInfo Value to bypass
SELECT @Cinfo = Context_Info();
IF @Cinfo = 0x55555
RETURN;
Set NOCOUNT ON;
IF UPDATE(StatusID)
BEGIN
UPDATE ti
SET ti.TradeInStatusID = CASE i.StatusID
WHEN 8 THEN
CASE
WHEN ih.StatusID BETWEEN 1 AND 4 THEN 19
WHEN ih.StatusID BETWEEN 5 AND 6 THEN 18
WHEN ih.StatusID = 7 THEN 15
ELSE ti.TradeInStatusID
END
WHEN 7 THEN 15
WHEN 6 THEN 17
WHEN 5 THEN 17 -- Changed from RFS => ONL (RAB 1/17/2013)
WHEN 4 THEN 17 -- Changed from RFS => ONL (RAB 1/17/2013)
ELSE ti.TradeInStatusID
END
FROM
AM_TradeIn ti
INNER JOIN AM_TradeInLots l ON ti.TradeInID = l.TradeInID
INNER JOIN inserted i ON l.LotID = i.LotID
INNER JOIN deleted d ON i.LotID = d.LotID
INNER JOIN AM_Consignor c ON ti.ConsignorID = c.ConsignorID
LEFT OUTER JOIN AM_InvoiceHeader ih ON i.InvoiceID = ih.InvoiceID
WHERE
i.StatusID <> d.StatusID
AND i.StatusID BETWEEN 4 AND 8
AND ti.StoreID > 0
AND c.IsBrightStar = 1;
END;
Any and all expertise and insight here is greatly appreciated.
Thanks,
SQL_ME_RICH
April 23, 2013 at 7:07 am
My understanding on CONTEXT_INFO is that of a "session variable". If you set it somewhere then all other objects in that session can see the value. If a called object messes with the value then it can disrupt the assumptions made about the value in objects called later in the process.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply