当我在表中插入三行时,我有一个触发器,它检查三行之和是否等于10,否则就完成回滚。问题是,只有当第三行试图插入时,这个触发器才会触发,这意味着前两行已经插入。但现在我想更改这个触发器,这样,如果总和不是三行中的10行,我希望删除插入的前两行。有人知道如何更改触发器以便删除最后插入的两行吗?
当前触发代码:
ALTER TRIGGER [dbo].[trg_Sum] ON [dbo].[Table]
AFTER UPDATE, INSERT
AS
IF EXISTS (
SELECT TOP 1 NULL FROM (
SELECT SUM(Procentandel) AS Sum
FROM Table
WHERE
ID = (SELECT ID FROM inserted)
AND
ID2 = (SELECT ID2 FROM inserted)
GROUP BY ID, ID2
HAVING COUNT(*) = 3) t
WHERE t.Sum <> 10
)
BEGIN
RAISERROR ('The sum must be 10!',16, 1)
ROLLBACK TRANSACTION
END
下面的触发器将保持不变"id1和id2的组合中没有正好有三行的和不等于10"。
create trigger dbo.trg_sum on dbo.trigger_test after update, insert as
delete
t
from
dbo.trigger_test t
where exists (
select
'x'
from
dbo.trigger_test t2
where
t.id1 = t2.id1 and
t.id2 = t2.id2 and
exists (
select
'x'
from
inserted i
where
i.id1 = t2.id1 and
i.id2 = t2.id2
)
group by
t2.id1,
t2.id2
having
count(*) = 3 and
sum(t2.Procentandel) != 10
);
要了解为什么以基于集合的方式进行,请考虑以下语句:
insert into dbo.trigger_test (
Id1, Id2, Procentandel
) values
(1, 1, 1),
(1, 1, 1),
(1, 1, 1);
insert into dbo.trigger_test (
Id1, Id2, Procentandel
) values
(1, 1, 1);
insert into dbo.trigger_test (
Id1, Id2, Procentandel
) values
(1, 1, 1),
(1, 1, 1);
也就是说,触发器感觉这是错误的设计。它看起来更像是一个有三列的表,每个值槽一列会更容易。
假设您引用的所有3行都在同一事务中被修改,您可以这样做:
ALTER TRIGGER [dbo].[trg_Sum] ON [dbo].[Table]
AFTER UPDATE, INSERT
AS
IF (SELECT SUM(Procentandel) FROM inserted) <> 10)
BEGIN
RAISERROR ('The sum must be 10!',16, 1)
ROLLBACK TRANSACTION
END