数据库作业

1. inserted表和deleted表的结构

Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。

Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。

2. 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断言，用触发器代替

3. 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&