Became Hot Network Question
3 Edited for better reading and added tags
| link
2 Edited for better reading
source | link

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

onOn insert and delete I add row to the log table,Log table and for update I add two rows.

createCreate table t1 ([name] nvarchar(40) primary key, [value] nvarchar(max))
createCreate 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))
createCreate 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

I have triggers to log changes on a one table to another table.

on insert and delete I add row to the log table, for update I add two rows.

create table t1 ([name] nvarchar(40) primary key, [value] nvarchar(max))
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))
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

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.

Create table t1 ([name] nvarchar(40) primary key, [value] nvarchar(max))
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))
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
1
source | link

What is the fastest way to (session) lock table?

I have triggers to log changes on a one table to another table.

on insert and delete I add row to the log table, 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?