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