טריגר לשמירת הסטוריה ב 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)
שלום! נראה שהשיחה הזו מעניינת אותך, אבל עדיין אין לך חשבון.
נמאס לכם לגלול בין אותם הפוסטים בכל ביקור? כשנרשמים לחשבון, תמיד תחזרו בדיוק למקום שבו הייתם קודם, ותוכלו לבחור לקבל התראות על תגובות חדשות (בין אם במייל, ובין אם בהתראת פוש). תוכלו גם לשמור סימניות ולפרגן ב-upvote לפוסטים כדי להביע הערכה לחברי קהילה אחרים.
בעזרת התרומה שלך, הפוסט הזה יכול להיות אפילו טוב יותר 💗
הרשמה התחברות