SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.Table1 (ID int IDENTITY,
                         SomeString varchar(10) NOT NULL);
CREATE TABLE dbo.Table2 (ID int IDENTITY,
                         AnotherString varchar(10) NOT NULL);
CREATE TRIGGER SomeTrigger ON dbo.Table2
INSTEAD OF INSERT AS--Is instead of really what you want?
BEGIN
    IF EXISTS (SELECT 1 FROM inserted WHERE AnotherString = 'abc')
        THROW 79862, N'Desribe your error reason here.', 16;
    INSERT INTO dbo.Table2 (AnotherString)
    SELECT AnotherString
    FROM inserted;
END;
SET XACT_ABORT ON;
BEGIN TRANSACTION
    INSERT INTO dbo.Table1 (SomeString)
    VALUES('abc');
    INSERT INTO dbo.Table2 (AnotherString)
    VALUES('abc');
COMMIT
Msg 79862 Level 16 State 16 Line 5
Desribe your error reason here.

SET XACT_ABORT ON;
BEGIN TRANSACTION
    INSERT INTO dbo.Table1 (SomeString)
    VALUES('def');
    INSERT INTO dbo.Table2 (AnotherString)
    VALUES('def');
COMMIT
3 rows affected

SET XACT_ABORT ON;
BEGIN TRANSACTION
    INSERT INTO dbo.Table1 (SomeString)
    VALUES('xyz'),('123');
    INSERT INTO dbo.Table2 (AnotherString)
    VALUES('xyz'),('abc');
COMMIT
Msg 79862 Level 16 State 16 Line 5
Desribe your error reason here.

SELECT *
FROM dbo.Table1;
SELECT *
FROM dbo.Table2;


ID
SomeString


2
def




ID
AnotherString


1
def


DROP TABLE dbo.Table1;
DROP TABLE dbo.Table2;