The Code Neuron

Roadmaps
Tech Stack
Get In Touch

SQL Triggers: Automating Database Actions

—

by

thecodeneuron
in ,

Welcome back, data adventurers! In our previous post, we delved deep into SQL Functions, discovering how these powerful tools help us summarize, manipulate, and extract insights from our data. We explored everything from COUNT() and SUM() to UPPER(), TRIM(), and essential date functions like GETDATE() and DATEDIFF().

Today, we’re taking another significant leap forward. We’ll explore a more advanced yet incredibly crucial concept: SQL Triggers. These aren’t just functions you call; they’re special database objects that automatically spring into action, ensuring your data remains consistent and your business rules are always followed.

Getting Started with Our eCommerce Database

Before we dive into the fascinating world of SQL Triggers, as always, let’s ensure our database environment is perfectly aligned. Throughout this blog series, we’ve consistently used our custom-built eCommerce database, complete with TeamMembers, ClientAccounts, and Transactions tables.

If you’re already familiar with our eCommerce database setup, fantastic! You’re ready to proceed.

If not, no worries at all! To follow along with the examples in this post and prepare your environment, please refer to our previous blogs where we detail the database and table creation:

Setting up the database will ensure you can execute all the queries and gain hands-on experience as we move forward!

Here are the setup commands for our eCommerce database:

SQL
-- Use the eCommerce database
USE eCommerce;

-- Create the TeamMembers table
CREATE TABLE TeamMembers (
    member_id INT NOT NULL,
    member_name VARCHAR(50) NOT NULL,
    location_city VARCHAR(50) NOT NULL,
    commission_rate DECIMAL(4,2) NOT NULL,
    PRIMARY KEY (member_id)
);

-- Insert records into TeamMembers table
INSERT INTO TeamMembers VALUES
    (101, 'Alice Smith', 'London', 0.12),
    (102, 'Bob Johnson', 'San Jose', 0.13),
    (103, 'Eve Davis', 'New York', 0.10),
    (104, 'Charlie Brown', 'London', 0.11),
    (105, 'Fran White', 'London', 0.26),
    (106, 'Grace Hopper', 'Seattle', 0.09),
    (107, 'David Lee', 'Barcelona', 0.15);

-- Create the ClientAccounts table
CREATE TABLE ClientAccounts (
    client_id INT NOT NULL,
    client_name VARCHAR(50) NOT NULL,
    client_city VARCHAR(50) NOT NULL,
    satisfaction_rating INT NOT NULL,
    assigned_member_id INT NOT NULL,
    PRIMARY KEY (client_id),
    FOREIGN KEY (assigned_member_id) REFERENCES TeamMembers(member_id)
);

-- Insert records into ClientAccounts table
INSERT INTO ClientAccounts VALUES
    (2001, 'Hoffman Corp', 'London', 100, 101),
    (2002, 'Giovanni Ltd', 'Rome', 200, 103),
    (2003, 'Liu Enterprises', 'San Jose', 200, 102),
    (2004, 'Grass Solutions', 'Berlin', 300, 102),
    (2005, 'Global Tech Solutions', 'Hyderabad', 150, 105),
    (2006, 'Clemens Inc', 'London', 100, 101),
    (2007, 'Pereira Services', 'Rome', 100, 104),
    (2008, 'Cisneros Group', 'San Jose', 300, 107);

-- Create the Transactions table
CREATE TABLE Transactions (
    transaction_id INT NOT NULL PRIMARY KEY,
    amt DECIMAL(7,2) NOT NULL,
    odate DATE NOT NULL,
    cnum INT NOT NULL,
    FOREIGN KEY (cnum) REFERENCES ClientAccounts(client_id)
);

