การเข้าใจปัญหาทริกเกอร์แทรกใน SQL Server 2005

เมื่อทำงานกับ SQL Server 2005 คุณอาจจะพบสถานการณ์ที่ทริกเกอร์ของคุณแทรกเรคอร์ดเพียงเรคอร์ดเดียวในตารางเป้าหมายแม้ว่าการทำธุรกรรมแทรกหลักจะสร้างเรคอร์ดหลายเรคอร์ด นี่อาจเป็นเรื่องที่น่าผิดหวังโดยเฉพาะอย่างยิ่งเมื่อคุณใช้การตั้งค่าย่อยเพื่อแทรกข้อมูลลงในตารางหลัก ในโพสต์นี้เราจะไปผ่านปัญหาทั่วไปและวิธีแก้ไขในรายละเอียด

อธิบายปัญหา

ลองนึกภาพว่าคุณมีตารางชื่อ tblMenuItems และเมื่อแทรกเรคอร์ดใหม่ลงไป คุณต้องการให้ทริกเกอร์ tblMenuItemInsertSecurity แทรกเรคอร์ดลงในตารางอีกตาราง (tblRestrictedMenuItems) โดยอัตโนมัติ ทริกเกอร์ดูเหมือนว่าจะทำงานได้ดีในกรณีที่มีการแทรกเรคอร์ดเดียว แต่ไม่ทำงานตามที่คาดหวังเมื่อมีการแทรกเรคอร์ดหลายเรคอร์ดในคราวเดียวโดยใช้การตั้งค่าย่อย

นี่คือคำสั่งแทรก

INSERT INTO [tblMenuItems] ([ID], [MenuID], [SortOrder], [ItemReference], [MenuReference], [ConcurrencyID]) 
SELECT [ID], [MenuID], [SortOrder], [ItemReference], [MenuReference], [ConcurrencyID] 
FROM [IVEEtblMenuItems]

รหัสทริกเกอร์ตัวอย่าง

CREATE TRIGGER [dbo].[tblMenuItemInsertSecurity] ON [dbo].[tblMenuItems] 
FOR INSERT
AS
BEGIN
    DECLARE @iRoleID int
    DECLARE @iMenuItemID int

    SELECT @iMenuItemID = [ID] FROM Inserted

    DECLARE tblUserRoles CURSOR FASTFORWARD FOR SELECT [ID] FROM tblUserRoles
    OPEN tblUserRoles 
    FETCH NEXT FROM tblUserRoles INTO @iRoleID 

    WHILE (@@FetchStatus = 0)
    BEGIN
        INSERT INTO tblRestrictedMenuItems(
            [RoleID],
            [MenuItemID],
            [RestrictLevel])
        VALUES(
            @iRoleID,
            @iMenuItemID,
            1)    

        FETCH NEXT FROM tblUserRoles INTO @iRoleID 
    END

    CLOSE tblUserRoles 
    DEALLOCATE tblUserRoles
END

การเข้าใจว่าทำไมทริกเกอร์ถึงล้มเหลว

สาเหตุหลักที่ทริกเกอร์ของคุณไม่แทรกเรคอร์ดหลายเรคอร์ดคือการใช้งานตารางเสมือน Inserted เมื่อตัวกระตุ้นทำงาน ตาราง Inserted จะประกอบด้วยแถวทั้งหมดที่กำลังจะถูกแทรก แต่ตรรกะของทริกเกอร์ของคุณกลับประมวลผลเฉพาะแถวแรกเท่านั้น

แนวคิดสำคัญที่ต้องเข้าใจ

• ทริกเกอร์และแถว: ใน SQL Server ทริกเกอร์จะทำงานเพียงครั้งเดียวสำหรับการกระทำการกระตุ้นต่อแต่ละชนิด (แทรก, อัปเดต, ลบ) และสามารถจัดการกับหลายแถวในเวลาเดียวกันได้

• ตารางเสมือน Inserted: ตารางนี้ช่วยให้เข้าถึงเรคอร์ดที่กำลังจะถูกแทรก และมีแถวทั้งหมดไม่ใช่แค่แถวแรก

วิธีแก้ไข: ปรับปรุงตรรกะของทริกเกอร์

