use [stalentzx]
go
/****** object: trigger [dbo].[gz_history_insert] script date: 2019/12/24 13:11:40 ******/
set ansi_nulls on
go
set quoted_identifier on
go
alter trigger [dbo].[gz_history_insert]
on [dbo].[ysyscolumns]
after insert–,delete,update
as
begin
declare @tablename varchar(50)
declare @colname varchar(50)
declare @colorder int
declare @coltype varchar(50)
declare @table_sql varchar(max)
declare @col_str varchar(max)
select @tablename = tablename , @colname = colname from inserted
if @tablename like ‘gz_patsetdata%’ and
isnumeric(right(@tablename , 4)) = 1
begin
select * into #ysyscolumns from ysyscolumns where tablename = @tablename
if exists(select 1 from ysyscolumns
where tablename = ‘c2’ + right(@tablename , 4)
)
begin
set @col_str = ”
select @col_str = @col_str + ‘,’ + colname + ‘ ‘ +
(case when coltype in (‘varchar’,’char’)
then coltype + ‘(‘ + convert(varchar(4000) , colwidth) + ‘)’
when coltype in (‘decimal’ , ‘numeric’)
then coltype + ‘(‘ + convert(varchar(4000) , colwidth) + ‘,’ +
convert(varchar(4000) , colprecision) + ‘)’
else coltype
end) +
(case when isnull(ysyscolumns.coldefault , ”) <> ”
then (case when charindex(‘,’ , coldefault) > 0
then ‘ default ‘ +
substring(coldefault , 1 , charindex(‘,’ , coldefault) – 1)
else ‘ default ‘ + coldefault
end)
else ”
end)
from ysyscolumns
where tablename=’gz_patsetdata’ + right(@tablename , 4) and
colname = @colname and
colname not in(select colname from ysyscolumns
where tablename = ‘c2’ + right(@tablename,4) and
colname = @colname
)
select @table_sql = ‘alter table c2’ + right(@tablename,4) + ‘ add ‘ +
substring(@col_str , 2 , 40000) + ‘;’
exec (@table_sql)
insert into ysyscolumns(tablename , colname , colorder , coltype , colwidth ,
colprecision , colnull , coldefault , displaylabel , displaywidth ,
displayformat , editformat , colvarify , varifymsg , colvisible ,
colproperty , colgroup , enus , zhtw , otherlanguage ,
relationrule , colgroupother)
select ‘c2’ + right(@tablename , 4) , colname , colorder , coltype , colwidth ,
colprecision , colnull , coldefault , displaylabel , displaywidth ,
displayformat , editformat , colvarify , varifymsg , colvisible ,
colproperty , colgroup , enus , zhtw , otherlanguage ,
relationrule , colgroupother
from inserted
where colname not in(select colname from ysyscolumns
where tablename = ‘c2’ + right(@tablename,4) and
colname = @colname
)
end
else
begin
———————————–创建历史记录物理表———————————
set @col_str = ”
select @col_str = @col_str + ‘,’ + colname + ‘ ‘ +
(case when coltype in (‘varchar’,’char’)
then coltype + ‘(‘ + convert(varchar(4000) , colwidth) + ‘)’
when coltype in (‘decimal’ , ‘numeric’)
then coltype + ‘(‘ + convert(varchar(4000) , colwidth) + ‘,’ +
convert(varchar(4000) , colprecision) + ‘)’
else coltype
end) +
(case when isnull(ysyscolumns.coldefault , ”) <> ”
then (case when charindex(‘,’ , coldefault) > 0
then ‘ default ‘ +
substring(coldefault , 1 , charindex(‘,’ , coldefault) – 1)
else ‘ default ‘ + coldefault
end)
else ”
end)
from ysyscolumns
where tablename=’gz_patsetdata’ + right(@tablename,4)
select @table_sql = ‘create table c2’ + right(@tablename,4) +
‘ (‘ + substring(@col_str , 2 , 40000) + ‘);’
exec (@table_sql)
——由于不存在任何对应的历史表薪资项栏位描述信息，所以需建立对应的历史表描述
select * into #ysystables from ysystables
where tablename = ‘gz_patsetdata’+ right(@tablename,4)
insert into ysystables(tablename , tabletypeid , tableorder , tablelabel , acessable ,
acessmodule , tablevisible ,presere , isuserdisplay , teamvisible , moduleid)
select ‘c2’+right(@tablename,4) , 7 , tableorder , tablelabel+’_h’ , ‘111’ ,
‘010000000000000000000000000000’ , ‘1’ , 0 , 1 , 1 , ’01’
from #ysystables
——建立对应历史表的薪资项栏位信息描述
insert into ysyscolumns(tablename , colname , colorder , coltype , colwidth ,
colprecision , colnull , coldefault , displaylabel , displaywidth ,
displayformat , editformat , colvarify , varifymsg , colvisible ,
colproperty , colgroup , enus , zhtw , otherlanguage ,
relationrule , colgroupother)
select ‘c2’ + right(@tablename , 4) , colname , colorder , coltype , colwidth ,
colprecision , colnull , coldefault , displaylabel , displaywidth ,
displayformat , editformat , colvarify , varifymsg , colvisible ,
colproperty , colgroup , enus , zhtw , otherlanguage ,
relationrule , colgroupother
from #ysyscolumns
end
end
end