How do you use the CREATE TRIGGER statement to create a DML trigger?
LeoRobs
Posted by LeoRobs
Last Updated: June 23, 2024
SQL Trigger
In SQL, a DML (Data Manipulation Language) trigger is a database object that automatically executes or fires when a specific data manipulation event occurs on a table, such as an INSERT, UPDATE, or DELETE operation. The CREATE TRIGGER statement is used to create such triggers in various database management systems like MySQL, PostgreSQL, and SQL Server, but the syntax may differ slightly among them. Here is a general structure for creating a DML trigger:
Syntax for MySQL
CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
BEGIN
    -- Your trigger logic here
END;
Example in MySQL
Suppose we have a table called employees and we want to create a trigger that logs changes made to the salary column whenever an UPDATE operation occurs: 
CREATE TRIGGER salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date)
        VALUES (NEW.id, OLD.salary, NEW.salary, NOW());
    END IF;
END;
Syntax for SQL Server
In SQL Server, the syntax is as follows: 
CREATE TRIGGER trigger_name
ON table_name
{ FOR | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE }
AS
BEGIN
    -- Your trigger logic goes here
END;
Example in SQL Server
Here’s an example of a trigger in SQL Server that performs the same function as above: 
CREATE TRIGGER trgSalaryUpdate
ON employees
AFTER UPDATE
AS
BEGIN
    IF UPDATE(salary)
    BEGIN
        INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date)
        SELECT id, deleted.salary, inserted.salary, GETDATE()
        FROM deleted
        JOIN inserted ON deleted.id = inserted.id
        WHERE deleted.salary <> inserted.salary;
    END
END;
Syntax for PostgreSQL
In PostgreSQL, the trigger needs to be associated with a function: 
CREATE FUNCTION function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- Your trigger logic here
    RETURN NEW; -- or RETURN OLD; depending on the context
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();
Example in PostgreSQL
CREATE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary IS DISTINCT FROM NEW.salary THEN
        INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date)
        VALUES (NEW.id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();
Summary
- Define the trigger: Use the CREATE TRIGGER statement. - Specify the timing: Use BEFORE or AFTER to specify when the trigger should fire. - Specify the event: Use INSERT, UPDATE, or DELETE to specify which type of operation should activate the trigger. - Define the trigger logic: Write the SQL commands that should be executed when the trigger is fired. - Remember: DML triggers can have performance implications and should be used judiciously, especially in high-frequency transaction environments.
Default Image