触发器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@TABLENAMEVARCHAR(50)
DECLARE@COLNAMEVARCHAR(50)
DECLARE@COLORDERINT
DECLARE@COLTYPEVARCHAR(50)

DECLARE @TABLE_SQL VARCHAR(MAX)
DECLARE@COL_STR VARCHAR(MAX)

SELECT@TABLENAME = TABLENAME , @COLNAME = COLNAMEFROMINSERTED
IF@TABLENAMELIKE'GZ_PATSETDATA%'AND
ISNUMERIC(RIGHT(@TABLENAME , 4)) = 1

BEGIN

SELECT*INTO#YSYSCOLUMNSFROMYSYSCOLUMNSWHERETABLENAME = @TABLENAME

IFEXISTS(SELECT1FROMYSYSCOLUMNS
WHERETABLENAME = 'C2' + RIGHT(@TABLENAME , 4)
)

BEGIN

SET@COL_STR = ''
SELECT@COL_STR = @COL_STR + ',' + COLNAME + ' ' +
(CASEWHENCOLTYPEIN('VARCHAR','CHAR')
THENCOLTYPE + '(' + CONVERT(VARCHAR(4000) , ColWidth) + ')'

WHENCOLTYPEIN('DECIMAL' , 'NUMERIC')
THENColType + '(' + CONVERT(VARCHAR(4000) , ColWidth) + ',' +
CONVERT(VARCHAR(4000) , ColPrecision) + ')'
ELSECOLTYPE
END) +

(CASEWHENISNULL(YSYSCOLUMNS.ColDefault , '') <> ''
THEN(CASEWHENCHARINDEX(',' , ColDefault) > 0
THEN' Default ' +
SUBSTRING(ColDefault , 1 , CHARINDEX(',' , ColDefault) - 1)
ELSE' Default ' + ColDefault
END)
ELSE ''
END)
FROMYSYSCOLUMNS
WHERETABLENAME='GZ_PATSETDATA' + RIGHT(@TABLENAME , 4)AND
COLNAME = @COLNAMEAND
COLNAMENOTIN(SELECTCOLNAMEFROMYSYSCOLUMNS
WHERETABLENAME = '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)

INSERTINTOYSYSCOLUMNS(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
FROMINSERTED
WHERECOLNAMENOTIN(SELECTCOLNAMEFROMYSYSCOLUMNS
WHERETABLENAME = 'C2' + RIGHT(@TABLENAME,4)AND
COLNAME = @COLNAME
)

END

ELSE
BEGIN
-----------------------------------创建历史记录物理表---------------------------------
SET@COL_STR = ''
SELECT@COL_STR = @COL_STR + ',' + ColName + ' ' +
(CASEWHENCOLTYPEIN('VARCHAR','CHAR')
THENCOLTYPE + '(' + CONVERT(VARCHAR(4000) , ColWidth) + ')'

WHENCOLTYPEIN('DECIMAL' , 'NUMERIC')
THENColType + '(' + CONVERT(VARCHAR(4000) , ColWidth) + ',' +
CONVERT(VARCHAR(4000) , ColPrecision) + ')'
ELSECOLTYPE
END) +

SELECT@TABLE_SQL = 'CREATE TABLE C2' + RIGHT(@TABLENAME,4) +
' (' + SUBSTRING(@COL_STR , 2 , 40000) + ');'
EXEC (@TABLE_SQL)

------由于不存在任何对应的历史表薪资项栏位描述信息，所以需建立对应的历史表描述
SELECT*INTO#YSYSTABLESFROMYSYSTABLES
WHERETABLENAME = 'GZ_PATSETDATA'+ RIGHT(@TABLENAME,4)

INSERTINTOySysTables(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

------建立对应历史表的薪资项栏位信息描述
INSERTINTOYSYSCOLUMNS(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

触发器insert

相关推荐