数据库浅谈之触发器

一.触发器使用的场景

 

1.当某些数据库对象发生改变时(例如对某张表进行insert、update、delete操作)，需要自动执行相关的动作。

 

-- 当表t_test_students的数据发生变化时，启动触发器更新t_test_major_stats的数据。

CREATE OR REPLACE TRIGGER UpdateMajorStats
  AFTER INSERT OR DELETE OR UPDATE ON t_test_students

DECLARE
  CURSOR c_Statistics IS
    SELECT major, COUNT(1) total_students, SUM(current_credits) total_credits FROM t_test_students GROUP BY major;

BEGIN

  DELETE FROM t_test_major_stats;

  FOR v_Statistics IN c_Statistics LOOP
    INSERT INTO t_test_major_stats
      (major, total_credits, total_students)
    VALUES
      (v_Statistics.major, v_Statistics.total_credits, v_Statistics.total_students);
  END LOOP;

END UpdateMajorStats;