Issue With Update trigger on storeprocedure

Question

Post reply

Issue With Update trigger on storeprocedure

sdennis

SSC-Addicted

Points: 431
More actions
February 19, 2013 at 1:48 pm

#270639

Hi
I have a trigger tuReferral_UpdateAudit on update of a table [dbo].[tReferral].The trigger is used for audit purposes where it updates 3 columns
below is the trigger
ALTER TRIGGER [dbo].[tuReferral_UpdateAudit]
ON [dbo].[tReferral]
FOR UPDATE
AS
-- Keep Audit "Update" audit columns current (reflect data changes) for table: tReferral
UPDATEtbl
SET
tbl.UpdateLogin= SUSER_SNAME()
,tbl.UpdateApp= SUBSTRING(APP_NAME(),1,64)
,tbl.UpdateDateTime= CURRENT_TIMESTAMP
FROMdbo.tReferral tbl
JOIN (SELECT *,CHECKSUM(*) AS CSUM FROM inserted) i
ON tbl.ReferralID = i.ReferralID
JOIN (SELECT *,CHECKSUM(*) AS CSUM FROM deleted) d
ON i.ReferralID = d.ReferralID
WHEREi.CSUM != d.CSUM
AND SUSER_SNAME() NOT IN ('sa','PKX\prodsqlsvc')
But I do not want this trigger to Update updatedatetime column when I'm updating the table [dbo].[tReferral] in one of my stored procedure "spGenerateBillingBatch" which has update query on treferral table .
One solution I could think of was to disable the trigger and run this stored procedure and then update only the columns required and enable the trigger back.But My manager was not really happy about this method.
Do we have any other solution for this ?
Thanks
Swethach
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 1

here's one idea: modify the trigger to also use CONTEXT_INFO as the update criteria

ALTER TRIGGER [dbo].[tuReferral_UpdateAudit]

ON [dbo].[tReferral]

FOR UPDATE

AS

-- Select the context information

DECLARE @sess VARBINARY(128), @var NVARCHAR(64)

SET @sess = (SELECT context_info FROM master.dbo.sysprocesses

WHERE spid = @@spid)

SET @var = ISNULL(CAST(@sess AS NVARCHAR(64)),'')

-- Keep Audit "Update" audit columns current (reflect data changes) for table: tReferral

UPDATEtbl

SET

tbl.UpdateLogin= SUSER_SNAME()

,tbl.UpdateApp= SUBSTRING(APP_NAME(),1,64)

,tbl.UpdateDateTime= CURRENT_TIMESTAMP

FROMdbo.tReferral tbl

JOIN (SELECT *,CHECKSUM(*) AS CSUM FROM inserted) i

ON tbl.ReferralID = i.ReferralID

JOIN (SELECT *,CHECKSUM(*) AS CSUM FROM deleted) d

ON i.ReferralID = d.ReferralID

WHEREi.CSUM != d.CSUM

AND SUSER_SNAME() NOT IN ('sa','PKX\prodsqlsvc')

and @var <> 'SKIPME'

then in your stored procedure, you do this:

Create Procedure MyProc

AS

BEGIN

DECLARE @var VARBINARY(128)

SET @var = CAST(N'SKIPME' AS VARBINARY(128))

SET CONTEXT_INFO @var

--do stuff

--disable the work around

SET @var = CAST(N'' AS VARBINARY(128))

SET CONTEXT_INFO @var

END --PROC

the advantage of that is it is a per session variable,and not global like a row in a table would be.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sdennis SSC-Addicted Points: 431 More actions · Answer 2

Thank you so much

But I have a question(It might be dumb,I'm not sure. I'm a beginner)

but the @@spid here returns the sessionid of the storeprocedure???(I was thinking since the current sessionID would be the execution of trigger)

ALTER TRIGGER [dbo].[tuReferral_UpdateAudit]

ON [dbo].[tReferral]

FOR UPDATE