-- Insert records into Transactions table
INSERT INTO Transactions VALUES
(3001, 18.69,  '1996-03-10', 2008),
(3002, 1900.19,'1996-03-10', 2007),
(3003, 767.19, '1996-03-10', 2001),
(3005, 5160.45,'1996-03-10', 2003),
(3006, 1098.16,'1996-03-10', 2008),
(3007, 75.75,  '1996-04-10', 2002),
(3008, 4723.00,'1996-05-10', 2006),
(3009, 1713.23,'1996-04-10', 2002),
(3010, 1309.95,'1996-06-10', 2004),
(3011, 9891.88,'1996-06-10', 2006);

For our trigger examples, we’ll add a new column, total_transaction_amount, to the ClientAccounts table. This column will automatically summarize the total transaction value for each client, a perfect use case for triggers!

SQL
-- Add a new column to ClientAccounts for trigger examples
ALTER TABLE ClientAccounts ADD total_transaction_amount DECIMAL(10,2);

-- Update existing client records with their initial total transaction amounts
UPDATE ClientAccounts
SET total_transaction_amount = (SELECT ISNULL(SUM(t.amt), 0) FROM Transactions t WHERE t.cnum = ClientAccounts.client_id);

-- Verify the update
SELECT client_id, client_name, total_transaction_amount FROM ClientAccounts;

Output:

What are SQL Triggers?

A SQL Trigger is a special type of stored procedure that executes automatically in response to a specified event on a table or view. These events are primarily Data Manipulation Language (DML) operations: INSERT, UPDATE, and DELETE.

Unlike regular stored procedures that you explicitly call, triggers work behind the scenes. They act like an automated reaction system, ensuring that when specific data changes, related actions are performed immediately and consistently.

Why Are Triggers Important?

  • Enforcing Complex Business Rules: Triggers can apply intricate logic that simple constraints can’t. For example, updating a client’s loyalty status when their total spending crosses a threshold.
  • Auditing Data Changes: Automatically log who changed what, when, and how for sensitive data. This is crucial for compliance and security.
  • Maintaining Data Consistency: Keep related data synchronized across different tables, preventing discrepancies.
  • Implementing Custom Validations: Perform advanced checks on data before or after it’s modified.

Understanding the inserted and deleted Virtual Tables

When a trigger fires, SQL Server provides two essential virtual tables: inserted and deleted. These tables are temporary and exist only during the trigger’s execution, providing a snapshot of the data involved in the DML operation.

  • inserted table: This table holds the new rows that were just inserted, or the new versions of rows after an UPDATE. It’s available in INSERT and UPDATE triggers.
  • deleted table: This table contains the old rows that were just deleted, or the old versions of rows before an UPDATE. It’s available in DELETE and UPDATE triggers.

By comparing or using data from these virtual tables, your trigger logic can perform precise actions based on the changes.

The AFTER INSERT Trigger: Responding to New Transactions

Let’s create our first trigger. This one will automatically update the total_transaction_amount in the ClientAccounts table whenever a new record is inserted into the Transactions table.

We will explore two common ways to write this trigger: using scalar variables and using direct joins with the virtual tables.

Method 1: Using Scalar Variables

This method declares local variables and populates them from the inserted (or deleted) table. This approach is straightforward for single-row operations.

SQL
-- Create a trigger to update total_transaction_amount after an insert into Transactions
-- (Method 1: Using Scalar Variables)
CREATE TRIGGER trg_UpdateClientTotalOnInsert_Scalar
ON Transactions
AFTER INSERT AS
BEGIN
    SET NOCOUNT ON;

    -- Declare variables to hold values from the inserted record
    DECLARE @new_amt DECIMAL(7,2);
    DECLARE @new_cnum INT;

    -- Assign values from the 'inserted' virtual table to the variables.
    -- IMPORTANT: This method works correctly only if ONE row is inserted at a time.
    -- If multiple rows are inserted, these variables will only hold values from the LAST row.
    SELECT @new_cnum = cnum, @new_amt = amt FROM inserted;

    -- Update the ClientAccounts table by adding the new order amount
    UPDATE ClientAccounts
    SET total_transaction_amount = ISNULL(total_transaction_amount, 0) + @new_amt
    WHERE client_id = @new_cnum;
END;
GO

Output: You can now see the trigger created as shown below.

