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:
- The goal is to create a trigger that automatically updates related tables when a change occurs in the parent table.
- CREATE TRIGGER : Defines the trigger.
- AFTER UPDATE : Specifies when the trigger should fire.
- inserted Table : Tracks the new values after an update.
- Cascading updates ensure data consistency across related tables without requiring manual intervention.
- For example, in organizational databases, you might use this trigger to update department names across all related employee records.
1. Purpose of the Query :
2. Key Components :
3. Why Use Cascading Updates? :
4. Real-World Application :
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.
Go to:
PREV : Create a Scalar Function with Conditional Logic.
NEXT : Create a Stored Procedure with Dynamic SQL.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.