触发器
1:创建触发器
1:创建触发器的执行函数
create or replace function student_delete_trigger()
returns trigger as $$
delete from score where student_no=OLD.student_no;
return OLD;
end;
$$
language plpgsql;
2:创建触发器
create trigger delete_student_trigger
after delete on student
for each row execute procedure student_delete_trigger();
示例:
1:创建学生表和成绩表,触发器实现:当删除学生表中学生信息时,自动删除成绩表中该学生的信息。
create table student(student_no int primary key,student_name varchar(40),age int);
create table score (student_no int,chinese_score int,math_score int,test_date date);
2:向两表中插入数据:
insert into student values(1,'张三',14);
insert into student values(2,'李四',15);
insert into student values(3,'王二',14);
insert into score values(1,98,87, date '2023-09-08');
insert into score values(2,65,98, date '2020-09-23');
insert into score values(3,76,87,date '2022-01-23');
3:查看两个表中数据:
4:删除student学号3的学生信息:
5:score表中信息也被删除,触发器生效