เพื่อจัดการการแทรกสำหรับหลายเรคอร์ดได้อย่างมีประสิทธิภาพ คุณสามารถปรับโครงสร้างทริกเกอร์ของคุณได้สองวิธี: ใช้ลูปกับตัวชี้หรือลดการใช้ลูปโดยใช้การดำเนินการที่มีชุดข้อมูลซึ่งมีประสิทธิภาพมากกว่า

1. ปรับโครงสร้างด้วยตัวชี้

สามารถทำการวนรอบผ่านตารางเสมือน Inserted โดยใช้ตัวชี้ แต่ควรระวังว่าการใช้วิธีนี้อาจทำให้เกิดปัญหาด้านประสิทธิภาพเมื่อทำงานกับชุดข้อมูลขนาดใหญ่

นี่คือเวอร์ชันที่ปรับปรุงของทริกเกอร์ของคุณโดยใช้ตัวชี้:

CREATE TRIGGER [dbo].[tblMenuItemInsertSecurity] ON [dbo].[tblMenuItems] 
FOR INSERT
AS
BEGIN
    DECLARE @iRoleID int
    DECLARE @iMenuItemID int

    DECLARE tblUserRoles CURSOR FASTFORWARD FOR SELECT [ID] FROM tblUserRoles
    OPEN tblUserRoles 
    FETCH NEXT FROM tblUserRoles INTO @iRoleID 

    -- วนรอบผ่านแต่ละแถวในตาราง Inserted
    DECLARE tblInserted CURSOR FOR SELECT [ID] FROM Inserted
    OPEN tblInserted 
    FETCH NEXT FROM tblInserted INTO @iMenuItemID 

    WHILE @@FETCH_STATUS = 0
    BEGIN
        WHILE (@@FetchStatus = 0)
        BEGIN
            INSERT INTO tblRestrictedMenuItems(
                [RoleID],
                [MenuItemID],
                [RestrictLevel])
            VALUES(
                @iRoleID,
                @iMenuItemID,
                1)    

            FETCH NEXT FROM tblUserRoles INTO @iRoleID 
        END

        FETCH NEXT FROM tblInserted INTO @iMenuItemID 
    END

    CLOSE tblInserted
    DEALLOCATE tblInserted
    CLOSE tblUserRoles 
    DEALLOCATE tblUserRoles
END

2. ใช้การดำเนินการตามชุดข้อมูล (แนะนำ)

วิธีนี้มีประสิทธิภาพมากกว่าและช่วยหลีกเลี่ยงการวนรอบที่ไม่จำเป็น

ลองเปลี่ยนตรรกะเพื่อเชื่อมโยงโดยตรงกับตาราง Inserted:

CREATE TRIGGER [dbo].[tblMenuItemInsertSecurity] ON [dbo].[tblMenuItems] 
FOR INSERT
AS
BEGIN
    INSERT INTO tblRestrictedMenuItems ([RoleID], [MenuItemID], [RestrictLevel])
    SELECT u.[ID], i.[ID], 1
    FROM tblUserRoles u
    JOIN Inserted i ON <condition if needed>
END

รหัสนี้ช่วยให้มั่นใจว่ามีการแทรกเรคอร์ดที่เหมาะสมจำนวนมากในตาราง tblRestrictedMenuItems สำหรับแต่ละแถวในตาราง Inserted

สรุป

โดยการเข้าใจพฤติกรรมของทริกเกอร์และการใช้งานตารางเสมือน Inserted อย่างถูกต้อง คุณสามารถมั่นใจได้ว่าทริกเกอร์ของ SQL Server 2005 ของคุณทำงานได้อย่างมีประสิทธิภาพเมื่อมีการแทรกเรคอร์ดหลายรายการ เลือกระหว่างวิธีการวนรอบหรือชุดข้อมูลตามบริบทเฉพาะของคุณเพื่อประสิทธิภาพสูงสุด

หากคุณทำตามขั้นตอนที่กล่าวไว้ข้างต้นเพื่อปรับปรุงทริกเกอร์ คุณน่าจะเริ่มเห็นพฤติกรรมที่ต้องการ และมีการแทรกเรคอร์ดหลายเรคอร์ดลงในตารางเป้าหมายของคุณเมื่อทำการแทรกหลายรายการในตารางหลัก