קודם כל אקדים ואומר שזה מתבסס על הנחת יסוד שיש לך בטבלה שדה אחד שהוא ה 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)