SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[Person] ([ID] [int] NOT NULL,
                             [Name] [varchar](50) NULL,
                             [Age] [int] NULL,
                             CONSTRAINT [PK_Person]
                                 PRIMARY KEY CLUSTERED ([ID] ASC));
CREATE TABLE [dbo].[PersonLog] ([PersonID] [int] NOT NULL,
                                [Name] [varchar](50) NULL,
                                [Age] [int] NULL,
                                [AddDate] [datetime] NULL,
                                [UpdateDate] [datetime] NULL,
                                [DeleteDate] [datetime] NULL);
CREATE TRIGGER [dbo].[Trigger_Insert]
ON [dbo].[Person]
AFTER INSERT
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @age int;
    SELECT @age = Age
    FROM inserted;
    --If the age is less than 130, normal insertion, otherwise data rollback
    IF (@age < 130)
    BEGIN
        INSERT INTO PersonLog (PersonID,
                               Name,
                               Age,
                               AddDate)
        SELECT ID,
               Name,
               Age,
               GETDATE()
        FROM inserted;
    END;
    ELSE
    BEGIN
        PRINT ('the age should be less than 130');
        ROLLBACK TRANSACTION; --data rollback
    END;

END;
--Works
INSERT INTO dbo.Person (ID,
                        Name,
                        Age)
VALUES (1, 'John', 37);
1 rows affected

--Fails
INSERT INTO dbo.Person (ID,
                        Name,
                        Age)
VALUES (2, 'Jane', 137);
Msg 3609 Level 16 State 1 Line 2
The transaction ended in the trigger. The batch has been aborted.

--Might fails, might work
INSERT INTO dbo.Person (ID,
                        Name,
                        Age)
VALUES (3, 'Greg', 131),
       (4, 'Hannah', 29);
Msg 3609 Level 16 State 1 Line 2
The transaction ended in the trigger. The batch has been aborted.

--Might fails, might work
INSERT INTO dbo.Person (ID,
                        Name,
                        Age)
VALUES (5, 'Sam', 18),
       (6, 'Samantha', 142);
2 rows affected

SELECT *
FROM dbo.Person;


ID
Name
Age


1
John
37

5
Sam
18

6
Samantha
142


DROP TABLE dbo.Person;
DROP TABLE dbo.PersonLog;