plsql learning - five:trigger

最新推荐文章于 2022-06-07 11:30:56 发布

原创最新推荐文章于 2022-06-07 11:30:56 发布 · 159 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#C #C++ #C#

DataBase 专栏收录该内容

14 篇文章

订阅专栏

本文介绍了一种使用触发器在执行插入、删除或更新操作后更新统计数据的方法。通过创建一个触发器，在students表上进行DML操作时，该触发器会自动更新major_stats表中的统计数据。

When we want to update the info of some important tables,we can use the trigger,it can be triggered after we use some DML for some tables.here is the example of it:

create or replace trigger UpdateMajorStats
  after insert or delete or update on students
declare
  cursor c_Statistics is
    select major,count(*) total_students,sum(current_credits) total_credits
    from students 
    group by major;
begin
  delete from major_stats;
  for v_StatsRecord in c_Statistics loop
    insert into major_stats(major,total_credits,total_students)
    values(v_StatsRecord.major,v_StatsRecord.total_credits,v_StatsRecord.total_students);
  end loop;
end UpdateMajorStats;

then test it:

1.select * from major_stats;
      Computer Science 22 3
      Economics 15 2
      History 12 3
      Music 11 2
      Nutrition 16 2

2.insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'tang','lei','CS',10)

3.select * from major_stats;
    CS 10 1
    Computer Science 22 3
    Economics 15 2
    History 12 3
   Music 11 2
   Nutrition 16 2

so can got a conclusion,the first row updated in this table by trigger.