How it works (Scalar Variable Method):

  • DECLARE @new_amt DECIMAL(7,2); DECLARE @new_cnum INT;: Declares local variables to temporarily store the amount and client ID.
  • SELECT @new_cnum = cnum, @new_amt = amt FROM inserted;: This is the crucial part. It attempts to assign values from the inserted table to the scalar variables. However, if multiple rows are inserted in a single INSERT statement, these variables will only capture the values from the last row processed in the inserted table. This can lead to incorrect calculations for bulk inserts.
  • The UPDATE statement then uses these scalar variables to update the ClientAccounts table.

Let’s test it (Scalar Variable Method)!

First, check the current total_transaction_amount for ‘Hoffman Corp’ (client ID 2001):

SQL
-- Purpose: Check current total_transaction_amount for Client ID 2001 (Hoffman Corp).
SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id = 2001;

Output:

Now, insert a single new transaction for client 2001:

SQL
INSERT INTO Transactions VALUES (3012, 1500.00, '2025-07-23', 2001);

You should see that total_transaction_amount for ‘Hoffman Corp’ has increased by 1500! Let’s re-run the query to see below result.

Now, let’s see the limitation:

SQL
-- Insert multiple transactions in one go:
INSERT INTO Transactions VALUES
    (3013, 100.00, '2025-07-23', 2001),
    (3014, 200.00, '2025-07-23', 2001);
    
-- Purpose: Check total_transaction_amount after bulk insert (scalar trigger may show incorrect sum).
SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id = 2001;
-- If the scalar variable trigger was active, it would likely only add 100.00 (from 3014), not 200.00.

Output: Visible that only first transaction amount was added, not the second one. So, total amount now set to 2367.19, not added another transaction amount.

Let’s correct this by manually updating, to keep table clean and correct. run the below query to re-adjust values.

SQL
-- Update existing client records with their initial total transaction amounts
UPDATE ClientAccounts
SET total_transaction_amount = (SELECT ISNULL(SUM(t.amt), 0) FROM Transactions t WHERE t.cnum = ClientAccounts.client_id);
--verify
SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id = 2001;

Output: After executing above queries, you can see the proper value getting set.

Method 2: Joining with Virtual Tables (Recommended)

This method directly joins the target table with the inserted (or deleted) virtual table within the UPDATE statement. This approach is robust and correctly handles both single-row and multi-row DML operations.

SQL
-- Drop the scalar variable trigger first
IF OBJECT_ID('trg_UpdateClientTotalOnInsert_Scalar', 'TR') IS NOT NULL
    DROP TRIGGER trg_UpdateClientTotalOnInsert_Scalar;
GO

-- Purpose: Create a trigger to update total_transaction_amount after an insert (Joining Method - Recommended).
-- Create a trigger to update total_transaction_amount after an insert into Transactions
-- (Method 2: Joining with Virtual Tables - Recommended and Aggregated)
CREATE TRIGGER trg_UpdateClientTotalOnInsert_Join
ON Transactions
AFTER INSERT AS
BEGIN
    SET NOCOUNT ON;
    -- Update ClientAccounts by joining with the aggregated 'inserted' data.
    -- We sum the 'amt' for each 'cnum' (client_id) in the 'inserted' table
    -- to get the total amount for that client from the current batch of inserts.
    UPDATE ca
    SET ca.total_transaction_amount = ISNULL(ca.total_transaction_amount, 0) + i_agg.total_new_amt
    FROM ClientAccounts ca
    INNER JOIN (
        -- Subquery to aggregate the amounts from the 'inserted' pseudo-table
        SELECT
            cnum,
            SUM(amt) AS total_new_amt -- Sum all amounts for each client in the batch
        FROM inserted
        GROUP BY cnum -- Group by client number to get a sum per client
    ) AS i_agg ON ca.client_id = i_agg.cnum;
END;
GO

Output: After dropping earlier trigger and importing this one, you can see the new trigger added to the table as below.

