Handling All Rows in Bulk Operations with Triggers in SQL Server

Handling All Rows in Bulk Operations with Triggers in SQL Server

To process multiple rows correctly during bulk operations in SQL Server, leverage the INSERTED and DELETED pseudo-tables in triggers. These tables store the new and old values of rows affected by INSERT, UPDATE, or DELETE operations. Set-based operations on these tables allow handling all affected rows efficiently, avoiding row-by-row processing.


Example:

CREATE TABLE Attendance (

    ID INT IDENTITY PRIMARY KEY,         -- Unique identifier for each record

    EmployeeID INT,                      -- ID of the employee (foreign key reference)

    PunchTime DATETIME,                  -- The time the employee punched in or out

    Status NVARCHAR(50),                 -- Status (e.g., 'Present', 'Absent', 'Late', etc.)

    CreatedDate DATETIME DEFAULT GETDATE()  -- Timestamp for when the attendance was recorded

);

 

CREATE TABLE Transactions (

    TransactionID INT IDENTITY PRIMARY KEY,

    TransactionDate DATETIME,

    OperationType NVARCHAR(10), -- 'INSERT', 'UPDATE', or 'DELETE'

    LogID INT,                  -- ID of the affected row in the Attendance table

    EmployeeID INT,

    PunchTime DATETIME,

    Status NVARCHAR(50)

);

  

CREATE TRIGGER trg_AfterBulkInsertUpdateDelete

ON Attendance

AFTER INSERT, UPDATE, DELETE

AS

BEGIN

    -- Log INSERTED rows for INSERT operations

    IF EXISTS (SELECT * FROM INSERTED)

    BEGIN

        INSERT INTO Transactions (TransactionDate, OperationType, LogID, EmployeeID, PunchTime, Status)

        SELECT GETDATE(), 'INSERT', ID, EmployeeID, PunchTime, Status

        FROM INSERTED;

    END

 

    -- Log DELETED rows for DELETE operations

    IF EXISTS (SELECT * FROM DELETED)

    BEGIN

        INSERT INTO Transactions (TransactionDate, OperationType, LogID, EmployeeID, PunchTime, Status)

        SELECT GETDATE(), 'DELETE', ID, EmployeeID, PunchTime, Status

        FROM DELETED;

    END

 

    -- Log UPDATED rows for UPDATE operations

    IF EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED)

    BEGIN

        INSERT INTO Transactions (TransactionDate, OperationType, LogID, EmployeeID, PunchTime, Status)

        SELECT GETDATE(), 'UPDATE', i.ID, i.EmployeeID, i.PunchTime, i.Status

        FROM INSERTED i

        JOIN DELETED d ON i.ID = d.ID

        WHERE i.EmployeeID <> d.EmployeeID

           OR i.PunchTime <> d.PunchTime

           OR i.Status <> d.Status;

    END

END;

 

Consider using BATCHSIZE for bulk inserts to control the number of rows inserted in each batch. This helps manage large datasets by processing in smaller chunks, reducing locking contention and enhancing performance.


Example

BULK INSERT Attendance

FROM 'D:\AttendanceData.csv'

WITH (BATCHSIZE = 1000); 


Using INSERTED and DELETED tables for set-based processing, along with an appropriate BATCHSIZE for bulk operations, ensures that triggers correctly handle all affected rows during bulk INSERT, UPDATE, or DELETE operations in SQL Server.



Comments

Post a Comment

Popular posts from this blog

Multiline to singleline IN C# - CODING

Multiline to singleline IN C# - CODING using System; using System.Linq; class Program {     static void Main(string[] args)     {         string input = @"This is                  multiline";                  // Split the input into lines, trim each line, and filter out empty lines         string result = string.Join(" ", input.Split(new[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries)                                                .Select(line => line.Trim()));         Console.WriteLine(result);     } } Output: This is  multiline
Read more

EF Core interview questions for beginners

1. What is Entity Framework Core (EF Core)? EF Core is an open-source Object-Relational Mapper (ORM) that enables .NET developers to work with databases using .NET objects. It allows you to interact with a database using strongly typed classes and LINQ queries, rather than writing raw SQL queries. EF Core supports various database providers, such as SQL Server, SQLite, PostgreSQL, MySQL, and more. 2. What are the advantages of using EF Core over traditional ADO.NET? Productivity:  EF Core allows you to work with databases using high-level objects, reducing the amount of boilerplate code you need to write. Cross-platform:  EF Core supports multiple databases and works on Windows, Linux, and macOS. LINQ Support:  You can query the database using LINQ, which is more type-safe and easier to maintain than raw SQL queries. Code-First & Database-First:  EF Core supports both approaches to model databases. Change Tra...
Read more

EF Core interview questions for experienced

EF Core interview questions for experienced 1. What are the main differences between Entity Framework (EF) and Entity Framework Core (EF Core)? EF Core is a complete rewrite of EF. Unlike EF, EF Core supports cross-platform development, improved performance, LINQ query translation, and has a more modular architecture. EF Core does not support all features that EF 6 does (like lazy loading and some database providers). 2. How does EF Core handle migrations, and what are some common pitfalls? EF Core uses migrations to update the database schema based on model changes. Common pitfalls include not applying migrations correctly, misalignment between model and database state, or missing/overwriting migrations during collaboration in a team environment. 3. Explain the difference between Add and Attach methods in EF Core. Add method is used to add a new entity to the context and mark it as Added , while Attach attaches an existing entity to the context with no changes. The Attach meth...
Read more