הפורום נסגר!

במקומו נפתח פורום חדש, טוב יותר בהרבה מובנים. >>>>
אתם מוזמנים להצטרף אליו, בואו!

ההצטרפות לפורום התכנות החדש כרוכה בתשלום חודשי סמלי.

טריגר לשמירת הסטוריה ב MS-SQL

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

טריגר לשמירת הסטוריה ב MS-SQL

הודעהעל ידי softs » 25 אוגוסט 2014, 16:25

קודם כל אקדים ואומר שזה מתבסס על הנחת יסוד שיש לך בטבלה שדה אחד שהוא ה 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)
softs
משתמש וותיק
 
הודעות: 282
הצטרף: 22 יוני 2014, 16:43
שלח תודה: 25 פעמים
קיבל תודה: 444 פעמים

Re: טריגר לשמירת הסטוריה ב MS-SQL

הודעהעל ידי דוד ל.ט. » 26 אוגוסט 2014, 11:54

אלמלא החופש ושיממון הפורום היו פה נראה לי מחיאות כפיים סוערות...
תודה!
הלואי שהייתי מצליח לכתוב כזה קוד.
חזק.
סמל אישי של המשתמש
דוד ל.ט.
משתמש וותיק
 
הודעות: 2380
הצטרף: 12 יוני 2013, 11:02
שלח תודה: 1238 פעמים
קיבל תודה: 2019 פעמים

Re: טריגר לשמירת הסטוריה ב MS-SQL

הודעהעל ידי softs » 26 אוגוסט 2014, 13:01

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

האמת שזה חלק מתוך פרויקט שאני עובד עליו של DAL ל WINFORMS
אני מקווה שבקרוב אוכל לשחרר עוד
softs
משתמש וותיק
 
הודעות: 282
הצטרף: 22 יוני 2014, 16:43
שלח תודה: 25 פעמים
קיבל תודה: 444 פעמים

Re: טריגר לשמירת הסטוריה ב MS-SQL

הודעהעל ידי ארכיטקט » 28 אוגוסט 2014, 14:50

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

Re: טריגר לשמירת הסטוריה ב MS-SQL

הודעהעל ידי softs » 28 אוגוסט 2014, 15:20

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


זה לא טבלת ראי . . .
softs
משתמש וותיק
 
הודעות: 282
הצטרף: 22 יוני 2014, 16:43
שלח תודה: 25 פעמים
קיבל תודה: 444 פעמים

Re: טריגר לשמירת הסטוריה ב MS-SQL

הודעהעל ידי ארכיטקט » 29 אוגוסט 2014, 09:52

סליחה צודק, השאלה למה החלטת לעשות טבלה לכל טבלה, אפשר טבלה אחת ענקית, ועמודה של שם הטבלה לא???
יש גבול לכמות המציאות שאדם יכול לשאת - גוזף קונרד.
זמן זה הדבר שאותו אנחנו מודדים באמצעות שעון - אלברט איינשטיין.
ארכיטקט
משתמש וותיק
 
הודעות: 1001
הצטרף: 20 יוני 2013, 12:48
שלח תודה: 373 פעמים
קיבל תודה: 634 פעמים

Re: טריגר לשמירת הסטוריה ב MS-SQL

הודעהעל ידי softs » 30 אוגוסט 2014, 23:58

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

נכון, לפעמים אני עושה כך ולפעמים כך הכל לפי משתני הסביבה
softs
משתמש וותיק
 
הודעות: 282
הצטרף: 22 יוני 2014, 16:43
שלח תודה: 25 פעמים
קיבל תודה: 444 פעמים


חזור אל שיתופיה

מי מחובר

משתמשים הגולשים בפורום זה: all bots ו 0 אורחים