Answer the question
In order to leave comments, you need to log in
How not to block neighboring records for updating by adding a trigger?
Create trigger Update_applicationUpdate_MainTab on MainTab
after update
NOT FOR REPLICATION
as
begin
Update [SubTab] with(ROWLOCK) set [LatUpdateTime] = SYSDATETIME()
where [dbo].[SubTab].[ClientTableId] in (select Id from inserted)
end;
CREATE TABLE [dbo].[SubTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Table] [nvarchar](64) NULL,
[LatUpdateTime] [datetime2](7) NULL,
[ClientTableId] [nvarchar](64) NULL,
CONSTRAINT [PK_SubTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[MainTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL
CONSTRAINT [PK_MainTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
insert into [dbo].[MainTable] VALUES('check1')
insert into [dbo].[MainTable] VALUES('check2')
insert into [dbo].[SubTable] VALUES('[dbo].[MainTable]', SYSDATETIME(), SYSDATETIME(), 1)
insert into [dbo].[SubTable] VALUES('[dbo].[MainTable]', SYSDATETIME(), SYSDATETIME(), 2)
Create trigger Update_applicationUpdate_MainTable on MainTable
after update
as
Update [SubTable] with(ROWLOCK) set [LatUpdateTime] = SYSDATETIME()
where [dbo].[SubTable].[ClientTableId] in (select Id from inserted)
and [Table] = '[dbo].[MainTable]';
begin transaction
update [dbo].[MainTable]
set [Name] = 'Тестовый37' where id = 1
update [dbo].[MainTable]
with(ROWLOCK)
set [Name] = 'Тестовый37' where id = 2
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question