create table 表A (年 int, 数量 int)insert into 表A select 2012, 132 union all select 2011, 332 union all select 1985, 222 union all select 1984, 332;-- 建触发器 create trigger tr_表A on 表A for update,delete,insert as begin if exists(select 1 from inserted where 年<2011) or exists(select 1 from deleted where 年<2011) begin rollback transaction; raiserror(N'禁止update,insert,delete 2011年前的数量.',10,1); end end--> 测试update,insert,delete 2011年前的数量update 表A set 数量=611 where 年=1985 /* 禁止update,insert,delete 2011年前的数量. Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted. */insert into 表A select 1981,132 /* 禁止update,insert,delete 2011年前的数量. Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted. */delete from 表A where 年=1984 /* 禁止update,insert,delete 2011年前的数量. Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted. */--> 表数据未变更 select * from 表A /* 年 数量 ----------- ----------- 2012 132 2011 332 1985 222 1984 332(4 row(s) affected) */
create table 表A
(年 int, 数量 int)insert into 表A
select 2012, 132 union all
select 2011, 332 union all
select 1985, 222 union all
select 1984, 332;-- 建触发器
create trigger tr_表A
on 表A
for update,delete,insert
as
begin
if exists(select 1 from inserted where 年<2011)
or exists(select 1 from deleted where 年<2011)
begin
rollback transaction;
raiserror(N'禁止update,insert,delete 2011年前的数量.',10,1);
end
end--> 测试update,insert,delete 2011年前的数量update 表A set 数量=611 where 年=1985
/*
禁止update,insert,delete 2011年前的数量.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/insert into 表A select 1981,132
/*
禁止update,insert,delete 2011年前的数量.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/delete from 表A where 年=1984
/*
禁止update,insert,delete 2011年前的数量.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/--> 表数据未变更
select * from 表A
/*
年 数量
----------- -----------
2012 132
2011 332
1985 222
1984 332(4 row(s) affected)
*/