יום רביעי, 6 בינואר 2010

Trigger with insert select

שלום רב,

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

במבט ראשון, הפתרון הינו פשוט :

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

בדרך כלל הטריגר יראה כך:

CREATE TRIGGER [dbo].[tri_TRIGGER_test_insert]
ON [dbo].[t_test_trigger]
AFTER INSERT
AS
declare @id int
select @id = id from inserted
exec p_send_mail @id
GO

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

ראשית ניצור טבלה:
CREATE table t_test_trigger
( id int)
go

להלן טריגר אשר אחרי כל הכנסת רשומה ידפיס את המספר הלקוח שהוכנס:
alter TRIGGER [dbo].[tri_TRIGGER_test_insert]
ON [dbo].[t_test_trigger]
AFTER INSERT
AS
declare @id int
select @id = id from inserted
select @id
GO

במידה ונבצע הכנסת נתונים בדרך הבאה, הכל יעבוד כשורה והטריגר יקפוץ 3 פעמים:
insert into t_test_trigger values (1)
insert into t_test_trigger values (2)
insert into t_test_trigger values (3)

אך שימו לב שאם נבצע הכנסת נתונים בו זמנית, הטריגר יקפוץ פעם אחד בלבד:
insert into t_test_trigger
select *
from t_test_trigger

במקרה שכזה הפרוצדורה תרוץ רק פעם אחת, ורק לקוח אחד יקבל מייל...

פתרון:
במקרים בהם אנו יודעים שאופי הכנסת הנתונים מול הטבלה הינה גם על ידי
insert and select
הפתרון הינו לשים לולאה בתוך הטריגר אשר תרוץ על נתוני טבלת ה- inserted וכך הפרוצדורה שלנו תרוץ על כל הרשומות.

לדוגמא:
alter TRIGGER [dbo].[tri_TRIGGER_test_insert]
ON [dbo].[t_test_trigger]
AFTER INSERT
AS

DECLARE @id int
DECLARE uniques_cursor CURSOR FOR

select id from inserted

OPEN uniques_cursor
FETCH NEXT
FROM uniques_cursor
INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

select @id

FETCH NEXT
FROM uniques_cursor
INTO @id
END
CLOSE uniques_cursor
DEALLOCATE uniques_cursor

GO


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

בהצלחה.

הוסף רשומת תגובה