השימוש בטריגרים
טריגר על פעולות DML בטבלה הוא כלי מוכר יחסית, אך לא כל האופציות מוכרות; ואולי מתבלבלים לעיתים בינו לבין טריגרים אחרים.
הפוסט הזה עוסק בטריגרים על טבלאות שמופעלים בעקבות ביצוע פעולות Delete / Update / Insert בטבלה עליה הם הוגדרו. הטריגרים אינם מופעלים בעת ביצוע פקודת Select או Truncate, שינוי סכימה של טבלה, או כל שינוי שהוא באובייקטים של המערכת; ולכל אלו יש פתרונות אחרים.
כדי לפשט את הדוגמאות- אשתמש בכל פעם בטבלה אחת בלבד, והטריגרים יפעילו פקודות Print או Select כדי להמחיש את הפעולה שלהם. במציאות הטריגרים אמורים להפעיל בדרך כלל פקודות Insert לטבלת לוג שמתעדת את השינויים בטבלה אליה הטריגר קשור, אם כי יכולים להיות להם שימושים נוספים.
כפי שציינתי- הטריגרים משמשים בדרך כלל לתיעוד השינויים לטבלת לוג, ולשם כך עליהם "ללכוד" את השורות שהשתנו. הטריגר הוא סקריפט (סדרת פקודות ב-TSQL) שמוגדר על טבלה ומופעל – בהתאם לאופן בו הוא הוגדר – בעקבות ביצוע פעולות Delete / Update / Insert (ניתן להגדיר טריגר על פעולה אחת או על מספר פעולות).
הטריגר יכול להיעזר בשני סטים יחודיים: Inserted שהוא סט השורות שהתווספו, ו-Deleted שהוא סט השורות שנמחקו. בפקודת Insert יש תוכן רק ב-Inserted, בפקודת Delete יש תוכן רק ב-Deleted, ובפקודת Update יש תוכן בשתיהן (Inserted עם השורות לאחר העדכון ו-Deleted עם השורות לפני העדכון).
ניצור טבלה להדגמה וניצור לה טריגר:
Use tempdb;
Go
If Object_Id('T_1','U') Is Not Null Drop Table T_1;
Go
Create Table T_1(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_1 On T_1
After Insert, Update, Delete As
Select Case When I.ID Is Null Then 'Deleted'
When D.ID Is Null Then 'Inserted'
Else 'Updated' End [Type],
IsNull(I.ID,D.ID) ID,
D.Txt OldTxt,
I.Txt NewTxt
From Inserted I
Full Outer Join Deleted D
On I.ID=D.ID;
Go
הטריגר שולף מ-Deleted ו-Inserted את כל השורות הרלוונטיות: בגלל שמדובר ב-Full Outer Join ישלפו במקרה של Insert כל ה-Inserted למרות שאין Deleted, להיפך במקרה של Delete, ובמקרה של Update ישלפו כולם; ולפי עמודת ה-ID נוכל לדעת באיזה סוג פעולה מדובר (עמודה Type);
כאשר אני מדגיש שוב שהשליפה למסך נועדה להמחיש כיצד הטריגר עובד: במציאות הפלט יופנה לטבלת לוג מתאימה.
Insert
Into T_1(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Update T_1
Set Txt='New bb'
Where ID=2;
Delete
From T_1
Where Txt='ccc';
אפשר לראות שכל אחת מהפעולות הפעילה את הטריגר, והוא שלף למסך את המידע השורות הרלוונטיות.
כמובן שניתן ליצור טריגר רק לפעולה אחת או שתיים, וניתן ליצור מספר טריגרים שיפעלו בטור זה אחר זה.
העובדה שהטריגר הנ"ל הוגדר כ-After (כדאי לעיין בקוד) היא ברירת המחדל, וניתן להשתמש באופרטור For במקומה והתוצאה תהיה זהה.
כדאי להדגיש שפעולת ה-DML והפעלת הטריגר מהווים טרנזקציה אחת: אם אחד מהם יכשל- כל הטרנזקציה תיפול, ושתי הפעולות לא יתבצעו. בנוסף- אם הטריגר איטי – כל פעולת ה-DML תהיה איטית ויקח זמן רב מהצפוי עד שנקבל אישור שהפעולה הסתיימה בהצלחה. בנוסף- החיווי בלשונית ה-Messages תתייחס גם לפעולת ה-DML וגם לפעולת הטריגר.
אופציה נוספת מיוחדת לטריגרים היא הפונקציה Update שבעזרתה ניתן לבדוק אילו עמודות השתנו:
Use tempdb;
Go
If Object_Id('T_2','U') Is Not Null Drop Table T_2;
Go
Create Table T_2(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_2 On T_2
After Insert, Update, Delete As
If Update(ID) Print 'ID column was updated';
If Update(Txt) Print 'Txt column was updated';
Go
Insert
Into T_2(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Update T_2
Set Txt='New bb'
Where ID=2;
Delete
From T_2
Where Txt='ccc';
בפקודת ה-Insert עודכנו שתי העמודות ולכן שתי פקודות ה-Print פעלו,
בפקודת ה-Update רק עמודה Txt עודכנה ולכן רק פקודת ה-Print "שלה" פעלה,
ובפקודת ה-Delete העמודות – לצורך העניין – לא התעדכנו ופקודות ה-Print לא פעלו.
האם חייבים לכתוב תנאי Update על כל עמודה וכמודה כדאי לדעת אילו התעדכנו? הרי בטבלה יכולים להיות עד 1024 עמודות (בהנחה שאין שימוש ב-Sparse).. לנוחיות ציבור הנוסעים קיים משתנה בשם Columns_Updated שהמידע הזה מקודד בו: מדובר במשתנה מסוג VarBinary שכל Byte בו מייצג שמונה עמודות על פי הסדר. כלומר- ערכו הבינארי יכול להיות למשל 01001001 ואז ניתן לדעת שעמודות מספר 2,5,8 בשמיניה שאותו Byte מייצג – התעדכנו.
בעזרת טבלת מספרים מ-1 ועד 128 (כי 1024 עמודות הן 128 שמיניות של עמודות), שימוש ב-& (ע"ע Bitwise And), פניה לטבלת המערכת sys.columns בה מופיעות כל העמודות ממוספרות על פי הסדר, הפונקציה DataLength שמחשבת כמה Bytes (שמיניות) יש ב-Columns_Updated ועוד כמה פירוטכניקות מגיעים לקוד הבא (טבלה, טריגר, ופעולות DML להמחשה):
Use tempdb;
Go
If Object_Id('T_3','U') Is Not Null Drop Table T_3;
Go
Create Table T_3(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_3 On T_3
After Insert, Update, Delete As
With Num As
(Select Top 128 Row_Number() Over(Order By DB_ID()) N --טבלת מספרים: מקסימום 128 שמיניות
From sys.messages)
Select Name --שם העמודה
From sys.columns --טבלת העמודות
Inner Join Num --טבלת המספרים הנ"ל
On column_id Between 8*(Num.N-1)+1 And 8*Num.N
Where Object_Name(object_id)='T_3' --רק העמודות של הטבלה לה שייך הטריגר
And Num.N<=DataLength(Columns_Updated()) --צד ימין מציין את מספר השמיניות
And Convert(Binary(1),Substring(Columns_Updated(),Num.N,1))&Power(2,column_id-(8*(Num.N-1)+1))>0 --אילו עמודות נמצאות בשמינייתן
Order By column_id; --מיון לפי סדר העמודות בטבלה
Go
Insert
Into T_3(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Update T_3
Set Txt='New bb'
Where ID=2;
Delete
From T_3
Where Txt='ccc';
שוב- הפלט למסך הוא רק כדי להמחיש מה המערכת זיהתה, ובמציאות המידע יופנה בדרך כלל לטבלה מתאימה.
האם קיים טריגר מסוג Before Insert? לא בדיוק: קיים Instead of Trigger שמתבצע במקום הפעולה (וממילא לפני שהיא מתבצעת..):
Use tempdb;
Go
If Object_Id('T_4','U') Is Not Null Drop Table T_4;
Go
Create Table T_4(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_4 On T_4
Instead of Update, Delete As
Return
Go
Insert
Into T_4(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Update T_4
Set Txt='New bb'
Where ID=2;
Delete
From T_4
Where Txt='ccc';
Select *
From T_4;
הטריגר הפעם הוא על פקודות Update & Delete ולא על Insert, ולכן פקודת ה-Insert התבצעה אך האחרות לא וניתן לראות זאת בפלט של פקודת ה-Select בסוף.
בטריגר עצמו מופיעה רק פקודת Return ולכן הוא אינו מבצע דבר במקום מה שאמור היה להתבצע.
כפי שציינתי- ניתן ליצור מספר טריגרים על אותה טבלה, אבל Instead of Triggers אינם יכולים לחפוף זה את זה (כלומר לא יכולים להיות שני Instead of Insert Triggers), וכשאחד מהם מופעל- הטריגרים האחרים (After Triggers) לא יתבצעו.
סדר ביצוע הטריגרים הוא אקראי אלא אם כן הגדרנו אחרת באמצעות SP_SetTriggerOrder:
Use tempdb;
Go
If Object_Id('T_5','U') Is Not Null Drop Table T_5;
Go
Create Table T_5(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_5_1 On T_5
After Insert, Update, Delete As
Print 'Trg_T_5_1';
Go
Create Trigger Trg_T_5_2 On T_5
After Insert, Update, Delete As
Print 'Trg_T_5_2';
Go
Create Trigger Trg_T_5_3 On T_5
After Insert, Update, Delete As
Print 'Trg_T_5_3';
Go
Insert
Into T_5(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Exec SP_SetTriggerOrder 'Trg_T_5_1', 'Last', 'Update';
Exec SP_SetTriggerOrder 'Trg_T_5_3', 'First', 'Delete';
Update T_5
Set Txt='New bb'
Where ID=2;
Delete
From T_5
Where Txt='ccc';
בפקודת ה-Insert הטריגרים בוצעו בסדר אקראי (במקרה או שלא- לפי סדר היווצרותם).
לאחר מכן הטריגר הראשון הוגדר כאחרון לפקודות Update, והטריגר השלישי כראשון לפקודות Delete.
בפקודת ה-Update הטריגר הראשון בוצע אחרון כפי שהוגדר עבורו,
ובפקודת ה-Delete הטריגר השלישי בוצע ראשון כפי שהוגדר עבורו (והראשון אחריו מכיוון שהוא אינו מוגדר כאחרון עבור Delete אלא רק עבור Update).
לבסוף- ניתן ליצור טריגרים גם על Views, בתנאי שמדובר ב-Instead of Trigger:
Use tempdb;
Go
If Object_Id('T_6','U') Is Not Null Drop Table T_6;
Go
Create Table T_6(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_6_1 On T_6
After Insert, Update, Delete As
Print 'Trg_T_6_1';
Go
If Object_Id('V_6','V') Is Not Null Drop View V_6;
Go
Create View V_6 As
Select *
From T_6;
Go
Create Trigger Trg_T_6_2 On V_6
Instead of Insert, Update, Delete As
Print 'Trg_T_6_2';
Go
Insert
Into T_6(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Insert
Into V_6(Txt)
Select 'd' Union All
Select 'ee' Union All
Select 'fff';
כפי שאפשר להבין- פעולת ה-Insert הראשונה הופנתה לטבלה והתבצעה,
ואילו פעולת ה-Insert השניה הופנתה ל-View והטריגר התבצע במקומה.
אין מניעה לבצע Insert ל-Views כמו זה, אולם המגבלה של Instead of Triggers גורפת לכל סוגי ה-Views בין אם ניתן לבצע עליהם פעולות DML ובין אם לא.
טריגרים ניתן לנטרל מבלי לבטל כך (בעיקר כשמדובר בהשעייה זמנית):
Disable Trigger All On T_6; --כל הטריגרים
Go
Disable Trigger Trg_T_6_1 On T_6; --טריגר ספציפי
Go
ולהחזיר לכשירות כך:
Enable Trigger All On T_6; --כל הטריגרים
Go
Enable Trigger Trg_T_6_1 On T_6; --טריגר ספציפי
Go