AS

-- Select the context information

DECLARE @sess VARBINARY(128), @var NVARCHAR(64)

SET @sess = (SELECT context_info FROM master.dbo.sysprocesses

WHERE spid = @@spid)

SET @var = ISNULL(CAST(@sess AS NVARCHAR(64)),'')

-- Keep Audit "Update" audit columns current (reflect data changes) for table: tReferral

UPDATEtbl

SET

tbl.UpdateLogin= SUSER_SNAME()

,tbl.UpdateApp= SUBSTRING(APP_NAME(),1,64)

,tbl.UpdateDateTime= CURRENT_TIMESTAMP

FROMdbo.tReferral tbl

JOIN (SELECT *,CHECKSUM(*) AS CSUM FROM inserted) i

ON tbl.ReferralID = i.ReferralID

JOIN (SELECT *,CHECKSUM(*) AS CSUM FROM deleted) d

ON i.ReferralID = d.ReferralID

WHEREi.CSUM != d.CSUM

AND SUSER_SNAME() NOT IN ('sa','PKX\prodsqlsvc')

and @var <> 'SKIPME'

ThankYou

Swetha

Lowell SSC Guru Points: 323518 More actions · Answer 3

@@spid is the unique Id SQL server assigned to the connection, which in this case, would be executing your stored procedure.

if you run exec sp_who2 on your server, you'll see that most connections above 50 are various user connections; within that connection they may run multiple queries, but when someone disconnects, the spid context_info related to the spid would be destroyed, and the spid could then be assigned/reused for a new connection.

@@procid would be the id of the calling stored procedure, which is another option; you could do WHERE OBJECT_NAME(@@PROCID) = 'MyProc' i think, but that needs to be tested and verified...@@procid might return the id of the trigger instead!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Edit. Removed As hadn't seen whole thread. #stupidtablet

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sdennis SSC-Addicted Points: 431 More actions · Answer 5

Okie ,Got it.

Now I have better understanding of @@spid and Context_Info better than before

Thank you once again Lowell 🙂

ScottPletcher SSC Guru Points: 101100 More actions · Answer 6

I support the idea of using CONTEXT_INFO for this.

But I'd suggest limiting the use of it to a single, predefined byte location. And also not destroying any unused in CONTEXT_INFO when you set it: you never know, you might be wiping out something used by another process or trigger.

Since it's a pain to "implant"/stuff binary values to certain byte(s), I use a function to generate the new CONTEXT_INFO value with the byte(s) provided "stuffed" into the byte location specified:

USE Utility_Db

GO

CREATE FUNCTION [dbo].[SetContextInfo] (

@start int,

@value varbinary(128)

)

RETURNS varbinary(128)

AS

--SELECT dbo.SetContextInfo ( 10, 0xA1B2C3D4 ) --sample call that sets 4 bytes, starting at

--byte 10, other bytes are left unchanged; can set anywhere from 1 to 128 bytes

BEGIN

RETURN (

SELECT

COALESCE(SUBSTRING(CONTEXT_INFO(), 1, @start - 1), CAST(REPLICATE(0x00, @start - 1) AS varbinary(128))) +

@value +

COALESCE(SUBSTRING(CONTEXT_INFO(), @start + DATALENGTH(@value), 128 - @start - DATALENGTH(@value) + 1), 0x)

)

END --FUNCTION

GO

--Then in the set up code:

USE data_db

DECLARE @context_info varbinary(128)

SET @context_info = Utility_Db.dbo.SetContextInfo ( 10, 0xEE ) --starting byte, new value

SET CONTEXT_INFO @context_info

GO

--Finally, in the query that checks CONTEXT_INFO,

-- you can use a variable, as shown in the other code posted above;

-- or, if you prefer, you can just test CONTEXT_INFO directly:

...

and ISNULL(SUBSTRING(CONTEXT_INFO(), 10, 1), 0x00) <> 0xEE

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".