Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue With Update trigger on storeprocedure Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 1:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 03, 2014 7:57 AM
Points: 21, Visits: 214
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
Post #1421837
Posted Tuesday, February 19, 2013 2:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:27 AM
Points: 12,741, Visits: 31,055
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1421845
Posted Tuesday, February 19, 2013 2:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 03, 2014 7:57 AM
Points: 21, Visits: 214
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
Post #1421857
Posted Tuesday, February 19, 2013 3:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:27 AM
Points: 12,741, Visits: 31,055
@@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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1421867
Posted Tuesday, February 19, 2013 3:00 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 1,651, Visits: 5,200
Edit. Removed As hadn't seen whole thread. #stupidtablet

MM


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1421868
    Posted Tuesday, February 19, 2013 3:03 PM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Thursday, April 03, 2014 7:57 AM
    Points: 21, Visits: 214
    Okie ,Got it.

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

    Thank you once again Lowell :)
    Post #1421870
    Posted Tuesday, February 19, 2013 5:59 PM
    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 12:59 PM
    Points: 1,734, Visits: 2,534
    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)
    I'm not fat, I'm gravity challenged.
    Post #1421903
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse