SQL笔记-触发器

一、基本语法

• Oracle触发器使用方法：创建触发器时需要指定触发时间（BEFORE或AFTER）和触发事件（INSERT、UPDATE或DELETE），并编写相应触发器程序。触发器程序可以是PL/SQL块或调用存储过程。例如创建一个在插入员工表时自动插入一条记录到日志表的触发器：

CREATE TRIGGER log_employee
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employee_log (employee_id, hire_date)
  VALUES (:NEW.employee_id, SYSDATE);
END;
1
2
3
4
5
6
7

• MySQL触发器使用方法：创建触发器时需要指定触发时间（BEFORE或AFTER）和触发事件（INSERT、UPDATE或DELETE），并编写相应触发器程序。触发器程序可以是SQL语句或存储过程。例如创建一个在插入员工表时自动插入一条记录到日志表的触发器：

CREATE TRIGGER log_employee
AFTER INSERT ON employees
FOR EACH ROW
INSERT INTO employee_log (employee_id, hire_date)
VALUES (NEW.employee_id, NOW());
1
2
3
4
5

• SQL Server触发器使用方法：创建触发器时需要指定触发时间（AFTER）和触发事件（INSERT、UPDATE或DELETE），并编写相应触发器程序。触发器程序可以是T-SQL语句或调用CLR存储过程。例如创建一个在插入员工表时自动插入一条记录到日志表的触发器：

CREATE TRIGGER log_employee
ON employees
AFTER INSERT
AS
BEGIN
  INSERT INTO employee_log (employee_id, hire_date)
  SELECT employee_id, GETDATE() FROM inserted;
END;
1
2
3
4
5
6
7
8

• PostGreSQL触发器使用方法：创建触发器时需要指定触发时间（BEFORE或AFTER）和触发事件（INSERT、UPDATE或DELETE），并编写相应触发器程序。触发器程序可以是PL/pgSQL函数或任意其他支持的语言。例如创建一个在插入员工表时自动插入一条记录到日志表的触发器：

CREATE TRIGGER log_employee
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE PROCEDURE log_employee_func();
1
2
3
4

二、示例

假设有两个表employee和department，其中employee表包含员工信息，department表包含部门信息，它们的建表语句如下：

CREATE TABLE department (
  dept_id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  name VARCHAR(50),
  dept_id INT,
  salary INT,
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);
1
2
3
4
5
6
7
8
9
10
11
12

为了演示触发器的多表增删改，我们向这两个表插入一些数据：

INSERT INTO department VALUES (1, 'Sales');
INSERT INTO department VALUES (2, 'Marketing');
INSERT INTO department VALUES (3, 'Finance');

INSERT INTO employee VALUES (1, 'Alice', 1, 5000);
INSERT INTO employee VALUES (2, 'Bob', 1, 6000);
INSERT INTO employee VALUES (3, 'Charlie', 2, 7000);
INSERT INTO employee VALUES (4, 'David', 3, 8000);
INSERT INTO employee VALUES (5, 'Eve', 3, 9000);
1
2
3
4
5
6
7
8
9

接下来，我们分别演示触发器的多表增删改：

• Oracle触发器多表增删改：

CREATE TRIGGER update_dept_salary
AFTER INSERT OR UPDATE OR DELETE ON employee
DECLARE
  v_dept_id department.dept_id%TYPE;
BEGIN
  IF INSERTING OR UPDATING THEN
    v_dept_id := :NEW.dept_id;
  ELSE
    v_dept_id := :OLD.dept_id;
  END IF;
  UPDATE department d
  SET total_salary = (
    SELECT SUM(salary)
    FROM employee e
    WHERE e.dept_id = d.dept_id
  )
  WHERE d.dept_id = v_dept_id;
END;
/

INSERT INTO employee VALUES (6, 'Frank', 1, 7000);

UPDATE employee SET salary = 8000 WHERE emp_id = 1;

DELETE FROM employee WHERE emp_id = 2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

• MySQL触发器多表增删改：

CREATE TRIGGER update_dept_salary
AFTER INSERT OR UPDATE OR DELETE ON employee
FOR EACH ROW
BEGIN
  UPDATE department d
  SET total_salary = (
    SELECT SUM(salary)
    FROM employee e
    WHERE e.dept_id = d.dept_id
  )
  WHERE d.dept_id = NEW.dept_id OR d.dept_id = OLD.dept_id;
END;

INSERT INTO employee VALUES (6, 'Frank', 1, 7000);

UPDATE employee SET salary = 8000 WHERE emp_id = 1;

DELETE FROM employee WHERE emp_id = 2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

• SQL Server触发器多表增删改：

CREATE TRIGGER update_dept_salary
ON employee
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  UPDATE department
  SET total_salary = (
    SELECT SUM(salary)
    FROM employee
    WHERE employee.dept_id = department.dept_id
  )
  WHERE department.dept_id IN (
    SELECT dept_id FROM inserted
    UNION SELECT dept_id FROM deleted
  );
END;

INSERT INTO employee VALUES (6, 'Frank', 1, 7000);

UPDATE employee SET salary = 8000 WHERE emp_id = 1;

DELETE FROM employee WHERE emp_id = 2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

• PostGreSQL触发器多表增删改：

CREATE FUNCTION update_dept_salary_func()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE department d
  SET total_salary = (
    SELECT SUM(salary)
    FROM employee e
    WHERE e.dept_id = d.dept_id
  )
  WHERE d.dept_id = NEW.dept_id OR d.dept_id = OLD.dept_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_dept_salary
AFTER INSERT OR UPDATE OR DELETE ON employee
FOR EACH ROW
EXECUTE PROCEDURE update_dept_salary_func();

INSERT INTO employee VALUES (6, 'Frank', 1, 7000);

UPDATE employee SET salary = 8000 WHERE emp_id = 1;

DELETE FROM employee WHERE emp_id = 2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24