触发器insert
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
推荐阅读
-
解决Hibernate JPA中insert插入数据后自动执行select last_insert_id()
-
MySQL 系列(三)你不知道的 视图、触发器、存储过程、函数、事务、索引、语句
-
MySQL因大事务导致的Insert慢实例分析
-
JS排序方法(sort,bubble,select,insert)代码汇总
-
navicat中创建存储过程、触发器和使用游标的简单实例(图文)
-
SQL Server 2000中的触发器使用
-
SQL Server 触发器 表的特定字段更新时,触发Update触发器
-
你真的了解触发器么 数据实时同步更新问题剖析
-
SQL SERVER 触发器介绍
-
SqlServer-触发器