我需要创建一个SQL Server触发器来阻止对表Service的更新和删除。
应该只对其中列State的示例数据“完成”的Service执行此操作。
它应该允许对列State的样本数据是“活动的”的Service进行更新和删除。
这就是我所尝试的,我在执行else操作时遇到了问题(即允许更新其中列State样本数据是“活动的”的Service )。
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发布于 2016-07-10 15:32:44
因此,如果我理解正确的话,如果State列的值为Active,则应该允许任何UPDATE或DELETE,但在任何其他情况下都应停止。
然后我会这样做:
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注意:您不能轻易地从触发器返回消息(使用SELECT @Results) -触发器只是通过回滚当前活动的事务来静默地失败
https://stackoverflow.com/questions/38289573
复制相似问题