שמירת היסטורית שינויים ב SQL

[softs]

שכלול של קטע שפרסמתי בעבר, זה פרוצדורה שיוצרת טבלה באותו שם עם תחילית H_ וגם טריגר ששומר כל פעולה של שינוי דאטה.
השכלול העיקרי הוא שהוא יודע כעת להתמודד עם טבלאות עם כמה עמודות PK וגם עם טבלאות בלי PK ואם אין בכלל PK אז לוקח IDENTITY (אם יש).
על מנת להפעיל שמירת היסטוריה על טבלה מריצים את הפרוצדורה עם פרמטר של שם הטבלה, אחרי שינוי במבנה הטבלה יש להריץ שוב.

CREATE PROC [dbo].[CreateTableTriggers] (@Table NVARCHAR(MAX)) AS
    
    SET NOCOUNT ON
     
    DECLARE @CName AS NVARCHAR(MAX)
 
    DECLARE @TName AS NVARCHAR(MAX)
 
    DECLARE @TCol AS TABLE(name NVARCHAR(MAX), typ NVARCHAR(max))
 
    INSERT INTO @TCol
    Select C.name,T.name from sys.columns C inner join sys.types T ON T.user_type_id = C.user_type_id where Object_id(@Table) = object_id AND C.user_type_id NOT IN (34,165,173)
     
    DECLARE @DKey AS NVARCHAR(MAX) 
	DECLARE @IKey AS NVARCHAR(MAX) 
 
	DECLARE @TKey AS TABLE(name NVARCHAR(MAX), sort int)
 	
	INSERT INTO @TKey
	SELECT  COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1 AND TABLE_NAME = @Table AND TABLE_SCHEMA = 'dbo'
	
	SET @DKey = (SELECT stuff((select ' + ''_'' + CAST(D.' + name + ' AS NVARCHAR(4000))' as [text()] FROM @TKey ORDER BY sort for xml path('')), 1, 9, ''))

	SET @IKey = (SELECT stuff((select ' + ''_'' + CAST(I.' + name + ' AS NVARCHAR(4000))' as [text()] FROM @TKey ORDER BY sort for xml path('')), 1, 9, ''))

	IF(@DKey IS NULL OR @IKey IS NULL) 
	BEGIN

		SET @DKey = (SELECT TOP 1 'CAST(D.' + name + ' AS NVARCHAR(4000))' FROM sys.columns where Object_id(@Table) = object_id and is_identity = 1)
		SET @IKey =	(SELECT TOP 1 'CAST(I.' + name + ' AS NVARCHAR(4000))' FROM sys.columns where Object_id(@Table) = object_id and is_identity = 1)	

	END

	IF(@DKey IS NULL OR @IKey IS NULL) RAISERROR ('No Key or Identity column found.',16,1);
	

    DECLARE @sSQL AS NVARCHAR(MAX) = 
    '
    CREATE TRIGGER {Table}History ON {Table} AFTER UPDATE, INSERT, DELETE AS
 
    SET NOCOUNT ON
    INSERT INTO H_{Table}
    SELECT GETDATE(),APP_NAME(),{I.KEY},''Object'',{I.KEY},'''', ''I'' FROM INSERTED I LEFT JOIN DELETED D ON {D.KEY} = {I.KEY} WHERE {D.KEY} IS NULL
    UNION
    SELECT GETDATE(),APP_NAME(),{D.KEY},''Object'',{D.KEY},'''', ''D'' FROM DELETED D LEFT JOIN INSERTED I ON {D.KEY} = {I.KEY} WHERE {I.KEY} IS NULL
    '
  
    DECLARE col_cur CURSOR FOR SELECT name, typ FROM @TCol    
    OPEN col_cur
     
    FETCH NEXT FROM col_cur INTO @CName,@TName
         
    WHILE @@FETCH_STATUS = 0
    BEGIN
         
        DECLARE @Compare AS NVARCHAR(MAX)
          
        IF (@TName IN ('int','decimal','numeric','bigint'))
            SET @Compare = 'ISNULL(D.{Col},0) <> ISNULL(I.{Col},0)'
        ELSE
            SET @Compare = 'ISNULL(D.{Col},'''') <> ISNULL(I.{Col},'''')'
 
        SET @sSQL +=
        REPLACE('
        INSERT INTO H_{Table}
        SELECT GETDATE(),APP_NAME(),{I.KEY},' + '''{Col}''' + ',D.{Col},I.{Col}, ''U'' FROM DELETED D INNER JOIN INSERTED I ON {D.KEY} = {I.KEY} WHERE ' + @Compare + ' 
        ','{Col}',@CName)
         
        FETCH NEXT FROM col_cur INTO @CName,@TName
        END
 
    CLOSE col_cur
    DEALLOCATE col_cur
     
    DECLARE @sDropSQL AS NVARCHAR(MAX) = REPLACE('IF OBJECT_ID (''{Table}History'',''TR'') IS NOT NULL
        DROP TRIGGER {Table}History','{Table}',@Table)
 
    EXEC(@sDropSQL)
 
    DECLARE @sTableSQL AS NVARCHAR(MAX) = REPLACE('
        IF OBJECT_ID (''H_{Table}'') IS NULL
        CREATE TABLE H_{Table}
        (           
            ChangeDate DATETIME NULL,
			ApplicationName NVARCHAR(4000),
            [Key] NVARCHAR(MAX),
            ColumnName NVARCHAR(100) NULL,
            OldValue NVARCHAR(MAX) NULL,
            NewValue NVARCHAR (MAX) NULL,
            Action NVARCHAR (1)
        )
        ','{Table}',@Table)
 
    EXEC(@sTableSQL)
 
    SET @sSQL = REPLACE(@sSQL,'{Table}',@Table)
    SET @sSQL = REPLACE(@sSQL,'{D.KEY}',@DKey)
    SET @sSQL = REPLACE(@sSQL,'{I.KEY}',@IKey)
     

    EXEC(@sSql)

פורסם במקור בפורום CODE613 ב29/11/2015 13:17 (+02:00)