How it works (Joining Method):

  • The UPDATE statement directly uses a JOIN clause with the inserted table.
  • This allows the UPDATE to process all rows that were part of the INSERT operation, ensuring that total_transaction_amount is correctly updated for every affected client, even during bulk inserts.
  • Crucially, the subquery (SELECT cnum, SUM(amt) AS total_new_amt FROM inserted GROUP BY cnum) aggregates the total amount for each client from the batch of inserted transactions. This prevents the issue of only the last transaction’s amount being considered for a client when multiple transactions for that client are inserted simultaneously.

Let’s test it (Joining Method)!

First, ensure trg_UpdateClientTotalOnInsert_Scalar is dropped or disabled if it exists to avoid conflicts. (Worried about how to DROP / DISABLE the Trigger? – We have it covered at the end, just before conclusion! )

SQL
-- Insert multiple transactions in one go (assuming trg_UpdateClientTotalOnInsert_Join is active):
INSERT INTO Transactions VALUES
    (3015, 100.00, '2025-07-23', 2001),
    (3016, 200.00, '2025-07-23', 2001);

INSERT INTO Transactions VALUES
    (3017, 50.00, '2025-07-23', 2001),
    (3018, 20.00, '2025-07-23', 2001);

-- Purpose: Verify total_transaction_amount for Client ID 2001 after bulk insert (join-based trigger).
SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id = 2001;
-- The total_transaction_amount for Hoffman Corp should now correctly reflect the sum of all new transactions.

Output: Now you can see the correct result.

Key Takeaway: While scalar variables might seem simpler for single-row operations, always prefer joining with inserted and deleted virtual tables in triggers. This ensures your triggers work correctly and efficiently for multi-row DML operations, which are common in real-world applications.

The AFTER UPDATE Trigger: Adapting to Transaction Changes

When an existing transaction is updated, we need a trigger to ensure the total_transaction_amount remains accurate. This trigger is slightly more complex as it needs to consider both the old and new values.

Method 1: Using Scalar Variables

SQL
-- Create a trigger for update on Transactions table (Method 1: Using Scalar Variables)
CREATE TRIGGER trg_UpdateClientTotalOnUpdate_Scalar
ON Transactions
AFTER UPDATE AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @new_amt DECIMAL(10,2);
    DECLARE @new_cnum INT;
    DECLARE @old_amt DECIMAL(10,2);
    DECLARE @old_cnum INT;

    -- Get old values from 'deleted' table (limitation applies for multiple updated rows)
    SELECT @old_cnum = cnum, @old_amt = amt FROM deleted;

    -- Get new values from 'inserted' table (limitation applies for multiple updated rows)
    SELECT @new_cnum = cnum, @new_amt = amt FROM inserted;

    -- If the client number has changed (transaction reassigned)
    IF @old_cnum <> @new_cnum
    BEGIN
        -- Deduct old amount from old client's total
        UPDATE ClientAccounts
        SET total_transaction_amount = ISNULL(total_transaction_amount, 0) - @old_amt
        WHERE client_id = @old_cnum;

        -- Add new amount to new client's total
        UPDATE ClientAccounts
        SET total_transaction_amount = ISNULL(total_transaction_amount, 0) + @new_amt
        WHERE client_id = @new_cnum;
    END
    ELSE -- If only the amount changed for the same client
    BEGIN
        -- Adjust the total_transaction_amount by the difference
        UPDATE ClientAccounts
        SET total_transaction_amount = ISNULL(total_transaction_amount, 0) - @old_amt + @new_amt
        WHERE client_id = @new_cnum; -- or @old_cnum, as they are the same here
    END
END;
GO

How it works (Scalar Variable Method):

  • This trigger declares variables for both old (deleted) and new (inserted) values.
  • It attempts to retrieve these values into scalar variables. Again, this only works reliably for single-row UPDATE statements.
  • It then uses conditional logic (IF @old_cnum <> @new_cnum) to handle whether the client for the transaction has changed or just the amount. This logic is sound for a single row but fails for multi-row updates.

Let’s test it (Scalar Variable Method)!

