ROLLBACK transaction in trigger

See more:

T-SQL

I had in mind to evaluate and rollback if not match the table row

create and populate the table

Use Test
go

if OBJECT_ID('ttable','U') is not null
	drop table ttable
go

declare @jj nvarchar(MAX) = N'[
{"text":"10","f_date":"1/11/20"},
{"text":"11","f_date":"1/11/21"},
{"text":"12","f_date":"1/11/22"}]';

select * into ttable from OPENJSON(@jj) with ([text] nvarchar(50),f_date smalldatetime)

and the trigger for each INSERT and UPDATE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE or alter TRIGGER dbo.TTest 
   ON dbo.ttable
   AFTER INSERT, UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	declare @txt nvarchar(50);  

	set @txt = (select [text] from inserted);
if @txt != '333'
	begin
		RAISERROR ('%s Not equal 333', 16, 1, @txt) with nowait;
			rollback TRANSACTION;  
	END
end
GO

populate the table with some records

insert into ttable values('2','1/1/12')
insert into ttable values('3','1/1/13')
insert into ttable values('4','1/1/14')
insert into ttable values('5','1/1/15')
insert into ttable values('6','1/1/16')
insert into ttable values('7','1/1/17')
insert into ttable values('8','1/1/18')
insert into ttable values('9','1/1/19')

I supposed the trigger will evaluate each row according to IF and rollback just one current record because there is just one row in current single transaction

but the trigger evaluates just the first record and blocks the handling the rest of rows

Could you please help

What I have tried:

TSQL, MSDN, C#, ROLLBACK TRANSACTION (Transact-SQL)[^]