- inserted表和deleted表的结构
Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中,新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。
Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时,行从触发器表中删除,并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。
- 173页第6题
create database worker_class;
use worker_class;
create table work(
work_num char(10) primary key,
work_name char(10),
manager char(10),
phone char(11)
);
create table calss(
calss_num char(10) primary key,
name char(10),
age int ,
duty char(10),
salary int(10),
worker_num char(10),
check(age between 0 and 60),
foreign key (work_num) references work(work_num)
);
create unique index in_name on class(name);
alter table workadd constraint fk_name foreign key (manager) references calss(name) ;
delimiter // //修改结束符为 //
//当对staff表进行插值时触发
create trigger check_age
after insert on calss
for each row
begin
declare age int;
set age=new.age;
if age>60
then
delete from class where age>60;
end if;
end&
//mysql中无法使用check断言,用触发器代替
- 173页第8题
create database fraternity;
use fraternity;
create table m_guest(
m_num char(10) primary key, //男士id
name char(10)
);
create table f_guest(
f_num char(10) primary key, //女士id
name char(10)
);
delimiter // //修改结束符为//
//当对m_guest表进行插值时触发
create trigger check_mnum
after insert on m_guest
for each row
begin
declare f_num0 int;
declare m_num0 int;
set f_num0=(select count(*) from f_guest);
set m_num0=(select count(*) from f_guest);
if (f_num0+m_num0)>50
then
delete from m_guest where m_guest.m_num=new.m_num;
end if;
end&
//当对f_guest表进行插值时触发
create trigger check_num
after insert on f_guest
for each row
begin
declare f_num0 int;
declare m_num0 int;
set f_num0=(select count(*) from f_guest);
set m_num0=(select count(*) from f_guest);
if (f_num0+m_num0)>50
then
delete from f_guest where m_guest.f_num=new.f_num;
end if;
end&