在表上设置触发器以限制INSERT,DELETE,UPDATE操作的数量 [英] Setup a Trigger on Table to limit the number of INSERT, DELETE, UPDATE Operations
本文介绍了在表上设置触发器以限制INSERT,DELETE,UPDATE操作的数量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
例如:用户不应执行 INSERT
, DELETE
和 UPDATE
操作在给定的时间点上对特定表的操作次数超过 X 次(如果用户执行了 X +1会触发一次。
For example: A user should not be able to perform INSERT
, DELETE
and UPDATE
operations more than X number of times on a particular table at a given point of time, if the user performs an operation X+1 times than a trigger will be launched.
推荐答案
create table dbo.targetTable
(
id int,
colA varchar(10)
);
go
create or alter trigger dbo.DMLxTimes on dbo.targetTable
for insert, update, delete
as
begin
/*
--old days..
IF @@ROWCOUNT > 5
begin
rollback;
end
--*/
declare @maxrows int = 5; --maximum number of rows allowed per dml action
if
(select count(*) from inserted) > @maxrows
or
(select count(*) from deleted) > @maxrows
begin
rollback;
end
end
go
insert into dbo.targetTable(id, colA)
values (0, 'a'),(0, 'a'),(0, 'a'),(0, 'a'),(0, 'a'); --5 rows inserted
go
insert into dbo.targetTable(id, colA)
values (0, 'a'),(0, 'a'),(0, 'a'),(0, 'a'),(0, 'a'),(0, 'a'); --6 rows inserted ... error
go
insert into dbo.targetTable(id, colA)
values (0, 'a'),(0, 'a'); --2 rows inserted
go
--all updated, error
update dbo.targetTable
set colA = colA;
go
--ok
update top(5) dbo.targetTable
set colA = colA;
go
delete top (4) from dbo.targetTable;
go
--ok
update top(15) dbo.targetTable
set colA = colA;
go
这篇关于在表上设置触发器以限制INSERT,DELETE,UPDATE操作的数量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文