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


Issue With Update trigger on storeprocedure


Issue With Update trigger on storeprocedure

Author
Message
sdennis
sdennis
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 264
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
UPDATE tbl
SET
tbl.UpdateLogin = SUSER_SNAME()
,tbl.UpdateApp = SUBSTRING(APP_NAME(),1,64)
,tbl.UpdateDateTime = CURRENT_TIMESTAMP
FROM dbo.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
WHERE i.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
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74485 Visits: 40983
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
UPDATE tbl
SET
tbl.UpdateLogin = SUSER_SNAME()
,tbl.UpdateApp = SUBSTRING(APP_NAME(),1,64)
,tbl.UpdateDateTime = CURRENT_TIMESTAMP
FROM dbo.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
WHERE i.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
sdennis
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 264
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
UPDATE tbl
SET
tbl.UpdateLogin = SUSER_SNAME()
,tbl.UpdateApp = SUBSTRING(APP_NAME(),1,64)
,tbl.UpdateDateTime = CURRENT_TIMESTAMP
FROM dbo.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
WHERE i.CSUM != d.CSUM
AND SUSER_SNAME() NOT IN ('sa','PKX\prodsqlsvc')
and @var <> 'SKIPME'


ThankYou
Swetha
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74485 Visits: 40983
@@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
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10911 Visits: 7891
Edit. Removed As hadn't seen whole thread. #stupidtablet

MM


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




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • sdennis
    sdennis
    SSC Rookie
    SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

    Group: General Forum Members
    Points: 35 Visits: 264
    Okie ,Got it.

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

    Thank you once again Lowell Smile
    ScottPletcher
    ScottPletcher
    SSC-Insane
    SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

    Group: General Forum Members
    Points: 20240 Visits: 7421
    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
    If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
    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