0

I have a table. I have a field in my table that says "islocked" record is locked. This number of fields can be more than one. isApproved, isAccepted etc.

There are various triggers (after delete etc.). There are procedures running inside the triggers.

Within the program, there are operations that delete one by one, and operations that delete in bulk.

The user asked me to prevent my deletion if the record is locked.

Instead of coding in the after trigger, I wanted to do it with the "INSTEAD OF DELETE" trigger, which was before it.

But it says record deleted. It is not deleted.

Multiple records are never deleted.

If there are locked and unlocked records in mass delete, it doesn't work either.

Where am I doing wrong?

At the same time, if this trigger gives an error, I want other triggers to not work at all. Is it possible to prioritize?

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, 0)
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, 0)
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'Aise', N'Moskova', CAST(N'2022-01-01' AS Date), 40, 1)
create TRIGGER MyTable_Trigger_Delete ON dbo.MyTable
INSTEAD OF DELETE AS

BEGIN

    IF ((SELECT COUNT(*) FROM DELETED WHERE islocked=1)>0)

        BEGIN
            RAISERROR ('You cannot delete a locked recording.', 16, 1)
            ROLLBACK TRANSACTION
        END

END
select * from MyTable
name city date value islocked
John Berlin 2022-01-02 10 0
Marty Istanbul 2022-01-03 20 1
Aila Washington 2022-01-05 30 0
Aise Moskova 2022-01-01 40 1
select * from MyTable where (city='Istanbul' OR city='Berlin')
name city date value islocked
John Berlin 2022-01-02 10 0
Marty Istanbul 2022-01-03 20 1
delete from MyTable where (city='Istanbul' OR city='Berlin')
Msg 50000 Level 16 State 1 Line 9
You cannot delete a locked recording.
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 0
Marty Istanbul 2022-01-03 20 1
Aila Washington 2022-01-05 30 0
Aise Moskova 2022-01-01 40 1
delete from MyTable where city='Berlin'
1 rows affected
select * from MyTable
name city date value islocked
John Berlin 2022-01-02 10 0
Marty Istanbul 2022-01-03 20 1
Aila Washington 2022-01-05 30 0
Aise Moskova 2022-01-01 40 1

fiddle

1 Answer 1

2

In an INSTEAD trigger you need to DELETE your records also, as the trigger goes instead of the delete action. See example below:

CREATE TRIGGER MyTable_Trigger_Delete
ON dbo.MyTable
INSTEAD OF DELETE
AS
BEGIN

    IF EXISTS (SELECT 1 FROM deleted WHERE islocked = 1)
    BEGIN
        RAISERROR('You cannot delete a locked recording.', 16, 1);
        ROLLBACK TRANSACTION;
    END;
    ELSE
    BEGIN
        /*we now can delete the rows*/
        DELETE t
        FROM dbo.MyTable t
            JOIN Deleted AS d
                ON d.Name = t.Name
                   AND d.City = t.City
                   AND d.Date = t.Date
                   AND d.Value = t.Value;
    END;

END;

Also, in your example you don't have a primary key specified. I advice you will do that ;-)

Note that the records won't be deleted when a part of it is locked now. If you also want to delete those you should edit your trigger like this:

CREATE OR ALTER TRIGGER MyTable_Trigger_Delete
ON dbo.MyTable
INSTEAD OF DELETE
AS
BEGIN

    IF EXISTS (SELECT 1 FROM deleted WHERE islocked = 1)
    BEGIN
        RAISERROR('You cannot delete a locked recording.', 16, 1);
    END;
    
    
        /*we now can delete the rows which are not locked*/
        DELETE t
        FROM dbo.MyTable t
            JOIN Deleted AS d
                ON d.Name = t.Name
                   AND d.City = t.City
                   AND d.Date = t.Date
                   AND d.Value = t.Value
                   AND t.islocked = 0
    

END;
1
  • Then it's wrong to use "instead" trigger. Because when I execute the sentence "delete from MyTable where city='Berlin'" I want to do the checks in this trigger. I wanted the other trigger (AFTER DELETE) to work if it passed the checks. If After Trigger doesn't return raiserror, I wanted the first sentence I run to be processed. Seems like it would be more convenient to put an if in the "AFTER DELETE" trigger and then try...catch. I understood the INSTEAD trigger, thanks. I wanted to make multiple delete triggers and process them sequentially, but it's not the right way.
    – omerix
    Nov 29, 2022 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.