(Temporarily drop any existing AFTER UPDATE triggers if needed.)

Assume ‘Hoffman Corp’ (2001) has a total that includes transaction 3012 (1500.00). ‘Giovanni Ltd’ (2002) also has an existing total.

  • Scenario 1: Update amount for an existing transaction (same client)

First, let’s find out the existing total_transaction_amount

SQL
-- Purpose: Verify total_transaction_amount for Client ID 2001 
SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id = 2001;

Output:

Now, execute below queries and find out the new result.

SQL
-- Update transaction 3012's amount for client 2001 from 1500.00 to 1800.00
UPDATE Transactions
SET amt = 1800.00
WHERE transaction_id = 3012;

SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id = 2001;
-- total_transaction_amount for Hoffman Corp should increase by 300 (1800 - 1500)

Output: Increase in total by 300 clearly visible.

  • Scenario 2: Update client for an existing transaction (reassign transaction)
SQL
-- Reassign transaction 3012 (now 1800.00 for client 2001) to client 2002
UPDATE Transactions
SET cnum = 2002
WHERE transaction_id = 3012;

SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id IN (2001, 2002);
-- Hoffman Corp's total should decrease by 1800.00
-- Giovanni Ltd's total should increase by 1800.00

Output:

Method 2: Joining with Virtual Tables (Recommended)

SQL
-- Purpose: Drop the scalar variable UPDATE trigger before creating the join-based one.
IF OBJECT_ID('trg_UpdateClientTotalOnUpdate_Scalar', 'TR') IS NOT NULL
    DROP TRIGGER trg_UpdateClientTotalOnUpdate_Scalar;
GO

-- Create a trigger to update total_transaction_amount after an update on Transactions
-- (Method 2: Joining with Virtual Tables - Recommended)
CREATE TRIGGER trg_UpdateClientTotalOnUpdate_Join
ON Transactions
AFTER UPDATE AS
BEGIN
    SET NOCOUNT ON;

    -- Step 1: Deduct the old transaction amounts from the respective client totals.
    -- We join ClientAccounts with the 'deleted' table (containing old values).
    UPDATE ca
    SET total_transaction_amount = ISNULL(ca.total_transaction_amount, 0) - d.amt
    FROM ClientAccounts ca
    INNER JOIN deleted d ON ca.client_id = d.cnum;

    -- Step 2: Add the new transaction amounts to the respective client totals.
    -- We join ClientAccounts with the 'inserted' table (containing new values).
    UPDATE ca
    SET total_transaction_amount = ISNULL(ca.total_transaction_amount, 0) + i.amt
    FROM ClientAccounts ca
    INNER JOIN inserted i ON ca.client_id = i.cnum;
END;
GO

How it works (Joining Method):

  • AFTER UPDATE: This trigger activates after an UPDATE statement on Transactions.
  • It uses both deleted (for old values) and inserted (for new values) in separate UPDATE statements.
  • The first UPDATE statement subtracts the amt from the deleted table (the value before the update) from the relevant client’s total.
  • The second UPDATE statement adds the amt from the inserted table (the value after the update) to the (possibly new) client’s total. This two-step process correctly handles both amount changes and client reassignments for any number of rows updated in a single statement. This is the correct and robust pattern for AFTER UPDATE triggers.

Let’s test it (Joining Method)!

(First, ensure trg_UpdateClientTotalOnUpdate_Scalar is dropped or disabled if it exists.)

As we are going to update two transaction for Clients 2001 and 2008, let’s first look into what total amount value is existing already with them. Execute the query below to find this out.

SQL
--fetch total transaction amount for client 2001, 2008
SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id IN (2001, 2008);

Now, let’s execute the update and fetch the result.

SQL
-- Update multiple transactions in one go (assuming trg_UpdateClientTotalOnUpdate_Join is active):
-- Update transaction 3001 (client 2008, amt 18.69) to amt 50.00
-- Update transaction 3003 (client 2001, amt 767.19) to amt 800.00
UPDATE Transactions
SET amt = CASE transaction_id
            WHEN 3001 THEN 50.00
            WHEN 3003 THEN 800.00
          END
