触发器 jackey

2009.0713

 

insert 触发器

 

CREATE TRIGGER [change_view_tDBDes_when_insert] ON [dbo].[tDBDes]
FOR INSERT
AS
declare @db varchar(255)
declare @sql1 nvarchar(4000), @sql2 nvarchar(4000), @sql3 nvarchar(4000), @sql4 nvarchar(4000), @sql5 nvarchar(4000), @sql6 nvarchar(4000), @sql7 nvarchar(4000)
declare @name1 varchar(255), @name_table varchar(255)
set @name1 = (select name1 from inserted)
set @db = substring(@name1,7,len(@name1)-6)
if(substring(@name1,7,1)=3) set @name_table='tAJB_UID'
else set @name_table='tYHXX'
set @sql1='create view v1_'+@db+' as '
set @sql2='create view v2_'+@db+' as '
set @sql3='create view v3_'+@db+' as '
set @sql4='create view v4_'+@db+' as '
set @sql5='create view v5_'+@db+' as '
set @sql6='create view v6_'+@db+' as '
set @sql7='create view v7_'+@db+' as '
if(substring(@db,1,1)=1)
begin
set @sql1=@sql1+'(SELECT a.*,UIDS,YHMC,YHDZ,EMAIL,YHZT,comment,JJLJHM,short_address,convert(char(20),CJSJ,120) CJSJ1,convert(char(20),ZHXGSJ,120) ZHXGSJ1,convert(char(20),ZHCSSJ,120) ZHCSSJ1,'''' reserved1,b.handlePlan as uidHandlepLan '
set @sql1=@sql1+' FROM vYH a,'+@name1+'.dbo.'+@name_table+' b'
set @sql1=@sql1+' WHERE a.UID=b.UID)'
end
else if(substring(@db,1,1)=3)
begin
set @sql1=@sql1+'(SELECT a.*,UIDS,YHMC,YHDZ,EMAIL,YHZT,comment,'''' JJLJHM,short_address,convert(char(20),CJSJ,120) CJSJ1,convert(char(20),ZHXGSJ,120) ZHXGSJ1,convert(char(20),ZHCSSJ,120) ZHCSSJ1,c.reserved1,b.handlePlan as uidHandlepLan, d.PlanDescription as uidPlanDescription '
set @sql1=@sql1+' FROM vYH a inner join '+@name1+'.dbo.'+@name_table+' b on a.UID=b.UID left join  CACDB_'+@db+'.dbo.userinfo c'
set @sql1=@sql1+' on substring(b.UID,11,10)=substring(c.id,1,10) left join tPlanManager d on d.PlanID =b.handlePlan)'
end
else
begin
set @sql1=@sql1+'(SELECT a.*,UIDS,YHMC,YHDZ,EMAIL,YHZT,comment,'''' JJLJHM,short_address,convert(char(20),CJSJ,120) CJSJ1,convert(char(20),ZHXGSJ,120) ZHXGSJ1,convert(char(20),ZHCSSJ,120) ZHCSSJ1,'''' reserved1,b.handlePlan as uidHandlepLan,c.PlanDescription as uidPlanDescription'
set @sql1=@sql1+' FROM vYH a inner join '+@name1+'.dbo.'+@name_table+' b'
set @sql1=@sql1+' on a.UID=b.UID left join tPlanManager c on c.PlanID =b.handlePlan)'
end
set @sql2=@sql2+'(select pre_UID,a.address,description,a.handlePlan,d.PlanDescription,c.UID,YHZT,comment'
set @sql2=@sql2+' from tArea a inner join '+@name1+'.dbo.'+@name_table+' c'
set @sql2=@sql2+' on a.pre_UID='''+substring(@name1,7,len(@name1)-6)+''' left join tPlanManager d on d.PlanID=a.handlePlan)'
set @sql3=@sql3+'(select a.YHDZ UID_Address, a.short_address, a.UID'
set @sql3=@sql3+' from '+@name1+'.dbo.'+@name_table+' a)'
set @sql4=@sql4+'(select a.id operator_id,a.loginId loginId,b.*,convert(char(20),AlarmDateTime,120) AlarmDateTime1'
set @sql4=@sql4+' from CACDB_User_Log.dbo.Handle_Alarm_Log_'+@db+' b left join tOperator a '
set @sql4=@sql4+' on b.Handler_ID=a.id)'
set @sql5=@sql5+'(select a.UID,a.monitor_id,a.mainAccount'
set @sql5=@sql5+' from '+@name1+'.dbo.tUser_Monitor a, CACDB_S1000.dbo.vYH b'
set @sql5=@sql5+' where a.isMonitor=1 and a.UID=b.UID and b.ifMonitor=2)'
set @sql5=@sql5+' union all (select a.UID,b.id,0 from '+@name1+'.dbo.'+@name_table+' a, CACDB_S6000.dbo.tMonitor b, t_c6101group_Area c, t_c6101group_monitor d where c.area_id='''+substring(@name1,7,len(@name1)-6)+''' and c.group_id=d.group_id and d.monitor_id=b.id)'
set @sql6=@sql6+'(select a.UID,a.monitor_id,a.mainAccount'
set @sql6=@sql6+' from '+@name1+'.dbo.tUser_Monitor a, CACDB_S1000.dbo.vYH b'
set @sql6=@sql6+' where a.UID=b.UID and b.ifMonitor=2)'
set @sql6=@sql6+' union all (select a.UID,b.id,0 from '+@name1+'.dbo.'+@name_table+' a, CACDB_S6000.dbo.tMonitor b, t_c6101group_Area c, t_c6101group_monitor d where c.area_id='''+substring(@name1,7,len(@name1)-6)+''' and c.group_id=d.group_id and d.monitor_id=b.id)'
--add by locker v7
if(substring(@db,1,1)='5')
begin
set @sql7=@sql7+'(SELECT a.c_hsb_cs_id as smasId,a.c_state,a.c_ser_healthchkintervalsecond,a.c_proxy_ip,a.c_register_ip,c.YHMC,c.YHZT,c.JJLJHM,c.UIDLevel,c.sendInfoToRelatedPerson,c.RelationCamer,d.* '
set @sql7=@sql7+' FROM '+@name1+'.dbo.mst_hsbpara a  INNER JOIN ' + @name1 +'.dbo.'+@name_table+' c ON c.hsb_userid = a.c_hsb_cs_id inner join vYH d on d.UID=c.UID)'
exec(@sql7)
end
exec(@sql1)
exec(@sql2)
exec(@sql3)
exec(@sql4)
exec(@sql5)
exec(@sql6)
declare x cursor for select name1 from tDBDes
open x
set @sql1='alter view vUser as '
set @sql2='alter view vArea as '
set @sql3='alter view v_UID_address as '
set @sql4='alter view vAlarmLog_operator as '
set @sql5='alter view vUID_Monitor as '
set @sql6='alter view vUID_Monitor_All as '
set @sql7='alter view v_SmasIDtoUser as '
fetch next from x into @name1
while(@@FETCH_STATUS=0)
begin
 set @db = substring(@name1,7,len(@name1)-6)
 
 set @sql1=@sql1+'(select * from v1_'+@db+')'
 set @sql1=@sql1+' UNION ALL '
 
 set @sql2=@sql2+'(select * from v2_'+@db+')'
 set @sql2=@sql2+' UNION ALL '
 
 set @sql3=@sql3+'(select * from v3_'+@db+')'
 set @sql3=@sql3+' UNION ALL '
 
 set @sql4=@sql4+'(select * from v4_'+@db+')'
 set @sql4=@sql4+' UNION ALL '
 
 set @sql5=@sql5+'(select * from v5_'+@db+')'
 set @sql5=@sql5+' UNION ALL '
 
 set @sql6=@sql6+'(select * from v6_'+@db+')'
 set @sql6=@sql6+' UNION ALL '
 print @db
 if(substring(@db,1,1)='5') set @sql7=@sql7+'(select * from v7_'+@db+')' + ' UNION ALL '
 
 fetch next from x into @name1
end
close x
deallocate x
set @sql1=substring(@sql1,1,len(@sql1)-10)
set @sql2=substring(@sql2,1,len(@sql2)-10)
set @sql3=substring(@sql3,1,len(@sql3)-10)
set @sql4=substring(@sql4,1,len(@sql4)-10)
set @sql5=substring(@sql5,1,len(@sql5)-10)
set @sql6=substring(@sql6,1,len(@sql6)-10)
set @sql7=substring(@sql7,1,len(@sql7)-10)
print @sql7
exec(@sql1)
exec(@sql2)
exec(@sql3)
exec(@sql4)
exec(@sql5)
exec(@sql6)
exec(@sql7)

 

 

 

 

 

 

 

删除触发器

 

CREATE TRIGGER [change_view_tDBDes_when_delete] ON [dbo].[tDBDes]
FOR DELETE
AS
declare @db varchar(255)
declare @sql1 nvarchar(4000), @sql2 nvarchar(4000), @sql3 nvarchar(4000), @sql4 nvarchar(4000), @sql5 nvarchar(4000), @sql6 nvarchar(4000), @sql7 nvarchar(4000)
declare @name1 varchar(255), @name_table varchar(255)
declare @viewName1 varchar(255),@viewName2 varchar(255),@viewName3 varchar(255),@viewName4 varchar(255),@viewName5 varchar(255),@viewName6 varchar(255),@viewName7 varchar(255)
set @name1 = (select name1 from deleted)
set @db = substring(@name1,7,len(@name1)-6)
if(substring(@name1,7,1)=3) set @name_table='tAJB_UID'
else set @name_table='tYHXX'
set @sql1='drop view v1_'+@db
set @sql2='drop view v2_'+@db
set @sql3='drop view v3_'+@db
set @sql4='drop view v4_'+@db
set @sql5='drop view v5_'+@db
set @sql6='drop view v6_'+@db
set @sql7='drop view v7_'+@db
set @viewName1 = 'v1_'+@db
set @viewName2 = 'v2_'+@db
set @viewName3 = 'v3_'+@db
set @viewName4 = 'v4_'+@db
set @viewName5 = 'v5_'+@db
set @viewName6 = 'v6_'+@db
set @viewName7 = 'v7_'+@db
 if exists (select * from sysobjects where name=''+@viewName1)
 exec(@sql1)
 if exists (select * from sysobjects where name=''+@viewName2)
 exec(@sql2)
 if exists (select * from sysobjects where name=''+@viewName3)
 exec(@sql3)
 if exists (select * from sysobjects where name=''+@viewName4)
 exec(@sql4)
 if exists (select * from sysobjects where name=''+@viewName5)
 exec(@sql5)
 if exists (select * from sysobjects where name=''+@viewName6)
 exec(@sql6)
 if exists (select * from sysobjects where name=''+@viewName7)
 exec(@sql7)
 --print 'aa'
  
declare x cursor for select name1 from tDBDes
open x
set @sql1='alter view vUser as '
set @sql2='alter view vArea as '
set @sql3='alter view v_UID_address as '
set @sql4='alter view vAlarmLog_operator as '
set @sql5='alter view vUID_Monitor as '
set @sql6='alter view vUID_Monitor_All as '
set @sql7='alter view v_SmasIDtoUser as '
fetch next from x into @name1
while(@@FETCH_STATUS=0)
begin
 set @db = substring(@name1,7,len(@name1)-6)
 
 set @sql1=@sql1+'(select * from v1_'+@db+')'
 set @sql1=@sql1+' UNION ALL '
 
 set @sql2=@sql2+'(select * from v2_'+@db+')'
 set @sql2=@sql2+' UNION ALL '
 
 set @sql3=@sql3+'(select * from v3_'+@db+')'
 set @sql3=@sql3+' UNION ALL '
 
 set @sql4=@sql4+'(select * from v4_'+@db+')'
 set @sql4=@sql4+' UNION ALL '
 
 set @sql5=@sql5+'(select * from v5_'+@db+')'
 set @sql5=@sql5+' UNION ALL '
 
 set @sql6=@sql6+'(select * from v6_'+@db+')'
 set @sql6=@sql6+' UNION ALL '
 
 if(substring(@db,1,1)='5') set @sql7=@sql7+'(select * from v7_'+@db+')' + ' UNION ALL '
 
 
 fetch next from x into @name1
end
close x
deallocate x
set @sql1=substring(@sql1,1,len(@sql1)-10)
set @sql2=substring(@sql2,1,len(@sql2)-10)
set @sql3=substring(@sql3,1,len(@sql3)-10)
set @sql4=substring(@sql4,1,len(@sql4)-10)
set @sql5=substring(@sql5,1,len(@sql5)-10)
set @sql6=substring(@sql6,1,len(@sql6)-10)
set @sql7=substring(@sql7,1,len(@sql7)-10)
exec(@sql1)
exec(@sql2)
exec(@sql3)
exec(@sql4)
exec(@sql5)
exec(@sql6)
exec(@sql7)