With the help of SQL triggers, you can set up automatic batch SQL code execution for when particular events take place in your database. They are especially helpful for tracking modifications to your database tables, automating administrative activities, and preserving data integrity. This article will explain SQL triggers and provide an example of how to use them to log changes made to a product table into a product_log table, allowing you to follow such changes.
An SQL Trigger: What Is It?
A SQL trigger is a unique kind of stored procedure that is "triggered"—or automatically carried out—in response to specific events on a given database or view. INSERT, UPDATE, and DELETE actions are examples of these events. Triggers facilitate the automation of processes including updating relevant data, enforcing rules, and logging.
Components of a SQL Trigger
- Trigger Name: A unique name to identify the trigger.
- Trigger Timing: Specifies when the trigger should fire (e.g., AFTER, BEFORE).
- Trigger Event: The database operation that activates the trigger (INSERT, UPDATE, DELETE).
- Trigger Action: The SQL code that is executed when the trigger is fired.
Example Scenario
Suppose we have a table called [inventory].[products] where product details are stored. We want to track all changes made to this table by logging these changes into a product_log table.
Here’s how you can set this up.
Step 1. Create the Log Table
First, create a table to store the log entries. This table will capture the details of any changes made to the products table.
CREATE TABLE inventory.product_log (
log_id INT IDENTITY(1,1) PRIMARY KEY,
action_type NVARCHAR(50),
product_id INT,
product_name VARCHAR(255),
quantity INT,
price DECIMAL(18, 2),
change_date DATETIME DEFAULT GETDATE()
);
log_id: A unique identifier for each log entry.
action_type: Describes the type of action that occurred (INSERT, UPDATE).
product_id: The ID of the product that was changed.
product_name: The name of the product.
quantity: The quantity of the product.
price: The price of the product.
change_date: The date and time when the change occurred.
Step 2. Create the Trigger
Next, create a trigger that logs changes to the products table.
CREATE TRIGGER trg_productLog
ON inventory.products
AFTER INSERT, UPDATE
AS
BEGIN
-- Log the inserted records
INSERT INTO inventory.product_log (
action_type,
product_id,
product_name,
quantity,
price
)
SELECT
CASE
WHEN EXISTS (SELECT * FROM inserted i WHERE i.product_id IS NOT NULL)
THEN 'INSERT'
ELSE 'UPDATE'
END AS action_type,
i.product_id,
i.product_name,
i.quantity,
i.price
FROM inserted i;
END;
AFTER INSERT, UPDATE: The trigger will fire after a row is inserted or updated in the products table.
INSERTED Table: This system-defined table contains the new or updated rows from the products table.
action_type: Determines whether the action was an INSERT or UPDATE.
Step 3. Test the Trigger
To test the trigger, insert or update a row in the products table.
-- Insert a new product
INSERT INTO inventory.products
(product_id, product_name, quantity, price)
VALUES
(1, 'Product A', 100, 19.99);
-- Update an existing product
UPDATE inventory.products
SET quantity = 150,
price = 17.99
WHERE product_id = 1;
After running these statements, check the product_log table to see the log entries.
SELECT *
FROM inventory.product_log;
Conclusion
When it comes to automating and monitoring changes in your database, SQL triggers are indispensable. You may create a trigger that logs changes made to a table by following the above instructions. This will improve your ability to manage and audit your database by keeping a clear record of any data alterations.
HostForLIFEASP.NET SQL Server 2022 Hosting
