CREATE TRIGGER tD_shzb ON dbo.shzb FOR DELETE AS begin if exists( select deleted.* from deleted,shmxb where shmxb.shdh=deleted.shdh ) begin rollback transaction raiserror("The value of field_SHDH in the table_SHZB does exist in the table_SHMXB",11,1) return end if exists( select * from deleted,gyftb where deleted.shdh = gyftb.shdh ) begin rollback transaction raiserror("存在相应的收货付退记录,不允许删除!",11,1) return end if exists( select * from deleted,fkqkmxb where deleted.shdh = fkqkmxb.shdh ) begin rollback transaction raiserror("存在相应的付款记录,不允许删除!",11,1) return end return end GO
对于前端程序调用,似乎不太好处理,我用DELPHI,是获取出错信息来处理的。
2.事务是以COMMIT和ROLLBACK结束的在一个事物结束之前同一个会话是不能再起另一个事务的。
FOR DELETE
AS
begin
if exists(
select deleted.*
from deleted,shmxb
where shmxb.shdh=deleted.shdh
)
begin
rollback transaction
raiserror("The value of field_SHDH in the table_SHZB
does exist in the table_SHMXB",11,1)
return
end
if exists(
select *
from deleted,gyftb
where deleted.shdh = gyftb.shdh
)
begin
rollback transaction
raiserror("存在相应的收货付退记录,不允许删除!",11,1)
return
end if exists(
select *
from deleted,fkqkmxb
where deleted.shdh = fkqkmxb.shdh
)
begin
rollback transaction
raiserror("存在相应的付款记录,不允许删除!",11,1)
return
end
return
end
GO