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


Using the CONTEXT_INFO Function for multiple objects in the same database


Using the CONTEXT_INFO Function for multiple objects in the same database

Author
Message
SQL_ME_RICH
SQL_ME_RICH
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2458 Visits: 1596
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 int, @c int;
--SELECT @a = TradeInStatusID, @b = StatusID, @c = InStoreStatusID FROM inserted;
--PRINT 'INSERTED: TradeInStatusID = ' + CONVERT(varchar, @a) + '; StatusID = ' + CONVERT(varchar, @b) + '; InStoreStatusID = ' + CONVERT(varchar, @c)
--SELECT @a = TradeInStatusID, @b = StatusID, @c = InStoreStatusID FROM deleted;
--PRINT 'DELETED: TradeInStatusID = ' + CONVERT(varchar, @a) + '; StatusID = ' + CONVERT(varchar, @b) + '; 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 = StatusID, @c = InStoreStatusID FROM inserted;
--PRINT 'INSERTED: TradeInStatusID = ' + CONVERT(varchar, @a) + '; StatusID = ' + CONVERT(varchar, @b) + '; InStoreStatusID = ' + CONVERT(varchar, @c)
--SELECT @a = TradeInStatusID, @b = StatusID, @c = InStoreStatusID FROM deleted;
--PRINT 'DELETED: TradeInStatusID = ' + CONVERT(varchar, @a) + '; StatusID = ' + CONVERT(varchar, @b) + '; 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
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40134 Visits: 14413
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
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