Instead of Triggers

הפוסט שלהלן עוסק ב-Instead of Triggers, מציג דוגמה שימושית, ומטפל במקרי קצה מעניינים ולא שיגרתיים שיכולים להיות שימושים ללא קשר לטריגרים;

כך שגם מי שמכיר את הושא על בוריו - יש סיכוי שירוויח כמה טיפים..

ובכן: Instead of Trigger הוא DML Trigger, כלומר - טריגר על טבלה, שמתבצע לפני ובמקום פקודות Insert/ Update / Delete. כלומר: במקום Before Trigger (לא קיים ב-SQL Server) שמתבצע לפני פקודות ה-DML, הפקודות כברירת מחדל אינן מתבצעות, אבל ניתן לבצע אותן או אחרות מתוך הטריגר.

דוגמה - ניצור טבלה ונאכלס אותה במספר נתונים:

Use tempdb;

Drop Table If Exists MyTbl;

Create Table MyTbl

(ID Int Identity Primary Key Clustered,

ColI Int Null,

ColV Varchar(10) Null,

ColD DateTime Null);

Insert

Into MyTbl(ColI,ColV,ColD)

Values (1,'A','20000101'),

(Null,Null,Null),

(3,'B',Null),

(4,'C','20010131'),

(Null,'D','20020215'),

(Null,'E','20020303');

Select * From MyTbl;

Go

ה-Instead of Trigger כמו אחיו הידוע After Trigger (הטריגר הרגיל), יש לו גישה לשני סטים של נתונים במהלך הריצה: האחד נקרא Deleted והוא הסט של השורות שהיו אמורות להשתנות לפני השינוי, והשני נקרא Inserted והוא הסט של השורות שהיו אמורות להשתנות לאחר השינוי;

כאשר במקרה זה שום שינוי אינו קורה כי מדובר ב...Instead of Trigger.

ניצור איפוא טריגר כנ"ל רק כדי לראות את שני הסטים:

Create Trigger Tr_MyTbl

On MyTbl

Instead Of Update

As

Select 'The Inserted set', * From Inserted;

Select 'The Deleted set', * From Deleted;

וכעת נריץ פקודת עדכון פשוטה שלא תתבצע (Instead of Trigger ...), אבל נוכל לראות את הנתונים לפני ואחרי השינוי המנוטרל:

Update MyTbl

Set ColD=GetDate()

Where ID<=2;

Select * From MyTbl;

שני הסטים הראשונים מוצגים על ידי הטריגר - ה-Inserted וה-Deleted, וניתן לראות כיצד אמורים היו להיות הנתונים לפני ואחרי; והסט השלישי הוא פקודת Select שלי מהטבלה כדי להראות שלא חל בה כל שינוי, בשל הטריגר.

עד כאן דוגמה טכנית כדי להבין מה הטריגר הנ"ל עושה או יכול לעשות, ומכאן לדוגמה קצת יותר שימושית: אנחנו מעוניינים לבצע אך ורק פקודות בהן חל שינוי באחת משתי העמודות - ColI & ColV, כלומר - רק כשאחת משתי העמודות השתנתה הפקודה תתבצע, ואם שתיהן או אף לא אחת השתנתה - שהפקודה לא תתבצע.

לכאורה משימה פשוטה:

Create Or Alter Trigger Tr_MyTbl

On MyTbl

Instead Of Update

As

Update T

Set ColI=I.ColI,

ColV=I.ColV,

ColD=I.ColD

From MyTbl T

Inner Join Inserted I

On T.ID=I.ID

Inner Join Deleted D

On T.ID=D.ID

Where (I.ColI<>D.ColI And I.ColV=D.ColV)

Or (I.ColI=D.ColI And I.ColV<>D.ColV);

אנחנו מריצים את פקודת עדכון בעזרת הנתונים "המעודכנים" שב-Inserted כאשר קורה אחת משתיים: או שהנתונים ב-ColI השתנו מה-Deleted ל-Inserted ואלה שב-ColV לא, או להיפך.

נריץ מספר פקודות Update ו"נעטוף" אותן בטרנזקציה עם Rollback בסוף כדי שגם אם הנתונים ישתנו - שיחזרו לקדמותם עבור הדוגמאות הבאות; אבל כדי שנספיק לראות את השינויים - נריץ שתי פקודות Select מהטבלה - אחת לפני ה-Updates והשנייה אחרי ה-Updates ולפני ה-Rollback:

Begin Tran

Select * From MyTbl;

Update MyTbl

