CREATE TABLE TB20150202001(
ID INT
)
GO
CREATE TRIGGER TRG_TRG_20150202001
ON TB20150202001
INSTEAD OF INSERT
AS
BEGIN
IF 1=1
RAISERROR(N'自定义错误.',10,1)
ELSE
SELECT 1
END
GO
INSERT INTO TB20150202001
SELECT 1SELECT * FROM TB20150105001类似这样
ID INT
)
GO
CREATE TRIGGER TRG_TRG_20150202001
ON TB20150202001
INSTEAD OF INSERT
AS
BEGIN
IF 1=1
RAISERROR(N'自定义错误.',10,1)
ELSE
SELECT 1
END
GO
INSERT INTO TB20150202001
SELECT 1SELECT * FROM TB20150105001类似这样
create table tb(id int,code varchar(10))insert into tb
select 1,'aaa' union all
select 2,'bbb'
-- 建触发器
create trigger tr_tb on tb
for update
as
begin
if exists(select 1
from inserted
where code is null or code='')
begin
raiserror('code为空不能进行更新',18,18)
rollback tran
end
end
-- 测试1
update tb set code='ccc' where id=1
/*
(1 row(s) affected)
*/
-- 测试2
update tb set code='' where id=2
/*
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Msg 50000, Level 18, State 18, Procedure tr_tb, Line 10
code为空不能进行更新
*/
-- 测试3
update tb set code=null
/*
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Msg 50000, Level 18, State 18, Procedure tr_tb, Line 10
code为空不能进行更新
*/
-- 结果
select * from tb
/*
id code
----------- ----------
1 ccc
2 bbb(2 row(s) affected)
*/