SQL笔记-触发器

超、凢脫俗 2023-10-12 12:12 12阅读 0赞

#### 一、基本语法 ####

*  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;

*  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());

*  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;

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

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

#### 二、示例 ####

假设有两个表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)
    );

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

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);

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

*  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;

*  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;

*  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;

*  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;