Set ColV='A2'

Where ID=1;

Update MyTbl

Set ColD=GetDate()

Where ID=3;

Update MyTbl

Set ColI+=10,

ColV='C2',

ColD=GetDate()

Where ID=4;

Update MyTbl

Set ColI=10,

ColD=GetDate()

Where ID=6;

Select * From MyTbl;

RollBack

שורה 1 השתנתה כי רק עמודה ColV השתנתה. שורה 3 לא השתנתה כי אף לא אחת מעמודות ColI & ColV השתנתה.

שורה 4 לא השתנתה כי גם ColI וגם ColV השתנו (והתנאי הוא שרק אחת מהן תשתנה). שורה 6 לא השתנתה.. למה לא? הרי בפקודה עדכנו את ColI ולא את ColV.. היכן הטעות?

הטעות היא בתנאי I.ColI<>D.ColI שבטריגר: הערך שבתא השתנה מ-Null ל-10, ושני "ערכים" אלו אינם שונים אבל גם אינם זהים, פשוט מפני ש-Null אינו ערך.. הבדיקה בטריגר הייתה צריכה להיות יותר מורכבת: או שהם שונים, או שאחד Null והשני לא, או שהשני Null והראשון לא (ובאופן דומה לגבי עמודת ColV למקרה ששני הערכים הם Null ועד כמה שישמע מוזר - Null אינו שווה ל-Null וגם לא שונה).

אם כך אנחנו נאנחים ומשכתבים את הטריגר כדלקמן (לקחת נשימה עמוקה - זה הולך להיות ארוך..):

Create Or Alter Trigger Tr_MyTbl

On MyTbl

Instead Of Update

As

Update T

Set ColI=I.ColI,

ColV=I.ColV,

ColD=I.ColD

From MyTbl T

Inner Join Inserted I

On T.ID=I.ID

Inner Join Deleted D

On T.ID=D.ID

Where ((I.ColI<>D.ColI Or (I.ColI Is Null And D.ColI Is Not Null) Or (I.ColI Is Not Null And D.ColI Is Null))

And (I.ColV=D.ColV Or (I.ColV Is Null And D.ColV Is Null)))

Or ((I.ColV<>D.ColV Or (I.ColV Is Null And D.ColV Is Not Null) Or (I.ColV Is Not Null And D.ColV Is Null))

And (I.ColI=D.ColI Or (I.ColI Is Null And D.ColI Is Null)));

מה נהיה פה? לבדוק את כל האפשרויות, כולל Null פה ושם לא ולהיפך וכך גם בעמודה השנייה, יוצר המון אפשרויות שצריך לכסות את כולן.. וזה עובד? נריץ את פקודות העדכון הקודמות עם הטרנזקציה וה-Rollback שוב:

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

פתרון אפשרי לכך הוא להשתמש ב-Union: פעולת Union בניגוד ל-Union All מבצעת Distinct, וכך משני ערכים זהים נותר אחד, משני Nulls נשאר אחד, ומשני ערכים שונים - נשארים שניים (גם אם אחד מהם Null), ולהלן הצעת הגשה:

Create Or Alter Trigger Tr_MyTbl

On MyTbl

Instead Of Update

As

Update T

Set ColI=I.ColI,

ColV=I.ColV,

ColD=I.ColD

From MyTbl T

Inner Join Inserted I

On T.ID=I.ID

Inner Join Deleted D

On T.ID=D.ID

Where (Select Count(*) From (Select I.ColI X Union Select D.ColI X) T)

+(Select Count(*) From (Select I.ColV X Union Select D.ColV X) T)

=3;

כלומר - אחנו נעדכן את הטבלה אם ישארו לנו 3 ערכים בסה"כ - שניים ב-Union עמודה אחת, ואחד ב-Union של האחרת; ובאופן דומה אם היו לנו 5 עמודות שמתוכן רק אחת צריכה להתעדכן.

בדיקה של הקוד עם גרסה זו של הטריגר מראה שהוא תקין.

עד כאן פילטרנו בעזרת הטריגר את פקודות ה-Update וקבענו מה לבצע ומה לא, אלא שיש בדעתנו לעדכן בנוסף טבלת לוג לגבי השינויים בטבלת MyTbl, ולכן קודם כל ניצור אותה (שוב- הצעת הגשה):

Drop Table If Exists MyTblLog;

Create Table MyTblLog

