CODE613M

קודם כל אקדים ואומר שזה מתבסס על הנחת יסוד שיש לך בטבלה שדה אחד שהוא ה KEY והוא מוגדר כ IDENTITY
כמובן שמי שעובד אחרת יכול להתאים את הקוד לשיטה שבה הוא עובד

מטרת הקוד היא ליצור טיפול בשמירת הסטוריה ברמת ה DB ולהשאיר את הקוד "נקי" מהכאב ראש הזה
(כמובן שלמי שיש SQL ENTERPRISE יש CDC ומומלץ להשתמש בו)

מה שהקוד הזה למעשה עושה - הוא יוצר פרוצדורה שאפשר לקרוא לה ולתת שם טבלה כפרמטר
הפרוצדורה יוצרת טריגר וטבלה עם קידומת H_ עבור הטבלה המקורית ושומרת הסטורית שינויים בטבלת ה H_

אם שיניתם את מבנה הטבלה - הוספתם / הורדתם / עדכנתם עמודה פשוט קוראים שוב לפרוצדורה והיא מעדכנת את הטריגר

sql code

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)

אלמלא החופש ושיממון הפורום היו פה נראה לי מחיאות כפיים סוערות...
תודה!
הלואי שהייתי מצליח לכתוב כזה קוד.
חזק.

תודה על המחמאות

האמת שזה חלק מתוך פרויקט שאני עובד עליו של DAL ל WINFORMS
אני מקווה שבקרוב אוכל לשחרר עוד

קוד נהדר, יישר כוח וכל הכבוד!!!
אני אמנם אוהב צורות אחרות של שמירה בצורת key value ולא בצורת טבלת ראי. אבל זה ענין של טעם, לגופו של קוד אני מוכרח לומר "תיסלם על השיתוף".

ארכיטקט כתב:קוד נהדר, יישר כוח וכל הכבוד!!!
אני אמנם אוהב צורות אחרות של שמירה בצורת key value ולא בצורת טבלת ראי. אבל זה ענין של טעם, לגופו של קוד אני מוכרח לומר "תיסלם על השיתוף".

זה לא טבלת ראי . . .

סליחה צודק, השאלה למה החלטת לעשות טבלה לכל טבלה, אפשר טבלה אחת ענקית, ועמודה של שם הטבלה לא???

ארכיטקט כתב:אפשר טבלה אחת ענקית, ועמודה של שם הטבלה לא???

נכון, לפעמים אני עושה כך ולפעמים כך הכל לפי משתני הסביבה