By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[MyTable](
[name] [varchar](50) NULL,
[city] [varchar](50) NULL,
[date] [date] NULL,
[value] [int] NULL,
[islocked] [int] NULL
) ON [PRIMARY]
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'John', N'Berlin', CAST(N'2022-01-02' AS Date), 10,1)
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'Marty', N'Istanbul', CAST(N'2022-01-03' AS Date), 20, 1)
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'Aila', N'Washington', CAST(N'2022-01-05' AS Date), 30, 1)
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'Aise', N'Moskova', CAST(N'2022-01-01' AS Date), 40, 1)
4 rows affected
create TRIGGER MyTable_Trigger_Delete2 ON dbo.MyTable
AFTER DELETE AS
BEGIN
/* Control-2 */
IF ((SELECT COUNT(*) FROM DELETED WHERE date<='2022-01-04')>0)
BEGIN
PRINT 'Control-2 worked.'
RAISERROR ('You cannot delete records before January 5th.', 16, 1)
ROLLBACK TRANSACTION
END
else
PRINT 'Control-2 passed.'
END
create TRIGGER MyTable_Trigger_Delete1 ON dbo.MyTable
AFTER DELETE AS
BEGIN
/* Control-1 */
IF ((SELECT COUNT(*) FROM DELETED WHERE islocked=1)>0)
BEGIN
PRINT 'Control-1 worked.'
RAISERROR ('You cannot delete a locked recording.', 16, 1)
ROLLBACK TRANSACTION
END
else
PRINT 'Control-1 passed.'
END
select * from MyTable
| name | city | date | value | islocked |
|---|---|---|---|---|
| John | Berlin | 2022-01-02 | 10 | 1 |
| Marty | Istanbul | 2022-01-03 | 20 | 1 |
| Aila | Washington | 2022-01-05 | 30 | 1 |
| Aise | Moskova | 2022-01-01 | 40 | 1 |
delete from MyTable where city='Berlin'
Msg 50000 Level 16 State 1 Line 11
You cannot delete records before January 5th.
Msg 3609 Level 16 State 1 Line 1
The transaction ended in the trigger. The batch has been aborted.
select * from MyTable
| name | city | date | value | islocked |
|---|---|---|---|---|
| John | Berlin | 2022-01-02 | 10 | 1 |
| Marty | Istanbul | 2022-01-03 | 20 | 1 |
| Aila | Washington | 2022-01-05 | 30 | 1 |
| Aise | Moskova | 2022-01-01 | 40 | 1 |