/*
Author :Bean
Create date :2011-08-12
问题描述1 :很多时候我们一个模板会有很多的表格,例如Care 项目,一共有29张表,想删除一条单据,
那么我们要写29条Delete语句
问题描述2 :如果不写触发器,很多时候只是_1表的数据被删除了,剩下的_2,_3,_4...表的数据还会存在数据库里浪费资源
功能描述 :主要用于Landa数据库单据删除,当删除_1表后,后面带动的_2,_3,_4...表相同VGUID的单据会被自动删除
优点 :快捷,方便
使用案例 :Exec Porc_AddTrigger '表名_1'
*/
if OBJECT_ID('Porc_AddTrigger','P') is not null drop proc Porc_AddTrigger
go
create Proc Porc_AddTrigger
@TB_name varchar(50)
as
Begin
Begin Transaction
Begin Try
if RIGHT(@TB_name,2)!='_1'
RaisError('Table name should end with "_1"',16,1)
Else
Begin
declare
@sql varchar(max)
set @sql='
create trigger TR_'+left(@TB_name,LEN(@TB_name)-2)+'
on '+@TB_name+'
for delete
as
Begin
declare @TMP varchar(50),
@i numeric(38,0),
@table varchar(50),
@SQL varchar(max),
@VGUID uniqueidentifier
set @TMP='+''''+left(@TB_name,len(@TB_name)-1)+''''+'
set @table=''CSEMPL_2''
set @VGUID=newid()
set @i=2
select @VGUID=VGUID from deleted
while 1=1
Begin
set @table=@TMP+convert(varchar(10),@i)
if OBJECT_ID(@table,''U'') is not null
Begin
set @SQL=''delete from '' + @table +'' where VGUID ='''+'+''''''''+'+' convert(varchar(50),@VGUID)+'+''''''''''+'
exec(@SQL)
set @i=@i+1
End Else
Break;
end
End'
Exec(@sql)
End
Commit Transaction
End Try
Begin Catch
Rollback Transaction
declare @Error_Message varchar(1000)
set @Error_Message= 'Error_Nuber :'+CONVERT(varchar(5),Error_Number())+char(13)+
'Error_Line :'+Convert(varchar(5),Error_Line())+char(13)+
'Error_Message:'+CONVERT(varchar(50),Error_Message())
RaisError(@Error_message,16,1)
End Catch
End