Before delete trigger using transact-SQL

Question

I need to prevent from deleting an user who's occupation is 'Programmer'. So, the database table structure is:

 TABLE Client
 Id   |   Name    |    SSN    |    Occupation    |    Birthdate    |

The trigger I have created so far looks like this:

CREATE TRIGGER Check_User_Occupation
ON dbo.Client
FOR DELETE
AS
BEGIN
    IF(SELECT c.Occupation
    FROM deleted AS d
    INNER JOIN Client as c ON d.Id = c.Id) = 'Programmer'
    BEGIN
        PRINT 'YES';
        ROLLBACK TRAN
        RAISEERROR(SELECT 'Could not delete client because he is a programmer');
END
END
GO

I guess that the row is deleted before it can be compared, but I don't know the right way to approach this.

Any help is appreciated!

Answer 1

correct syntax is here:

(You do not need join deleted table to table itself, in deleted is whole row from delete statement...) But be aware a fact, that you are not able to do batch delete in this case. It will work only for row deletes.

CREATE TRIGGER Check_User_Occupation
ON dbo.Client
FOR DELETE
AS
BEGIN
    IF(SELECT Occupation FROM deleted) = 'Programmer'
    BEGIN
        PRINT 'YES';

        ROLLBACK

        RAISERROR ('Could not delete client because he is a programmer', -- Message text  
                    16, -- Severity 
                    1 -- State
                    )

    END
END
GO

For batch commands is better to use there following statement:

IF(SELECT COUNT(*) FROM deleted WHERE Occupation = 'Programmer') > 0

But ofc if you want to join on this table, you need to use

INSTEAD OF DELETE

And in this case you dont need use ROLLBACK

Finnaly it should looks like:

CREATE TRIGGER Check_User_Occupation
ON dbo.Client
INSTEAD OF DELETE
AS
BEGIN
    IF(SELECT Occupation FROM deleted) = 'Programmer'
    BEGIN
        PRINT 'YES';        

        RAISERROR ('Could not delete client because he is a programmer', -- Message text  
                    16, -- Severity 
                    1 -- State
                    )

    END
END
GO