דילוג לתוכן
  • דף הבית
  • קטגוריות
  • פוסטים אחרונים
  • משתמשים
  • חיפוש
  • חוקי הפורום
כיווץ
תחומים

תחומים - פורום חרדי מקצועי

💡 רוצה לזכור קריאת שמע בזמן? לחץ כאן!
  1. דף הבית
  2. תכנות
  3. ארכיון code613m
  4. טריגר לשמירת הסטוריה ב MS-SQL

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

מתוזמן נעוץ נעול הועבר ארכיון code613m
7 פוסטים 3 כותבים 552 צפיות
  • מהישן לחדש
  • מהחדש לישן
  • הכי הרבה הצבעות
התחברו כדי לפרסם תגובה
נושא זה נמחק. רק משתמשים עם הרשאות מתאימות יוכלו לצפות בו.
  • S מנותק
    S מנותק
    softs
    כתב ב נערך לאחרונה על ידי
    #1

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

    תגובה 1 תגובה אחרונה
    3
    • dovidD מנותק
      dovidD מנותק
      dovid ניהול
      כתב ב נערך לאחרונה על ידי
      #2

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

      פורסם במקור בפורום CODE613 ב26/08/2014 12:54 (+03:00)

      מנטור אישי למתכנתים (ולא רק) – להתקדם לשלב הבא!

      בכל נושא אפשר ליצור קשר dovid@tchumim.com

      תגובה 1 תגובה אחרונה
      0
      • S מנותק
        S מנותק
        softs
        כתב ב נערך לאחרונה על ידי
        #3

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

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

        פורסם במקור בפורום CODE613 ב26/08/2014 14:01 (+03:00)

        תגובה 1 תגובה אחרונה
        1
        • א מנותק
          א מנותק
          ארכיטקט
          כתב ב נערך לאחרונה על ידי
          #4

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

          פורסם במקור בפורום CODE613 ב28/08/2014 15:50 (+03:00)

          אידיאולוגיות גדולות נכנסות למטחנה של עולם המעשה ויוצאות ממנו בפירורים (ווינסטון צ'רצ'יל, סתם סתם, זה שלי)

          תגובה 1 תגובה אחרונה
          0
          • S מנותק
            S מנותק
            softs
            כתב ב נערך לאחרונה על ידי
            #5

            @ארכיטקט

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

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

            פורסם במקור בפורום CODE613 ב28/08/2014 16:20 (+03:00)

            תגובה 1 תגובה אחרונה
            0
            • א מנותק
              א מנותק
              ארכיטקט
              כתב ב נערך לאחרונה על ידי
              #6

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

              פורסם במקור בפורום CODE613 ב29/08/2014 10:52 (+03:00)

              אידיאולוגיות גדולות נכנסות למטחנה של עולם המעשה ויוצאות ממנו בפירורים (ווינסטון צ'רצ'יל, סתם סתם, זה שלי)

              תגובה 1 תגובה אחרונה
              0
              • S מנותק
                S מנותק
                softs
                כתב ב נערך לאחרונה על ידי
                #7

                @ארכיטקט

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

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

                פורסם במקור בפורום CODE613 ב31/08/2014 00:58 (+03:00)

                תגובה 1 תגובה אחרונה
                1

                בא תתחבר לדף היומי!
                • התחברות

                • אין לך חשבון עדיין? הרשמה

                • התחברו או הירשמו כדי לחפש.
                • פוסט ראשון
                  פוסט אחרון
                0
                • דף הבית
                • קטגוריות
                • פוסטים אחרונים
                • משתמשים
                • חיפוש
                • חוקי הפורום