欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

触发器insert

程序员文章站 2022-06-23 22:36:04
USE [stalentzx]GO/****** Object: Trigger [dbo].[GZ_HISTORY_INSERT] Script Date: 2019/12/24 13:11:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ON ......

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