1

I have some triggers to log changes on a table to Log table.

On insert and delete I add row to the Log table and for update I add two rows.

The log table contain identity column, and I want the 2 update rows to be sequential ( by the the id = identity)

for example: assuming the following table:

Create table t1 ([name] nvarchar(40) primary key, [value] nvarchar(max))

the log table is:

Create table t1_log 
([log_id] identity(1,1),[log_ts] DateTime default GETDATE(),
  [log_action] varchar(20), log_session_id int default @@SPID,
  [name] nvarchar(40), value nvarchar(max))

And I have 3 triggers to update the log:

Create trigger t1_ins on t1 After Insert as
begin
    Insert into t1_log([log_action],[name],[value]) select 'insert', [name], [value] from inserted 
end 
Go
create trigger t1_del on t1 After delete as
begin
    Insert into t1_log([log_action],[name],[value]) select 'delete', [name], [value] from deleted 
end 
Go
create trigger t1_upd on t1 After update as
begin
    Insert into t1_log([log_action],[name],[value]) 
       select [log_action], [name], [value] from (
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update from' as [log_action], [name], [value] from deleted)
          UNION 
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update to' as [log_action], [name], [value] from inserted)
        ) as temp_tbl
     Order By [temp_tbl].ROW_ID, [temp_tbl].[log_action]
end 
Go

In this solution, when I do update from several sessions, there is a chance to several updates in the same time and it break the update sequence. I can see 2 'update from' rows and then two 'update to' rows and I want to prevent it.

The only solution I can think of it to lock the t1_log table in the update trigger using :

Select * from t1_log with (TABLOCKX)

But what if the t1_log have many rows? I guess select * will be slow, and each update will return the selected *.

So I'm using the following:

create trigger t1_upd on t1 After update as
begin
    declare @tt
    Begin transaction

    select @tt=1 from t1_log with (TABLOCKX)

    Insert into t1_log([log_action],[name],[value]) 
       select [log_action], [name], [value] from (
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update from' as [log_action], [name], [value] from deleted)
          UNION 
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update to' as [log_action], [name], [value] from inserted)
        ) as temp_tbl
     Order By [temp_tbl].ROW_ID, [temp_tbl].[log_action]

     Commit trasaction
end 

this works better, but I still wonder if there is a fastest way to lock a table?

  • 1
    Have you tried yet? Insert into t1_log WITH (TABLOCK) ([log_action],[name],[value])... – Denis Rubashkin Jul 22 at 9:50
  • @DenisRubashkin Thanks, I'll try it – SHR Jul 22 at 11:43
  • 2
    And what does adding TABLOCK to your logging query do to your high throughput transaction system? It may get the desired result but your are going to slow down the inserts as everyone waits for the other sessions to finish logging. You need to remove/change having them appear in perfect unbroken sequence as a requirement. Use the session_id to resolve ties. Add a GUID and have the trigger generate a GUID so you can relate two rows together. But don't use TABLOCK on a high throughput table. – Jonathan Fite Jul 22 at 12:38
3

To further expand on my comment, here is some sample code for you to look at. I don't like the idea of introducing intentional locking on what appears to be a core table in your system. It will effectively slow everyone down to a single-threaded access.

The ideal solution would remove the need to have the update from and update to logging actions in a specific sequence. You can do this by adding a guid or some other identifier to the log table and use that to group the update from and update to actions together.

This example assumes that [Name] is a constant value and won't be changing.

/** Build up our table and triggers

    Note that I have consolidated the trigger logic into a single trigger
    and the additional column on T1_Log
    **/
CREATE TABLE dbo.T1
    (
    [Name] NVARCHAR(40) PRIMARY KEY NOT NULL
    , [Value] NVARCHAR(MAX) NOT NULL
    )

CREATE TABLE dbo.T1_Log
    (
    Log_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , Log_ActionGUID UNIQUEIDENTIFIER NOT NULL
    , Log_TS DATETIME DEFAULT GETDATE() NOT NULL
    , Log_Action VARCHAR(20) NOT NULL
    , Log_Session_ID INT DEFAULT @@SPID NOT NULL
    , [Name] NVARCHAR(40) NOT NULL
    , [Value] NVARCHAR(MAX) NOT NULL
    )

GO

CREATE OR ALTER TRIGGER trg_T1_Log ON dbo.T1
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    DECLARE @Log_ActionGUID UNIQUEIDENTIFIER = NEWID()


    ;WITH CTE_Actions AS
        (
        SELECT Log_Action = CASE    WHEN D.[name] IS NULL THEN 'insert'
                                    WHEN I.[name] IS NULL THEN 'delete'
                                    ELSE 'update from'
                                    END
            , Log_Sort = 1
            , [Name] = COALESCE(D.[name], I.[name])
            , [Value] = COALESCE(D.[Value], I.[Value])
        FROM inserted AS I
            FULL OUTER JOIN deleted AS D ON D.[Name] = I.[Name]
        UNION ALL
        SELECT 'update to' AS Log_Action
            , Log_Sort = 2
            , I.[Name]
            , I.[Value]
        FROM inserted AS I
        WHERE EXISTS (SELECT TOP (1) 1 FROM deleted AS D WHERE D.[name] = I.[name])
        )
    INSERT INTO dbo.T1_Log
        (Log_ActionGUID, Log_Action, [Name], [Value])
    SELECT @Log_ActionGUID
        , Log_Action
        , [Name]
        , [Value]
    FROM CTE_Actions AS A
    ORDER BY Log_Sort

END

GO

/** Test Statements **/
INSERT INTO dbo.T1
    ([Name], [Value])
VALUES 
    ('John Smith', 'Smith Value 1')

UPDATE dbo.T1
SET [Value] = 'New Value'
WHERE [Name] = 'John Smith'

DELETE FROM dbo.T1 WHERE [Name] = 'John Smith'

/** Show Log Data **/
SELECT * FROM dbo.T1_Log
ORDER BY Log_TS, Log_ActionGUID

/** Cleanup **/
DROP TABLE IF EXISTS dbo.T1_Log
DROP TABLE IF EXISTS dbo.T1
  • No lock is better, but I have other utility which read the log, and it read the log by the identity order. that is why I prefer the lock. thanks. – SHR Jul 22 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.