January 27, 2023 at 3:28 pm
I have a stored procedure that works across databases. So the "integration" database will take data from a temp table and insert that data into the "production" database.
There are insert triggers on the table in the "production" database I want to bypass. I'm setting context_info = 0x55555 right before the insert statement (in the integration database) but it does not seem to be bypassing the triggers.
My question is does context_info work if my procedure is in one database but the actual inserting occurs in a different database?
Thanks!
January 27, 2023 at 3:36 pm
It should work I think. But 0x55555 is an odd number of chars, so the value would actually get set to 0x055555.
Try setting it to 0x555555, that should work more the way you want it to.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 27, 2023 at 5:00 pm
Seems to work. Used two databases here:
Results are :
1 X
2 NULL
USE compare2
GO
CREATE TABLE TriggerTest (myid INT, mychar CHAR(1))
GO
CREATE TRIGGER tri_triggertest ON dbo.TriggerTest FOR INSERT
AS
BEGIN
IF CONTEXT_INFO() = 0x1256698456
PRINT 'caught'
ELSE
UPDATE dbo.TriggerTest
SET mychar = 'X'
FROM inserted i
WHERE i.myid = dbo.TriggerTest.myid
END
GO
USE compare1
GO
SET CONTEXT_INFO 0x000
GO
INSERT compare2.dbo.TriggerTest (myid, mychar) VALUES (1, NULL)
GO
SET CONTEXT_INFO 0x1256698456;
GO
SELECT CONTEXT_INFO();
GO
IF CONTEXT_INFO() = 0x1256698456
SELECT 'true'
GO
INSERT compare2.dbo.TriggerTest (myid, mychar) VALUES (2, NULL)
GO
SELECT * FROM compare2.dbo.TriggerTest AS tt
January 27, 2023 at 5:03 pm
Thanks for the answers! I appreciate it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply