הבלוג של גרי רשף

03/09/2010

טבלה בת שורה אחת

Filed under: Uncategorized — תגיות: , , , , , — גרי רשף @ 10:50

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

Use tempdb;

Go


If object_id('Try001') Is Not Null Drop Table Try001;

Go


Create Table Try001(I As 1 Primary Key,

                S Varchar(Max));


Insert Into Try001(S) Select 'Try001';

Go

לטבלה יש עמודה מחושבת שערכה 1 המשמשת כמפתח, ולכן לא ניתן להוסיף שורות עם ערך אחר וגם לא עם אותו ערך.

עמודה S מייצגת פרמטר כלשהו, וכמובן שאפשר להוסיף עמודות בעת יצירת הטבלה על פי מספר הפרמטרים הדרוש.

כיצד נמנע מחיקה של השורה הקיימת?

פתרון אפשרי יכול להיות Instead of Trigger שבמקרה של Delete לא יעשה כלום:

Create Trigger Tr_Try001 On Try001

Instaed Of Delete As

Return;

Go

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

כעת נסיון למחוק את השורה בעזרת Delete יכשל (כלומר- הפעולה לכאורה תצליח ולא תופיע אף הודעת שגיאה, אך בפועל לא יקרה דבר והשורה תישאר):

Delete From Try001;

Go


Select * From Try001;

Go

אבל מה יקרה אם נשתמש ב-Truncate?

Truncate Table Try001;

Go


Select * From Try001;

Go

אופס! ה-Truncate אינו מפעיל את הטריגר והשורה נמחקה..

מה לעשות?

הנה הצעה לפתרון חלופי- ניצור טבלת פרמטרים כנ"ל בשינוי קטן- העמודה לא תהיה מחושבת אבל יהיה אילוץ שהיא חייבת להיות שווה 1:

If object_id('Try011') Is Not Null Drop Table Try011;

Go


Create Table Try011(I Int Primary Key Check(I=1),

                    S Varchar(Max));

Go


Insert Into Try011 Select 1,'Try011';

Go

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

ניצור את טבלת שומרת הראש:

If object_id('Try012') Is Not Null Drop Table Try012;

Go


Create Table Try012(I Int Primary Key Check(I=1));

Go


Insert Into Try012 Select 1;

Go

וכעת ניצור Foreign Keys הדדיים:

Alter Table Try011

Add Foreign Key (I) References Try012(I);

Go


Alter Table Try012

Add Foreign Key (I) References Try011(I);

Go

נבדוק את יציבות המערכת:

Delete From Try011;

Go


Delete From Try012;

Go


Truncate Table Try011;

Go


Truncate Table Try012;

Go


Drop Table Try011;

Go


Drop Table Try012;

Go


Select * From Try011;

Go


Select * From Try012;

Go

כל הנסיונות למחוק את השורות ואף את הטבלאות עצמן- נכשלו: וִירִיחוֹ סגֶרֶת וּמְסֻגֶּרֶת מִפְּנֵי בְּנֵי יִשְׂרָאֵל אֵין יוֹצֵא וְאֵין בָּא (יהושוע ו 1).

האם ניתן לעשות זאת ללא טבלת עזר? לא הצלחתי, ואם מישהו יצליח- אשמח לשמוע!

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

להירגע- את המלכודת יש לפרק בשלבים- לבטל את ה-Foreign Keys ההדדיים, ורק אז לבטל את הטבלאות;

והנה סקריפט מן המוכן:

Declare    @SQL Varchar(Max);

Select    @SQL='Alter Table Try011 Drop Constraint '+Object_Name(constid)

From    sys.sysconstraints

Where    Object_Name(id)='Try011'

        And Object_Name(constid) Like 'FK%';

Exec(@SQL)

Go


Declare    @SQL Varchar(Max);

Select    @SQL='Alter Table Try012 Drop Constraint '+Object_Name(constid)

From    sys.sysconstraints

Where    Object_Name(id)='Try012'

        And Object_Name(constid) Like 'FK%';

Exec(@SQL)

Go

מודעות פרסומת

4 תגובות »

  1. אם כך, תגדיר משתמש בעל הגדרות db_datareader db_datawriter
    (בדיוק כמו במאמר שהעלית לגבי בעיה שהייתה לך לפני כמה שבועות, שבה היית צריך הרשאות גובהות יותר כדי לעשות TRUNCATE דרך VIEW אני חושב)
    אבל אין ספק שפתרון ברמת הטבלה ולא ברמת ההרשאות הוא מעניין:)

    תגובה של פלג — 05/09/2010 @ 08:38

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

      תגובה של גרי רשף — 05/09/2010 @ 17:34

  2. לא ניתן לקשור את הטבלה הזו לעצמה?
    ובכך למנוע את המחיקה?

    תגובה של חיים — 03/09/2010 @ 15:26

    • זה לא הולך- המערכת אינה מאפשרת ליצור FK כזה כי הוא גורם ללולאה אינסופית.

      תגובה של גרי רשף — 03/09/2010 @ 15:51


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: