In SQL, a view is a virtual table based on the result set of a SELECT statement. An INSTEAD OF trigger is used to define actions that should take place instead of the default actions when certain DML operations (like INSERT, UPDATE, or DELETE) are attempted on a view.
Here's how you can create a view and an INSTEAD OF trigger that responds to an insert operation on that view:
Example Scenario
Assume we have two tables, employees and departments, and we want to create a view called employee_info that displays employee names with their department names. We also want an INSTEAD OF trigger to insert records into the employees table when an insert is attempted on the employee_info view.
Step 1: Create the Tables
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
Step 2: Create the View
We'll create a view employee_info that combines data from the employees and departments tables.
CREATE VIEW employee_info AS
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
Step 3: Create the INSTEAD OF Trigger
Now we will create an INSTEAD OF trigger for the employee_info view. This trigger will specify how to handle insert operations.
CREATE TRIGGER trg_instead_of_insert
INSTEAD OF INSERT ON employee_info
FOR EACH ROW
BEGIN
-- Assume department_name is provided in the insert statement
DECLARE dept_id INT;
-- Get the department_id corresponding to the department_name
SELECT department_id INTO dept_id
FROM departments
WHERE department_name = NEW.department_name;
-- Insert into the employees table
INSERT INTO employees (employee_name, department_id)
VALUES (NEW.employee_name, dept_id);
END;
Explanation
1. View Creation: The view employee_info combines employee names with their respective department names from the base tables.
2. Trigger Creation: The INSTEAD OF trigger is created to handle insert operations on the employee_info view:
- The trigger captures the new row being inserted.
- It looks up the department_id based on the provided department_name.
- Finally, it inserts the new employee into the employees table with the resolved department_id.
Usage
You can now insert a new employee using the view:
INSERT INTO employee_info (employee_name, department_name)
VALUES ('John Doe', 'Sales');
Instead of throwing an error or performing a direct insert operation on the view, the trigger will handle the operation as defined, inserting the necessary data into the employees table.
Notes
- Make sure that the department_name exists in the departments table before executing the insert; otherwise, the trigger may fail.
- The INSTEAD OF trigger allows you to intercede in the operation, making it a powerful tool for encapsulating complex logic related to data modifications.