הודעת התקלה נ"ל מופיעה כשמנסים להכניס לטבלה רשומות שיוצרות כפילות במפתח.
כיצד מונעים זאת? ראשית חכמה התהליך צריך להיות בנוי כך שדברים כאלו לא יקרו, הרי לא מכניסים רשומות לטבלה ומקווים לטוב. אמורה להיות סיבה שתמנע תקלה כזו: אולי בכל יום מגיעים נתונים חדשים שנבדקו ובטבלה יש נתונים רק עד אתמול, אולי מתבצעת מראש מחיקה גורפת של נתונים מהטבלה או חלקית לפי הפילטר של אלו העומדים להיכנס, וכו'.
אני אעסוק כאן בטיפול בנקודת הכניסה, בעמדת הש"ג של הטבלה, וזה כמובן אינו מחליף את הטיפול המערכתי המתבקש.
למניעת הכנסת רשומות שיוצרות כפילות במפתח יש לטפל בשני מישורים:
1. מניעת הכנסת רשומות שהמפתח שלהן כבר קיים.
2. מניעת הכנסת רשומות בעלות מפתח זהה.
לצורך ההדגמה אצור שתי טבלאות עם נתונים- טבלת מכירות בה יש מפתח ראשי לפי עובד ותאריך,
וטבלת עדכון שמייצגת נתונים שיש להוסיף לטבלה (בפועל זה יכול להיות Select מורכב ממספר מקורות אך לשם הפשטות אסתפק בטבלה צנועה):
Use tempdb;
Go
If Object_Id('tempdb..T_Mehirot','U') Is Not Null Drop Table T_Mehirot;
Go
Create Table T_Mehirot(Oved Varchar(10) Not Null,
Taarih DateTime Not Null,
Camut Int);
Alter Table T_Mehirot Add Constraint PK_T_Mehirot Primary Key Clustered (Oved,Taarih)
Go
Insert
Into T_Mehirot
Select 'Tali','20110901',10 Union All
Select 'Tali','20110902',5 Union All
Select 'Tali','20110903',11 Union All
Select 'shaul','20110901',5;
Go
If Object_Id('tempdb..T_Idcun','U') Is Not Null Drop Table T_Idcun;
Go
Create Table T_Idcun(Oved Varchar(10),
Taarih DateTime,
Camut Int);
Go
Insert
Into T_Idcun
Select 'Tali','20110904',9 Union All
Select 'Tali','20110904',9 Union All
Select 'Tali','20110905',12 Union All
Select 'Shaul','20110901',5 Union All
Select 'Shaul','20110902',7;
Go
Select *
From T_Mehirot
Order By Oved,
Taarih;
Select *
From T_Idcun
Order By Oved,
Taarih;
ניסיון להוסיף את נתוני טבלת העדכון לטבלת המכירות יסתיים בכשלון מכיוון שלשאול יש רשומה חדשה אחת שהמפתח שלה כבר קיים, ולטלי שתי רשומות חדשות בעלות אותו מפתח:
Insert
Into T_Mehirot
Select *
From T_Idcun;
הדרך הנכונה למנוע זאת היא כדלקמן:
Insert
Into T_Mehirot
Select Oved,
Taarih,
Max(Camut) Camut
From T_Idcun I
Where Not Exists (Select 1 From T_Mehirot M Where M.Oved=I.Oved And M.Taarih=I.Taarih)
Group By Oved,
Taarih;
הפילטר Where מונע מרשומות בעלות מפתח קיים להיכנס, וה-Group By (לפי שדות המפתח) מאחד רשומות בעלות מפתח זהה.
כעת אנחנו יכולים להיות בטוחים שלא יופיעו הודעות שגיאה כנ"ל באמצע הריצה, אך שוב- יש להדגיש שמדובר בכיבוי שריפות ולא בטיפול מונע.
טקטיקה אחרת שטובה בעיקר בשלב הדיבוג- היא לשמור בצד את מצב הטבלאות ברגע שנעשה הניסיון הכושל לעדכן את טבלת המכירות, ולאח שהתהליך נעצר והתקבלה הודעת שגיאה- לבדוק מה ולמה נכשל.
להלן קוד לדוגמה ("הצעת הגשה" בלשון יצרני המזון):
Begin Try
Begin Tran
Insert
Into T_Mehirot
Select *
From T_Idcun;
Commit;
End Try
Begin Catch
Declare @T_Mehirot Table(Oved Varchar(10), Taarih DateTime, Camut Int);
Insert Into @T_Mehirot Select * From T_Mehirot;
If Object_Id('tempdb..Tmp_Mehirot','U') Is Not Null Drop Table Tmp_Mehirot;
Declare @T_Idcun Table(Oved Varchar(10), Taarih DateTime, Camut Int);
Insert Into @T_Idcun Select * From T_Idcun;
Rollback;
If Object_Id('tempdb..Tmp_Mehirot','U') Is Not Null Drop Table Tmp_Mehirot;
Select * Into Tmp_Mehirot From @T_Mehirot;
If Object_Id('tempdb..Tmp_Idcun','U') Is Not Null Drop Table Tmp_Idcun;
Select * Into Tmp_Idcun From @T_Idcun;
RaisError ('An error has occured',16,1);
End Catch
בלוק ה-Try הוא הניסיון להכניס נתונים לטבלה, במסגרת טרנקציה. במציאות נמצא בו את התהליך כולו, כולל השליפה של הרשומות שיופנו לטבלת המכירות ושכאן היא מיוצגת על ידי טבלת העדכון.
בלוק ה-Catch שמופעל במקרה של שגיאה- מכניס את נתוני שתי הטבלאות למשתני-טבלה (כך שלא יאבדו לאחר ביצוע Rollback לטרנקציה), לאחר ביצוע ה-Rollback הנתונים ממשתני-הטבלה נשמרים בטבלה זמנית, ומוקפצת הודעת שגיאה מתאימה בעזרת RaisError.
ראוי להדגיש שוב- קטע הקוד הזה נראה טריוויאלי ומסורבל שלא לצורך- הרי הנתונים בשתי הטבלאות הלא זמניות זהים לאלו שבטבלאות הזמניות (Tmp), אלא שהכוונה כאן שמדובר בתהליך הרבה יותר מורכב מסתם העברת נתונים מטבלה לטבלה, ובמקרה של נפילה – לא ניתן יהיה לשחזר מה היה בטבלת המכירות בנקודת הכנסת הרשומות החדשות בגלל ה-Rollback.
