In SQL, triggers are special kinds of stored procedures that are automatically executed in response to certain events on a particular table or view. The ROLLBACK statement is used to undo changes made in a transaction, but it's important to note that you cannot directly use a ROLLBACK statement inside a trigger.
Instead, you can control the flow of operations within a trigger using techniques that effectively prevent changes from being committed based on certain conditions.
How to Handle Rollbacks in a Trigger
If you want to prevent changes made by a trigger based on specific conditions, the common approach is to raise an error. When an error is raised in a trigger, it will automatically roll back the transaction that fired the trigger.
Here is an example of how you can implement this behavior:
Example: Preventing Insertions with a Trigger
Suppose you have a table employees and you want to ensure that no employee can have a salary greater than 100,000. If someone tries to insert an employee record with a salary above this threshold, you want to prevent the insertion and roll back the transaction.
Here's how you can do that:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2)
);
CREATE OR REPLACE FUNCTION check_salary()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.salary > 100000 THEN
RAISE EXCEPTION 'Salary cannot exceed 100,000';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary();
Explanation:
1. Table Creation: A table employees is created with columns for ID, name, and salary.
2. Function: A trigger function check_salary is defined using PL/pgSQL (PostgreSQL's procedural language):
- It checks if the new salary (the salary of the employee being inserted) exceeds 100,000.
- If it does, it raises an exception with a custom error message.
3. Trigger Creation: A trigger named salary_check_trigger is created:
- It fires before an insert operation on the employees table.
- It calls the check_salary function for each row being inserted.
Result Behavior:
- If an insert operation tries to add an employee with a salary greater than 100,000, the trigger's function will raise an exception.
- This causes the entire transaction to roll back, and no data will be added to the employees table.
Alternatives
For SQL implementations that do not support triggers, you can achieve similar functionality by checking conditions in stored procedures that handle your insert logic.
Conclusion
In summary, while you cannot explicitly use ROLLBACK inside a trigger, you can control the outcome by raising exceptions, which will automatically roll back the transaction in the context of the trigger's execution.