en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
פבר20

Written by: ronen ariely
20/02/2013 20:05 RssIcon

תקציר

COLUMNS_UPDATED היא פונקציה קטנה מובנית בשרתי SQL שנועדה לעבודה עם טריגרים (trigger). פונקציה זו מאפשרת לנו לזהות איזה טורים עודכנו או הוכנסו לטבלה (או לטבלה הוירטואלית VIEW).

העבודה עם הפונקציה קלה מאוד, אבל הנתון שהיא מחזירה מחייב פיענוח וזה החלק המעניין. הפונקציה מחזירה ערך בודד מסוג varbinary הכולל מספר בוד המחושב בצורה כזו שייתן לנו נתון חד-חד-ערכי של כל מספרי הטורים שעודכנו או הוכנסו.

כיצד נתון בודד מגדיר לנו בצורה חד-חד-ערכית מספר רב של נתונים?

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

הטורים בכל טבלה נשמרים בסדר קבוע על פי סדר היצירה שלהם. אם כי בפועל ביום יום אנחנו לא עושים שימוש במיקום הטור בסדר הטורים בטבלה, אלא אנחנו עושים שימו בשם הטור הרי שעדין המיקומים הם קבועים. נתוני הטורים בכל טבלה נשמרים בטבלת המערכת INFORMATION_SCHEMA.COLUMNS. אם נביט בטבלה זו נוכל למצוא טור בשם ORDINAL_POSITION. טור זה מכיל את המיקום הפיזי של הטור ברשימת הטורים בטבלה.

למשל בטבלה עם טורים ID,NAME כאשר טור ID נבנה לפני טור NAME בזמן הקמת הטבלה אז טור ID יקבל את הערך 1 וטור NAME יקבל את הערך 2.

פונקציית COLUMNS_UPDATED מחזירה לנו כאמור ערך בודד. ערך זה הוא למעשה ערך מספרי בודד שנקבע על פי החישוב של ערך בינארי, כאשר המספר מייצג בעזרת אפס טורים שאין בהם שימוש ובעזרת 1 את הטורים שיש בהם שימוש. כך למשל למשל המספר הבינארי 100110 (נעבור עליו מימין לשמאל) מייצג שהיה שימוש בטורים 2,3,6

חישוב מתמטי:

עבור כל טור בו נעשה שינוי נחשב את התוצאה של 2 בחזקת (מיקום הטור פחות 1)
את כל החישובים נסכם ביחד ונקבל את הערך המספרי שמחזירה לנו הפונקציה COLUMNS_UPDATED.

לדוגמה: אם הפונקציה אמורה להחזיר לנו את טורים 2,3,6 אז נקבל את החישוב הבא:

חישוב זה הוא חד-חד-ערכי, ז"א עבור כל תוצאה יש רק צירוף מסויים של מספרי טורים שיכלו להוביל לתוצאה זו והפוך... לכל צירוף של טורים יש רק תוצאה אחת שנקבל.

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

פיענוח התוצאות החוזרות מהפונקציה COLUMNS_UPDATED

ניתן לבצע את הפיענוח בצורה ישירה בהתאם להסבר מעל. עם זה אני מוצא שדרך יותר יעילה היא לעבוד בשיטה הבאה: על מנת לפענח את הנתון החוזר מהפונקציה נעזר בפונקציה מובנית בשם sys.fn_IsBitSetInBitmask שמטרתה לבדוק האם ערך מסויים של מספר טור חזר בפרמטר שלנו או לא (כך אני חוסך את החישוב המתמטי לבד או את הההמרה בין בינארי למספר).

נכתוב פונקציה קטנה אשר תציג לנו את כל הטורים בטבלה אשר הערך של fn_IsBitSetInBitmas הוא שונה מאפס (ז"א הטור היה בין הנתונים שחזרו בפרמטר שלנו)

הגיע הזמן לקוד :-)

פונקצייה העזר לפיענוח הנתון החוזר:

USE [AdventureWorks2012]
GO
/****** Object:  UserDefinedFunction [dbo].[udf_DecodeBitmask]    Script Date: 20/02/2013 20:36:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_DecodeBitmask](
    @Tablename      VARCHAR(100),
    @ColumnsUpdated VARBINARY(255)
)
RETURNS TABLE AS RETURN
SELECT  TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION
FROM    INFORMATION_SCHEMA.COLUMNS Field
WHERE   TABLE_NAME = @Tablename AND
        sys.fn_IsBitSetInBitmask(
        @ColumnsUpdated,
        COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'ColumnID')
        ) <> 0


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

עתה נוכל לבנות טריגר שעושה שימוש בתוצאות של פונקציה זו:

USE AdventureWorks2012;
GO
CREATE TRIGGER Person.uContact2 ON Person.Person
AFTER UPDATE AS
    SELECT * FROM dbo.udf_DecodeBitmask('Person', COLUMNS_UPDATED())
GO


נריץ עדכון פשוט על הטבלה ונראה את התוצאות שמגיעות מהטריגר שלנו:

UPDATE Person.Person
   SET NameStyle = NameStyle,
      FirstName=FirstName,
      EmailPromotion=EmailPromotion;
GO


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

* במצב מציאותי יותר בטריגר שלנו לא נבצע פעולת SELECT כמובן אלא פעולה כלשהיא התלויה בטורים שבוצעו בהם שינוי. למשל נוכל להכניס תנאי IF ולבדוק אם נעשה שינוי בטור מסויים, או שנוכל לבצע פעולה של שמירה שטבלת לוג את השעה הנוכחית ואת שות הטורים שעודכנו... מכאן הכל פתוח לצרכים

מקווה שזה נותן על קצה המזלג את האפשרויות הרבות שפונקציית COLUMNS_UPDATED פותחת לנו :-)

נספחים

* בבלוג הזכרנו את הפונקציה המובנית fn_IsBitSetInBitmask בה אנחנו נעזרים על מנת לפענח את התוצאה החוזרת מהפרמטר של COLUMNS_UPDATED. אם רוצים מידע נוסף על הפונקציה המובנית ניתן להעזר בפרוצדורה sp_helptext

exec sp_helptext 'fn_IsBitSetInBitmask'

* דרך נוספת לניהול שינויים בטבלאות היא עבודה באמצעות Change Data Capture. ועל כך בבלוג אחר כבר, אבל בינתיים ניתן להציץ כאן

* המרה בין מספרים בינאריים למספרים עשרוניים ניתן לבצע בקישור הבא

* דוגמאות נוספות ניתן למצוא בקישור הבא:

http://msdn.microsoft.com/en-us/library/ms186329.aspx