Creating a Trigger for Cascading Updates

Create a Trigger for Cascading Updates

Write a SQL query to create a trigger that cascades updates from one table to another.

Solution:

-- Create a trigger to cascade updates.
CREATE TRIGGER trg_CascadeDepartmentNameUpdate
ON Departments -- Specify the table to monitor.
AFTER UPDATE -- Trigger fires after an update operation.
AS
BEGIN
    -- Update related employees when a department name changes.
    UPDATE Employees
    SET DepartmentName = i.DepartmentName
    FROM inserted i
    WHERE Employees.DepartmentID = i.DepartmentID;
END;

Explanation:

1. Purpose of the Query :

1. The goal is to create a trigger that automatically updates related tables when a change occurs in the parent table.

2. Key Components :

1. CREATE TRIGGER : Defines the trigger.
2. AFTER UPDATE : Specifies when the trigger should fire.
3. inserted Table : Tracks the new values after an update.

3. Why Use Cascading Updates? :

1. Cascading updates ensure data consistency across related tables without requiring manual intervention.

4. Real-World Application :

1. For example, in organizational databases, you might use this trigger to update department names across all related employee records.

Additional Notes:

• Cascading updates reduce manual effort but should be used carefully to avoid unintended side effects.
• Use this exercise to demonstrate the power of triggers in maintaining referential integrity.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a trigger that cascades updates from a parent table to multiple child tables.
• Write a SQL query to create a trigger that synchronizes data between two related tables whenever a record is updated in the primary table.
• Write a SQL query to create a trigger that propagates changes from one table to another table in a different database.
• Write a SQL query to create a trigger that updates summary statistics in a reporting table whenever data in the source table changes.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Create a Scalar Function with Conditional Logic.
Next SQL Exercise: Create a Stored Procedure with Dynamic SQL.