在sql server也可以用@@error捕获,这和RAISERROR怎么抛出错误有关RAISERROR('Message',16,1)
@@error=50000RAISERROR(msgid,16,1) --msgid是个数字,msgid〉50000
@@error=msgid可以自己定义错误信息
exec sp_addmessage 50005,@msgtext ='你的信息'RAISERROR(50005,16,1)
@@error=50005select description from master..sysmessages
where error=@@error
可以查出信息
@@error=50000RAISERROR(msgid,16,1) --msgid是个数字,msgid〉50000
@@error=msgid可以自己定义错误信息
exec sp_addmessage 50005,@msgtext ='你的信息'RAISERROR(50005,16,1)
@@error=50005select description from master..sysmessages
where error=@@error
可以查出信息
CREATE trigger tr_delete_Test2
on Test2
for delete
as
set nocount on
if exists (select 1 from deleted where id=0)
begin
RAISERROR ('不能删除id=0的记录', 16, 1)
ROLLBACK TRANSACTION
end之后在存储过程中这么用:
create proc dbo.deltest2
as
delete from test2
if @@error<>0
delete from test2 where id>0
delete from test2 where id>0这句没有被执行。
看了一下,deleted存储了待删的所有记录的所有字段-_-,这样对性能的影响比原来想象的更厉害,对那个防止误删的问题老大有没有更好的办法hoho?
create trigger trg_delete_table on table
instead of delete
as
delete table from table as a inner join deleted as d on a.id = d.id where d.id <> 1
GO