Tenormis's Blog

Appearance

SQL Server 触发器管理 ​

1. 生成 M 表触发器脚本 ​

SQL
USE $DBName$;

DECLARE @BAK_ID_COL_NAME       VARCHAR(20) = '$BAK_ID$'
      , @BAK_OPT_ID_COL_NAME   VARCHAR(20) = '$BAK_OPT_ID$'
      , @BAK_OPT_TYPE_COL_NAME VARCHAR(20) = '$BAK_OPT_TYPE$'
      , @BAK_DT_COL_NAME       VARCHAR(20) = '$BAK_DT$';
DECLARE @triSchName      VARCHAR(50) = '$tri$'       --
      , @addTblNameTempl VARCHAR(50) = '$addbak_$#'  --
      , @addTriNameTempl VARCHAR(50) = '$addtri_$#'  --
      , @delTblNameTempl VARCHAR(50) = '$delbak_$#'  --
      , @delTriNameTempl VARCHAR(50) = '$deltri_$#'; --

-- specifyCols 不用包含 Id 列（已自动包含在内，不过提供了也没有关系）
DECLARE @tar TABLE (schName VARCHAR(500), tblName VARCHAR(500), specifyCols VARCHAR(MAX));
INSERT @tar (schName, tblName, specifyCols) VALUES ('$dbo$', '$TblName$', NULL);

IF EXISTS (SELECT 1 FROM @tar t WHERE t.schName = @triSchName)
BEGIN
  PRINT '>> error: @tar 中存在 schName 为 ' + @triSchName + ' 的记录';
  RETURN;
END;

SELECT      t.schName, t.tblName, TRIM(t1.colName) colName
INTO        #specifyCols
FROM        (
  SELECT t.schName, t.tblName, CONVERT(XML, '<r>' + REPLACE(t.specifyCols, ',', '</r><r>') + '</r>') specifyColsXML
  FROM   @tar t
  WHERE  t.specifyCols <> ''
) t
OUTER APPLY (SELECT t.c.value('.', 'VARCHAR(500)') colName FROM t.specifyColsXML.nodes('r') t(c) ) t1
WHERE       t1.colName <> '';

DECLARE @typeInfo TABLE (typeName VARCHAR(500) NOT NULL, hasLen TINYINT NOT NULL, hasPre TINYINT NOT NULL, hasSca TINYINT NOT NULL);

-- 目前暂不支持以下几种数据类型：
--   1. 本身触发器所不支持：text、ntext、image
--   2. 以及其它特殊的类型：geography、geometry、hierarchyid、sql_variant
INSERT @typeInfo (typeName, hasLen, hasPre, hasSca)
VALUES ('bigint', 0, 0, 0)
     , ('binary', 1, 0, 0)
     , ('bit', 0, 0, 0)
     , ('char', 1, 0, 0)
     , ('date', 0, 0, 0)
     , ('datetime', 0, 0, 0)
     , ('datetime2', 0, 0, 1)
     , ('datetimeoffset', 0, 0, 1)
     , ('decimal', 0, 1, 1)
     , ('float', 0, 0, 0)
     , ('int', 0, 0, 0)
     , ('money', 0, 0, 0)
     , ('nchar', 2, 0, 0)
     , ('numeric', 0, 1, 1)
     , ('nvarchar', 2, 0, 0)
     , ('real', 0, 0, 0)
     , ('smalldatetime', 0, 0, 0)
     , ('smallint', 0, 0, 0)
     , ('smallmoney', 0, 0, 0)
     , ('sysname', 0, 0, 0)
     , ('time', 0, 0, 1)
     , ('timestamp', 0, 0, 0)
     , ('tinyint', 0, 0, 0)
     , ('uniqueidentifier', 0, 0, 0)
     , ('varbinary', 1, 0, 0)
     , ('varchar', 1, 0, 0)
     , ('xml', 0, 0, 0);