WHERE transaction_id IN (3001, 3003);

SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id IN (2001, 2008);
-- Both clients' totals should be correctly updated based on the new amounts.

Output:

The AFTER DELETE Trigger: Cleaning Up After Removed Transactions

Finally, we’ll implement a trigger to accurately adjust the total_transaction_amount when a transaction is deleted from the Transactions table.

Method 1: Using Scalar Variables

SQL
-- Create a trigger for delete operation on Transactions table (Method 1: Using Scalar Variables)
CREATE TRIGGER trg_UpdateClientTotalOnDelete_Scalar
ON Transactions
AFTER DELETE AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @deleted_cnum INT;
    DECLARE @deleted_amt DECIMAL(10,2);

    -- Get the deleted values (limitation applies for multiple deleted rows)
    SELECT @deleted_cnum = cnum, @deleted_amt = amt FROM deleted;

    -- Deduct the deleted order amount from the client's total_transaction_amount
    UPDATE ClientAccounts
    SET total_transaction_amount = ISNULL(total_transaction_amount, 0) - @deleted_amt
    WHERE client_id = @deleted_cnum;
END;
GO

How it works (Scalar Variable Method):

  • Similar to the INSERT and UPDATE scalar variable methods, this retrieves values from the deleted table into variables.
  • It then uses these variables to perform the UPDATE.
  • Crucially, this method will only correctly process the last row if multiple rows are deleted in a single DELETE statement.

Let’s test it (Scalar Variable Method)!

(Temporarily drop any existing AFTER DELETE triggers if needed.)

Assume ‘Giovanni Ltd’ (2002) has a total that includes transaction 3009 (1713.23).

SQL
--fetch total transaction amount for client 2002
SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id = 2002;

Output:

Now, delete a single transaction:

SQL
--delete transaction with id 3009
DELETE FROM Transactions
WHERE transaction_id = 3009;

Check the client’s total again:

SQL
--fetch total transaction amount for client 2002
SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id = 2002;

Output: You should see that total_transaction_amount for ‘Giovanni Ltd’ has decreased by 1713.23!

Method 2: Joining with Virtual Tables (Recommended)

SQL
-- Drop the scalar variable trigger if it was created for testing
IF OBJECT_ID('trg_UpdateClientTotalOnDelete_Scalar', 'TR') IS NOT NULL
    DROP TRIGGER trg_UpdateClientTotalOnDelete_Scalar;
GO

