触发器insert

触发器insertUSE [stalentzx]GO/****** Object: Trigger [dbo].[GZ_HISTORY_INSERT] Script Date: 2019/12/24 13:11:40

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

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/10388.html

(0)
上一篇 2022-12-28
下一篇 2022-12-28

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注