-- 获取目标表信息
SELECT     tbl.object_id tblObjId
         , t.schName
         , t.tblName
         , idCol.name idColName
         , UPPER(idColType.name)
           + CASE WHEN idColTypeInfo.hasLen > 0 OR idColTypeInfo.hasPre > 0 OR idColTypeInfo.hasSca > 0 THEN
                    '('
                    + CASE WHEN idColTypeInfo.hasLen > 0 AND idCol.max_length = -1 THEN 'MAX'
                           ELSE
                             CONCAT_WS(
                               ', '
                             , CASE WHEN idColTypeInfo.hasLen > 0 OR idColTypeInfo.hasPre > 0 THEN COLUMNPROPERTY(tbl.object_id, idCol.name, 'Precision')
                               END
                             , CASE WHEN idColTypeInfo.hasSca > 0 THEN COLUMNPROPERTY(tbl.object_id, idCol.name, 'Scale')END
                             )
                      END + ')'
                  ELSE ''
             END idColType
         , delTbl.object_id delTblObjId
         , addTbl.object_id addTblObjId
         , CASE WHEN t.specifyCols <> '' THEN 1 ELSE 0 END hasSpecifyCols
INTO       #tblInfo
FROM       @tar t
INNER JOIN sys.schemas sch ON sch.name = t.schName
INNER JOIN sys.tables tbl ON tbl.schema_id = sch.schema_id AND tbl.name = t.tblName AND tbl.is_ms_shipped = 0
INNER JOIN sys.columns idCol ON idCol.object_id = tbl.object_id AND idCol.is_identity = 1
INNER JOIN sys.types idColType ON idColType.user_type_id = idCol.user_type_id
INNER JOIN @typeInfo idColTypeInfo ON idColTypeInfo.typeName = idColType.name
LEFT JOIN  sys.schemas triSch ON triSch.name = @triSchName
LEFT JOIN  sys.tables delTbl ON delTbl.schema_id = triSch.schema_id AND delTbl.name = REPLACE(@delTblNameTempl, '#', tbl.name)
LEFT JOIN  sys.tables addTbl ON addTbl.schema_id = triSch.schema_id AND addTbl.name = REPLACE(@addTblNameTempl, '#', tbl.name);

