Basics of SQL Triggers.
Introduction to SQL Triggers
SQL Triggers are a powerful feature of relational databases that allow you to automatically perform actions in response to specific events or changes in the database. Think of them as "watchers" that monitor certain activities on your tables, such as INSERT, UPDATE, or DELETE operations, and automatically execute predefined SQL code when those events occur.
Key Features of SQL Triggers:
Automatic Execution: Triggers run automatically when a specified database event happens, reducing the need for manual intervention.
Types of Triggers:
BEFORE Trigger: Executes before an operation (INSERT, UPDATE, DELETE).
AFTER Trigger: Executes after an operation has completed.
INSTEAD OF Trigger: Executes in place of the triggering operation.
Event-Driven: Triggers are event-driven, meaning they respond to specific changes in the database.
Enforcing Data Integrity: Triggers can be used to ensure that data entered into a database adheres to certain rules or conditions, helping maintain the integrity of the data.
Complex Operations: They can execute complex logic like updating multiple tables, calling stored procedures, and performing calculations based on the data.
Types of Triggers in SQL
There are several types of triggers, classified by their timing (before, after, instead of) and the event (INSERT, UPDATE, DELETE). Below is a breakdown of the different types of triggers based on these categories.
1. DML Triggers (Data Manipulation Language Triggers)
DML triggers respond to changes in data (insertions, updates, or deletions) within a table. These triggers can be categorized into three types based on when they are fired:
a. AFTER Trigger
An AFTER Trigger is fired after a DML operation (INSERT, UPDATE, DELETE) has been successfully executed on a table. The trigger is typically used for actions like auditing, logging, or cascading operations.
Common Use Cases: Logging, cascading updates or deletes, sending notifications, etc.
Example:
CREATE OR REPLACE FUNCTION log_after_insert()
RETURNS TRIGGER AS $$
BEGIN
-- Insert a log entry into a log table after a new row is inserted
INSERT INTO log_table (action, description)
VALUES ('INSERT', 'A new row was added to the film table');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER film_after_insert
AFTER INSERT ON film
FOR EACH ROW
EXECUTE FUNCTION log_after_insert();
b. BEFORE Trigger
A BEFORE Trigger is fired before a DML operation (INSERT, UPDATE, DELETE) is executed on a table. It allows you to modify the data before it is actually inserted, updated, or deleted. You can use it to perform data validation or transformations.
Common Use Cases: Data validation, preventing invalid data insertion or modification, auto-generating values.
CREATE OR REPLACE FUNCTION prevent_invalid_update()
RETURNS TRIGGER AS $$
BEGIN
-- Prevent updates to the `release_year` if the new value is in the past
IF NEW.release_year < EXTRACT(YEAR FROM CURRENT_DATE) THEN
RAISE EXCEPTION 'Release year cannot be in the past';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER film_before_update
BEFORE UPDATE ON film
FOR EACH ROW
EXECUTE FUNCTION prevent_invalid_update();
c. INSTEAD OF Trigger
An INSTEAD OF Trigger replaces the original DML operation with the trigger's action. It is often used with views, where you can't perform DML operations directly on a view. Instead, the trigger allows you to specify how the underlying tables should be modified when an operation is performed on the view.
Common Use Cases: Performing DML operations on views or complex scenarios where you want to customize the behavior of DML actions.
CREATE TRIGGER instead_of_insert_on_view
ON film_view
INSTEAD OF INSERT
AS
BEGIN
-- Insert data into the underlying `film` table from the `film_view`
INSERT INTO film (film_id, title, release_year)
SELECT film_id, title, release_year
FROM INSERTED;
END;
2. DDL Triggers (Data Definition Language Triggers)
DDL triggers are used to respond to changes in the structure of the database itself, such as creating, altering, or dropping tables, views, and indexes. DDL triggers help enforce security policies, auditing, and prevent unwanted changes to the schema.
a. BEFORE DDL Trigger
A BEFORE DDL Trigger executes before the DDL operation is performed. This can be useful to stop certain schema changes from being executed.
Common Use Cases: Preventing the dropping of tables, modifying sensitive schema objects, auditing schema changes.
CREATE OR REPLACE TRIGGER prevent_drop_table
BEFORE DROP ON SCHEMA
BEGIN
IF (USER = 'ADMIN' AND OBJECT_NAME = 'sensitive_table') THEN
RAISE_APPLICATION_ERROR(-20001, 'Dropping the sensitive_table is not allowed');
END IF;
END;
b. AFTER DDL Trigger
An AFTER DDL Trigger is executed after the DDL operation has been performed. It can be used for auditing or automatically logging schema changes.
Common Use Cases: Auditing, logging schema changes, enforcing post-DML procedures after schema changes.
CREATE OR REPLACE TRIGGER log_ddl_operations
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN
INSERT INTO ddl_log (operation, object_name, timestamp)
VALUES (ORACLE_ERROR_MESSAGE, ORACLE_OBJECT_NAME, SYSDATE);
END;
Logon Triggers: Logon triggers are usually executed in response to a LOGON event. They are typically used to control or monitor user sessions, enforce logon policies, or log user activity. For example, a logon trigger can limit access to certain hours or log each user's login time and IP address.
Example
CREATE TABLE login_audit ( username VARCHAR2(50),
login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ip_address VARCHAR2(50) );
CREATE OR REPLACE TRIGGER logon_trigger AFTER LOGON ON DATABASE BEGIN INSERT
INTO login_audit (username, ip_address)
VALUES (USER, SYS_CONTEXT('USERENV', 'IP_ADDRESS')); END;
Trigger Operations:
We can perform different operations using triggers.
Operations in triggers typically refer to the actions performed when the trigger is executed.
Insert Operation – Executes when new rows are added.
Update Operation – Executes when existing rows are modified.
Delete Operation – Executes when rows are removed.
1. Insert Operation (AFTER INSERT Trigger):
This trigger runs after a new row is inserted into the orders table. It logs the insertion into a audit_log table.
Example:
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO audit_log (action, table_name, record_id, action_date)
VALUES ('INSERT', 'orders', NEW.order_id, NOW());
END;
This trigger fires after an INSERT operation on the orders table.
It adds a new record to the audit_log table, recording the order_id of the newly inserted row.
2. Update Operation:(AFTER UPDATE Trigger):
This trigger fires after an UPDATE operation on the employees table to ensure that the salary is being updated .
Example:
CREATE TRIGGER log_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_log (employee_id, name, action)
VALUES (OLD.employee_id, OLD.name, 'updated');
END;
3. Delete Operation (AFTER DELETE Trigger)
This trigger executes after a record is deleted from the products table, and it logs the deletion to an audit_log table.
CREATE TRIGGER after_product_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
INSERT INTO audit_log (action, table_name, record_id, action_date)
VALUES ('DELETE', 'products', OLD.product_id, NOW());
END;
This trigger fires after a row is deleted from the products table.
It logs the product_id of the deleted row in the audit_log.
We can drop trigger.
Syntax:
DROP TRIGGER IF EXISTS log_changes;
This ensures the trigger is no longer active and won't execute its defined actions.
Keep learning!!!!!!!!!!!!!!!!!!!!!!!
Thank you.
