טריגר לשמירת הסטוריה ב MS-SQL
-
קודם כל אקדים ואומר שזה מתבסס על הנחת יסוד שיש לך בטבלה שדה אחד שהוא ה KEY והוא מוגדר כ IDENTITY
כמובן שמי שעובד אחרת יכול להתאים את הקוד לשיטה שבה הוא עובדמטרת הקוד היא ליצור טיפול בשמירת הסטוריה ברמת ה DB ולהשאיר את הקוד "נקי" מהכאב ראש הזה
(כמובן שלמי שיש SQL ENTERPRISE יש CDC ומומלץ להשתמש בו)מה שהקוד הזה למעשה עושה - הוא יוצר פרוצדורה שאפשר לקרוא לה ולתת שם טבלה כפרמטר
הפרוצדורה יוצרת טריגר וטבלה עם קידומת H_ עבור הטבלה המקורית ושומרת הסטורית שינויים בטבלת ה H_אם שיניתם את מבנה הטבלה - הוספתם / הורדתם / עדכנתם עמודה פשוט קוראים שוב לפרוצדורה והיא מעדכנת את הטריגר
CREATE PROC 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 <> 165 DECLARE @Key AS NVARCHAR(MAX) = (SELECT TOP 1 name FROM sys.columns where Object_id(@Table) = object_id and is_identity = 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(),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(),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(),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, [Key] INT, 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,'{KEY}',@Key) EXEC(@sSql)פורסם במקור בפורום CODE613 ב25/08/2014 17:25 (+03:00)
-