(ID Int Not Null,

ColI Int Null,

ColV Varchar(10) Null,

ColD DateTime Null,

ChangeDate DateTime Constraint [DF_T_TraceTable_Time] Default (GETDATE()),

HostName sysname Constraint DF_T_TraceTable_HostName Default (HOST_NAME()),

SUserName sysname Constraint DF_T_TraceTable_SUserName Default (SUSER_NAME()),

AppName sysname Constraint DF_T_TraceTable_AppName Default (APP_NAME()),

ObjectName sysname Constraint DF_T_TraceTable_Objectname Default (OBJECT_NAME(@@procid)));

הסבר: 4 העמודות הראשונות הן כמו ב-MyTbl ושומרות את הערכים של השורה שהתעדכנה בפועל. 5 העמודות הבאות מתעדות את שעת השינוי, המחשב או השרת ממנו הפקודה הורצה, המשתמש שהריץ, האפליקציה ממנה התבצע השינוי, והפרוצדורה (או הטריגר) שביצע את השינוי.

וכעת נוסיף לטריגר פקודת Insert לטבלת הלוג:

Create Or Alter Trigger Tr_MyTbl

On MyTbl

Instead Of Update

As

Update T

Set ColI=I.ColI,

ColV=I.ColV,

ColD=I.ColD

From MyTbl T

Inner Join Inserted I

On T.ID=I.ID

Inner Join Deleted D

On T.ID=D.ID

Where (Select Count(*) From (Select I.ColI X Union Select D.ColI X) T)

+(Select Count(*) From (Select I.ColV X Union Select D.ColV X) T)

=3;

Insert

Into MyTblLog(ID,ColI,ColV,ColD)

Select I.ID,

I.ColI,

I.ColV,

I.ColD

From Inserted I

Inner Join Deleted D

On I.ID=D.ID

Where (Select Count(*) From (Select I.ColI X Union Select D.ColI X) T)

+(Select Count(*) From (Select I.ColV X Union Select D.ColV X) T)

=3;

פקודת ה-Insert פונה רק ל-Inserted ול-Deleted, שולפת שוב את מה שמתאים ומכניסה לטבלת הלוג.

נריץ שוב את פקודות ה-Update הנ"ל, אבל הפעם נשלוף לפני ואחרי מטבלת הלוג MyTblLog כדי לראות מה נכנס אליה:

Begin Tran

Select * From MyTblLog;

Update MyTbl

Set ColV='A2'

Where ID=1;

Update MyTbl

Set ColD=GetDate()

Where ID=3;

Update MyTbl

Set ColI+=10,

ColV='C2',

ColD=GetDate()

Where ID=4;

Update MyTbl

Set ColI=10,

ColD=GetDate()

Where ID=6;

Select * From MyTblLog;

RollBack

ואפשר לראות ששתי שורות התווספו לטבלת הלוג כנגד שתי השורות שהתעדכנו בפועל ב-MyTbl, וכן את הפרטים השונים לגבי שעת ההרצה, מי הריץ (אני.. מודה באשמה..), האפליקציה (SSMS..) והאובייקט (הטריגר).

עובד ללא דופי, אך משהו עדיין מטריד אותנו: פנינו פעמיים ל-Inserted & Deleted תוך בדיקת הלוגיקה: פעם כדי לעדכן את MyTbl ופעם את MyTblLog. גם כפילות, גם מצריך תחזוקה כפולה במקרה של שינויים בלוגיקה, גם סכנה לטעויות ואי התאמות, ובקיצור: ניתן לעשות שימוש באופרטור Output מתוך פקודת ה-Update שבטריגר כדי לעדכן גם את טבלת הלוג:

Create Or Alter Trigger Tr_MyTbl

On MyTbl

Instead Of Update

As

Update T

Set ColI=I.ColI,

ColV=I.ColV,

ColD=I.ColD

Output Inserted.*

Into MyTblLog(ID,ColI,ColV,ColD)

From MyTbl T

Inner Join Inserted I

On T.ID=I.ID

Inner Join Deleted D

On T.ID=D.ID

Where (Select Count(*) From (Select I.ColI X Union Select D.ColI X) T)

+(Select Count(*) From (Select I.ColV X Union Select D.ColV X) T)

=3;

פקודה אחת שמבצעת Update בטבלה אחת ו-Insert בשנייה, אם נשנה את התנאים ב-Where זה ישנה עבור שתיהן ולכן הן בהכרח ישארו מתואמות, והקוראים החשדניים מוזמנים להריץ שוב את פקודות ה-Update הנ"ל ולוודא שהכל רץ תקין.