dimanche 10 juillet 2016

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 coulumn State sample data is "active".

go
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
go

Aucun commentaire:

Enregistrer un commentaire