-- Create a trigger to update total_transaction_amount after a delete from Transactions
-- (Method 2: Joining with Virtual Tables - Recommended and Aggregated)
CREATE TRIGGER trg_UpdateClientTotalOnDelete_Join
ON Transactions
AFTER DELETE AS
BEGIN
    SET NOCOUNT ON;

    -- Deduct the aggregated amount of the deleted transactions from the respective client totals.
    -- We join ClientAccounts with the 'deleted' table (containing the removed transaction's values).
    -- This method correctly handles single-row and multi-row deletes by summing the amounts.
    UPDATE ca
    SET ca.total_transaction_amount = ISNULL(ca.total_transaction_amount, 0) - d_agg.total_deleted_amt
    FROM ClientAccounts ca
    INNER JOIN (
        -- Subquery to aggregate the amounts from the 'deleted' pseudo-table
        SELECT
            cnum,
            SUM(amt) AS total_deleted_amt -- Sum all amounts for each client in the batch
        FROM deleted
        GROUP BY cnum -- Group by client number to get a sum per client
    ) AS d_agg ON ca.client_id = d_agg.cnum;
END;
GO

How it works (Joining Method):

  • AFTER DELETE: This trigger activates after a DELETE operation on Transactions.
  • It directly joins ClientAccounts with the deleted table.
  • Crucially, it uses a subquery with SUM(amt) GROUP BY cnum on the deleted table. This ensures that if multiple transactions for the same client are deleted in a single DELETE statement, the total_transaction_amount is correctly reduced by the sum of all those deleted amounts for that client.
  • This ensures that the total_transaction_amount is correctly adjusted for all clients whose transactions were deleted in a single DELETE statement.

Let’s test it (Joining Method)!

(First, ensure trg_UpdateClientTotalOnDelete_Scalar is dropped or disabled if it exists.)

Now, as we are going to delete two transaction, for client 2002 and 2006, let’s find out their existing total amount as below.

SQL
--fetch transaction amount for 2002, 2006
SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id IN (2002, 2006);

Output:

Now, delete the transactions by using below query and find out the result.

SQL
-- Delete multiple transactions in one go (assuming trg_UpdateClientTotalOnDelete_Join is active):
-- Delete transactions 3007 (client 2002, amt 75.75) and 3008 (client 2006, amt 4723.00)
DELETE FROM Transactions
WHERE transaction_id IN (3007, 3008);

--fetch transaction amount for 2002, 2006 again.
SELECT client_name, total_transaction_amount FROM ClientAccounts WHERE client_id IN (2002, 2006);
-- Both clients' totals should be correctly reduced based on the deleted amounts.

Output:

Now, as we have mentioned frequently about Dropping / Disabling the trigger, let’s see how to do that.

Dropping, Disabling, Enabling a Trigger

To manage triggers, you can either drop them (remove them permanently) or disable them (temporarily stop them from firing). Here are the instructions for both:

Dropping a Trigger

To permanently remove a trigger from your database, you use the DROP TRIGGER statement. Once dropped, the trigger is gone and cannot be easily recovered without re-creating it.

Syntax:

SQL
DROP TRIGGER [schema_name].[trigger_name];

Example:

If you want to remove the trg_UpdateClientTotalOnInsert_Scalar trigger we discussed, you would use:

SQL
DROP TRIGGER trg_UpdateClientTotalOnInsert_Scalar;
GO

Disabling and Enabling a Trigger

If you only want to temporarily prevent a trigger from firing without removing it, you can disable it. You can then re-enable it later when needed.

1. Disabling a Trigger

To disable a trigger, use the ALTER TABLE DISABLE TRIGGER statement.

Syntax:

SQL
ALTER TABLE [table_name] DISABLE TRIGGER [trigger_name];

Example:

To disable the trg_UpdateClientTotalOnInsert_Join trigger on the Transactions table:

SQL
ALTER TABLE Transactions DISABLE TRIGGER trg_UpdateClientTotalOnInsert_Join;
GO

2. Enabling a Trigger

To enable a disabled trigger, use the ALTER TABLE ENABLE TRIGGER statement.

Syntax:

SQL
ALTER TABLE [table_name] ENABLE TRIGGER [trigger_name];

Example:

To re-enable the trg_UpdateClientTotalOnInsert_Join trigger on the Transactions table:

SQL
ALTER TABLE Transactions ENABLE TRIGGER trg_UpdateClientTotalOnInsert_Join;
GO

Conclusion

SQL Triggers are an essential, advanced feature for building robust and reliable database systems. They provide a powerful way to automate responses to data modifications, ensuring that your data remains consistent, adheres to complex business rules, and supports critical auditing requirements.

We explored two primary methods for writing triggers: using scalar variables and joining with virtual tables. While the scalar variable approach might appear simpler for single-row operations, it has significant limitations when dealing with multi-row INSERT, UPDATE, or DELETE statements. The recommended and most robust approach is to directly join with the inserted and deleted virtual tables, as this ensures your triggers work correctly and efficiently for all types of DML operations.

We hope this deep dive into SQL Triggers has empowered you with the knowledge to bring more automation and integrity to your database operations. Stay tuned for our next blog, where we’ll continue to explore another fundamental building block of database programming: Stored Procedures and Rules!

Hi there 👋
It’s nice to see you.

Sign up to receive awesome content in your inbox, as soon as they gets posted!

This field is required.

We don’t spam! Read our privacy policy for more info.

Comments

Leave a Reply