IF EXISTS (SELECT 1 FROM #tblInfo t GROUP BY t.tblObjId HAVING COUNT(1) > 1)
BEGIN
  PRINT '>> error: #tblInfo 有重复的记录';
  RETURN;
END;

IF (SELECT COUNT(1)FROM #tblInfo t) <> (SELECT COUNT(1)FROM @tar t)
BEGIN
  PRINT '>> error: #tblInfo 的记录数与 @tar 不一致';
  RETURN;
END;

-- 获取目标表的所有列信息
SELECT     tblInfo.tblObjId
         , col.is_identity isIdCol
         , col.name colName
         , UPPER(colType.name)
           + CASE WHEN colTypeInfo.hasLen > 0 OR colTypeInfo.hasPre > 0 OR colTypeInfo.hasSca > 0 THEN
                    '('
                    + CASE WHEN colTypeInfo.hasLen > 0 AND col.max_length = -1 THEN 'MAX'
                           ELSE
                             CONCAT_WS(
                               ', '
                             , CASE WHEN colTypeInfo.hasLen > 0 OR colTypeInfo.hasPre > 0 THEN COLUMNPROPERTY(tblInfo.tblObjId, col.name, 'Precision')
                               END
                             , CASE WHEN colTypeInfo.hasSca > 0 THEN COLUMNPROPERTY(tblInfo.tblObjId, col.name, 'Scale')END
                             )
                      END + ')'
                  ELSE ''
             END colType
INTO       #tblColsInfo
FROM       #tblInfo tblInfo
INNER JOIN sys.columns col ON col.object_id = tblInfo.tblObjId AND col.is_computed = 0
INNER JOIN sys.types colType ON colType.user_type_id = col.user_type_id
INNER JOIN @typeInfo colTypeInfo ON colTypeInfo.typeName = colType.name
LEFT JOIN  #specifyCols specifyCols ON specifyCols.schName = tblInfo.schName AND specifyCols.tblName = tblInfo.tblName AND specifyCols.colName = col.name
WHERE      tblInfo.hasSpecifyCols = 0 OR col.is_identity = 1 OR specifyCols.colName IS NOT NULL;

DROP TABLE #specifyCols;

-- 获取备份表（如果之前已经创建了）的所有列信息
SELECT     tblInfo.delTblObjId
         , delTblCol.name colName
         , UPPER(delTblColType.name)
           + CASE WHEN delTblColTypeInfo.hasLen > 0 OR delTblColTypeInfo.hasPre > 0 OR delTblColTypeInfo.hasSca > 0 THEN
                    '('
                    + CASE WHEN delTblColTypeInfo.hasLen > 0 AND delTblCol.max_length = -1 THEN 'MAX'
                           ELSE
                             CONCAT_WS(
                               ', '
                             , CASE WHEN delTblColTypeInfo.hasLen > 0 OR delTblColTypeInfo.hasPre > 0 THEN
                                      COLUMNPROPERTY(tblInfo.delTblObjId, delTblCol.name, 'Precision')
                               END
                             , CASE WHEN delTblColTypeInfo.hasSca > 0 THEN COLUMNPROPERTY(tblInfo.delTblObjId, delTblCol.name, 'Scale')END
                             )
                      END + ')'
                  ELSE ''
             END colType
INTO       #delTblColsInfo
FROM       #tblInfo tblInfo
INNER JOIN sys.columns delTblCol ON delTblCol.object_id = tblInfo.delTblObjId
INNER JOIN sys.types delTblColType ON delTblColType.user_type_id = delTblCol.user_type_id
INNER JOIN @typeInfo delTblColTypeInfo ON delTblColTypeInfo.typeName = delTblColType.name
WHERE      tblInfo.delTblObjId IS NOT NULL AND delTblCol.name NOT IN (@BAK_ID_COL_NAME, @BAK_OPT_ID_COL_NAME, @BAK_OPT_TYPE_COL_NAME, @BAK_DT_COL_NAME);

SELECT   'USE $DBName$--\r\n
GO--\r\n
--\r\n
' + -- add 备份表表结构、索引
  CASE WHEN t.addTblObjId IS NULL THEN
         'CREATE TABLE [' + @triSchName + '].[' + t.addTblName + '] ([' + @BAK_DT_COL_NAME + '] DATETIME, [' + t.idColName + '] ' + t.idColType
         + ')--\r\n
--\r\n
CREATE NONCLUSTERED INDEX [IX_'        + t.addTblName + '_' + t.idColName + '] ON [' + @triSchName + '].[' + t.addTblName + '] ([' + t.idColName
         + '])--\r\n
GO--\r\n
--\r\n'
       ELSE ''
  END
         -- add 触发器脚本
         + CASE WHEN NOT EXISTS (
                           SELECT 1
                           FROM   sys.sysobjects
                           WHERE  parent_obj = t.tblObjId AND xtype = 'TR' AND name = REPLACE(@addTriNameTempl, '#', t.tblName)
                         ) THEN
                  'CREATE TRIGGER [' + t.schName + '].[' + REPLACE(@addTriNameTempl, '#', t.tblName) + ']--\r\n
ON ['                                                                                                                       + t.schName + '].[' + t.tblName
                  + ']--\r\n
AFTER INSERT--\r\n
AS--\r\n
BEGIN--\r\n
  SET NOCOUNT ON--\r\n
--\r\n
  INSERT ['                                                                                                                 + @triSchName + '].['
                  + t.addTblName + '] ([' + @BAK_DT_COL_NAME + '], [' + t.idColName + '])--\r\n
  SELECT GETDATE(), ['                                                                                                      + t.idColName
                  + '] FROM [Inserted]--\r\n
END--\r\n
GO--\r\n
--\r\n'
                ELSE ''
           END
         -- del 备份表表结构、主键、索引
         + CASE WHEN t.delTblObjId IS NULL THEN
                  'CREATE TABLE [' + @triSchName + '].[' + t.delTblName + '] ([' + @BAK_ID_COL_NAME + '] BIGINT IDENTITY(1, 1), [' + @BAK_OPT_ID_COL_NAME
                  + '] UNIQUEIDENTIFIER, [' + @BAK_OPT_TYPE_COL_NAME + '] INT, [' + @BAK_DT_COL_NAME + '] DATETIME, ' + t.colsDefine + ')--\r\n
--\r\n
ALTER TABLE ['                         + @triSchName + '].[' + t.delTblName + '] ADD CONSTRAINT [PK_' + @triSchName + '_' + t.delTblName
                  + '] PRIMARY KEY CLUSTERED ([' + @BAK_ID_COL_NAME + '])--\r\n
--\r\n
CREATE NONCLUSTERED INDEX [IX_'        + t.delTblName + '_' + @BAK_DT_COL_NAME + '] ON [' + @triSchName + '].[' + t.delTblName + '] ([' + @BAK_DT_COL_NAME
                  + '] DESC) INCLUDE ([' + t.idColName + '])--\r\n
GO--\r\n
--\r\n'
                WHEN EXISTS (
                       SELECT    1
                       FROM      #tblColsInfo tblColsInfo
                       LEFT JOIN #delTblColsInfo delTblColsInfo ON delTblColsInfo.delTblObjId = t.delTblObjId AND delTblColsInfo.colName = tblColsInfo.colName
                       WHERE     tblColsInfo.tblObjId = t.tblObjId AND delTblColsInfo.delTblObjId IS NULL
                     ) THEN
                  'ALTER TABLE [' + @triSchName + '].[' + t.delTblName + '] ADD '
                  + STUFF((
                            SELECT    ', [' + tblColsInfo.colName + '] ' + tblColsInfo.colType
                            FROM      #tblColsInfo tblColsInfo
                            LEFT JOIN #delTblColsInfo delTblColsInfo ON delTblColsInfo.delTblObjId = t.delTblObjId AND delTblColsInfo.colName = tblColsInfo.colName
                            WHERE     tblColsInfo.tblObjId = t.tblObjId AND delTblColsInfo.delTblObjId IS NULL
                            ORDER BY  tblColsInfo.colName ASC
                            FOR XML PATH('')
                          )
                        , 1
                        , 2
                        , ''
                    ) + '--\r\n
GO--\r\n
--\r\n'
                ELSE ''
           END
         -- del 触发器脚本
         + CASE WHEN EXISTS (
                       SELECT 1
                       FROM   sys.sysobjects
                       WHERE  parent_obj = t.tblObjId AND xtype = 'TR' AND name = REPLACE(@delTriNameTempl, '#', t.tblName)
                     ) THEN 'ALTER'
                ELSE 'CREATE'
           END + ' TRIGGER [' + t.schName + '].[' + REPLACE(@delTriNameTempl, '#', t.tblName) + ']--\r\n
ON ['    + t.schName + '].[' + t.tblName
         + ']--\r\n
AFTER DELETE, UPDATE--\r\n
AS--\r\n
BEGIN--\r\n
  SET NOCOUNT ON--\r\n
--\r\n
  DECLARE @optType INT = CASE WHEN EXISTS (SELECT 1 FROM [Inserted]) THEN 3 ELSE 2 END--\r\n
  DECLARE @optId UNIQUEIDENTIFIER = NEWID()--\r\n
  DECLARE @now DATETIME = GETDATE()--\r\n' + CASE WHEN t.hasSpecifyCols = 1 THEN '
  DECLARE @premiseCols TABLE ([' + t.idColName + '] ' + t.idColType + ')--\r\n
--\r\n
  IF @optType = 3--\r\n
  BEGIN--\r\n
    INSERT @premiseCols ([' + t.idColName + '])--\r\n
    SELECT t.[' + t.idColName + ']--\r\n
    FROM   (SELECT ' + t.cols + '--\r\n
            FROM   Inserted--\r\n
            EXCEPT--\r\n
            SELECT ' + t.cols + '--\r\n
            FROM   Deleted) t--\r\n
  END--\r\n'
                                                  ELSE ''
                                             END + '
--\r\n
  INSERT [' + @triSchName + '].[' + t.delTblName + '] ([' + @BAK_OPT_ID_COL_NAME + '], [' + @BAK_OPT_TYPE_COL_NAME + '], [' + @BAK_DT_COL_NAME + '], ' + t.cols
         + ')--\r\n
  SELECT @optId, @optType, @now, ' + t.cols + '--\r\n
  FROM   [Deleted]--\r\n' + CASE WHEN t.hasSpecifyCols = 1 THEN '
  WHERE  @optType = 2--\r\n
         OR [' + t.idColName + '] IN (SELECT [' + t.idColName + '] FROM @premiseCols)--\r\n'
                                 ELSE ''
                            END + '
END--\r\n
GO--\r\n'
FROM     (
  SELECT t.tblObjId
       , t.schName
       , t.tblName
       , t.idColName
       , t.idColType
       , t.delTblObjId
       , REPLACE(@delTblNameTempl, '#', t.tblName) delTblName
       , t.addTblObjId
       , REPLACE(@addTblNameTempl, '#', t.tblName) addTblName
       , t.hasSpecifyCols
       , STUFF((
                 SELECT   ', [' + tblColsInfo.colName + '] ' + tblColsInfo.colType
                 FROM     #tblColsInfo tblColsInfo
                 WHERE    tblColsInfo.tblObjId = t.tblObjId
                 ORDER BY tblColsInfo.colName ASC
                 FOR XML PATH('')
               )
             , 1
             , 2
             , ''
         ) colsDefine
       , STUFF((
                 SELECT   ', [' + tblColsInfo.colName + '] '
                 FROM     #tblColsInfo tblColsInfo
                 WHERE    tblColsInfo.tblObjId = t.tblObjId
                 ORDER BY tblColsInfo.colName ASC
                 FOR XML PATH('')
               )
             , 1
             , 2
             , ''
         ) cols
  FROM   #tblInfo t
) t
ORDER BY t.schName, t.tblName;

DROP TABLE #delTblColsInfo;
DROP TABLE #tblColsInfo;
DROP TABLE #tblInfo;

2. 删除 M 表触发器脚本 ​

SQL
USE $DBName$;

DECLARE @triSchName      VARCHAR(50) = '$tri$'       --
      , @addTblNameTempl VARCHAR(50) = '$addbak_$#'  --
      , @addTriNameTempl VARCHAR(50) = '$addtri_$#'  --
      , @delTblNameTempl VARCHAR(50) = '$delbak_$#'  --
      , @delTriNameTempl VARCHAR(50) = '$deltri_$#'; --

DECLARE @tar TABLE (schName VARCHAR(500), tblName VARCHAR(500));
INSERT @tar (schName, tblName) VALUES ('$dbo$', '$TblName$');

IF EXISTS (SELECT 1 FROM @tar t WHERE t.schName = @triSchName)
BEGIN
  PRINT '>> error: @tar 中存在 schName 为 ' + @triSchName + ' 的记录';
  RETURN;
END;

SELECT     tbl.object_id objId, t.schName, t.tblName, delTbl.name delTblName, addTbl.name addTblName, delTri.name delTriName, addTri.name addTriName
INTO       #tblInfo
FROM       @tar t
INNER JOIN sys.schemas sch ON sch.name = t.schName
INNER JOIN sys.tables tbl ON tbl.schema_id = sch.schema_id AND tbl.name = t.tblName AND tbl.is_ms_shipped = 0
LEFT JOIN  sys.columns idCol ON idCol.object_id = tbl.object_id AND idCol.is_identity = 1
LEFT JOIN  sys.schemas bakSch ON bakSch.name = @triSchName
LEFT JOIN  sys.tables delTbl ON delTbl.schema_id = bakSch.schema_id AND delTbl.name = REPLACE(@delTblNameTempl, '#', tbl.name)
LEFT JOIN  sys.tables addTbl ON addTbl.schema_id = bakSch.schema_id AND addTbl.name = REPLACE(@addTblNameTempl, '#', tbl.name)
LEFT JOIN  sys.sysobjects delTri ON delTri.parent_obj = tbl.object_id AND delTri.xtype = 'TR' AND delTri.name = REPLACE(@delTriNameTempl, '#', tbl.name)
LEFT JOIN  sys.sysobjects addTri ON addTri.parent_obj = tbl.object_id AND addTri.xtype = 'TR' AND addTri.name = REPLACE(@addTriNameTempl, '#', tbl.name);

IF EXISTS (SELECT 1 FROM #tblInfo t GROUP BY t.objId HAVING COUNT(1) > 1)
BEGIN
  PRINT '>> error: #tblInfo 有重复的记录';
  RETURN;
END;

IF (SELECT COUNT(1)FROM #tblInfo t) <> (SELECT COUNT(1)FROM @tar t)
BEGIN
  PRINT '>> error: #tblInfo 的记录数与 @tar 不一致';
  RETURN;
END;

SELECT   ISNULL('DROP TRIGGER [' + t.schName + '].[' + t.addTriName + ']--\r\n
GO--\r\n
--\r\n', '') + ISNULL('DROP TRIGGER [' + t.schName + '].[' + t.delTriName + ']--\r\n
GO--\r\n
--\r\n', '') + ISNULL('DROP TABLE [' + @triSchName + '].[' + t.addTblName + ']--\r\n
GO--\r\n
--\r\n', '') + ISNULL('DROP TABLE [' + @triSchName + '].[' + t.delTblName + ']--\r\n
GO--\r\n
--\r\n', '')
FROM     #tblInfo t
ORDER BY t.tblName;

DROP TABLE #tblInfo;

3. 根据 M 表追溯历史数据 ​

SQL
DECLARE @traceDT DATETIME = $TarDT$;

SELECT    t.$PKColName$, $Cols$
FROM      (
  SELECT ROW_NUMBER() OVER (PARTITION BY t.$PKColName$ ORDER BY t.$BAK_DT_COL_NAME$ ASC) rn, t.*
  FROM   (
    SELECT $BAK_DT_COL_NAME$, $PKColName$, $Cols$
    FROM   $DBName$.$tri$.$DelBakTblName$ WITH (NOLOCK)
    WHERE  $BAK_ID_COL_NAME$ IN (
             SELECT t.$BAK_ID_COL_NAME$
             FROM   (
               SELECT ROW_NUMBER() OVER (PARTITION BY $PKColName$ ORDER BY $BAK_DT_COL_NAME$ ASC, $BAK_ID_COL_NAME$ ASC) rn, $BAK_ID_COL_NAME$
               FROM   $DBName$.$tri$.$DelBakTblName$ WITH (NOLOCK)
               WHERE  $BAK_DT_COL_NAME$ > @traceDT
             ) t
             WHERE  t.rn = 1
           )
    UNION ALL
    SELECT GETDATE(), $PKColName$, $Cols$
    FROM   $DBName$.$dbo$.$OrgTblName$ WITH (NOLOCK)
  ) t
) t
LEFT JOIN (SELECT ins.$PKColName$ FROM $DBName$.$tri$.$AddBakTblName$ ins WITH (NOLOCK) WHERE ins.$BAK_DT_COL_NAME$ > @traceDT) ins ON ins.$PKColName$ = t.$PKColName$
WHERE     t.rn = 1 AND ins.$PKColName$ IS NULL;

转载请注明作者和出处

Copyright © 2025-至今 Tenormis