CODEWITHSUNDEEP
if-statementtriggerssql-server-2014
Ayyub
Ayyub

Reputation: 53

IF statements in SQL Server triggers

I need to create a SQL Server trigger to block updates and deletes to a table Service.

This action should be done only to Service in which the column States sample data is "completed".

It should allow updates and deletes to Service in which the column States sample data is "active".

This is what I tried, I am having problems with doing the else operation (that is allowing updates to Service in which the column State sample data is "active").

CREATE TRIGGER [Triggername]
ON dbo.Service
FOR INSERT, UPDATE, DELETE
AS
    DECLARE @para varchar(10),
            @results varchar(50)

    SELECT @para = Status
    FROM Service

    IF (@para = 'completed') 
    BEGIN
        SET @results = 'An invoiced service cannot be updated or deleted!';
        SELECT @results;
    END

BEGIN
    RAISERROR ('An invoiced service cannot be updated or deleted', 16, 1)
    ROLLBACK TRANSACTION

    RETURN 
END

Upvotes: 0

Views: 57

Answers (1)

marc_s
marc_s

Reputation: 755157

So if I understand you correctly, any UPDATE or DELETE should be allowed if the State column has a value of Active, but stopped in any other case??

Then I'd do this:

CREATE TRIGGER [Triggername]
ON dbo.Service
FOR UPDATE, DELETE
AS
BEGIN
   -- if any row exists in the "Deleted" pseudo table of rows that WERE 
   -- in fact updated or deleted, that has a state that is *not* "Active"
   -- then abort the operation
   IF EXISTS (SELECT * FROM Deleted WHERE State <> 'Active')
        ROLLBACK TRANSACTION

   -- otherwise let the operation finish 
END

As a note: you cannot easily return messages from a trigger (with SELECT @Results) - the trigger just silently fails by rolling back the currently active transaction

Upvotes: 1

Related Questions