数据库SQL语句--触发器上机练习（2）

create database teaching;

Create table teacher
( 
    tno int primary key,  --工号           
    tname nvarchar(6),    --姓名
    prof_title  nvarchar(10), --职称
    salary int  --薪水         
);
create table salary_level
( 
  prof_title nvarchar(10) primary key , --职称
  minsalary int,   --薪水最小值
  maxsalary int    --薪水最大值
);

insert into salary_level values('教授',7000,8900)
insert into salary_level values('副教授',5800,7200)
insert into  salary_level values('讲师',4500,5900)
insert into  salary_level values('助教',3900,4900)

select * from teacher;
select * from salary_level;

--1、在教师表上创建一个触发器，用于实现复杂的约束：
--在对教师的工资进行录入和修改时，按职称级别进行约束。
go
create trigger addsalary1
on teacher
for insert, update
as
	declare @mytname nvarchar(10),@myprof_title nvarchar(10)
	declare @mysalary int,@mymin int,@mymax int
	select @myprof_title=salary_level.prof_title,@mysalary=inserted.salary,@mymin=minsalary,@mymax=maxsalary,@mytname=inserted.tname
	from inserted,salary_level
	where inserted.prof_title=salary_level.prof_title
	if not (@mysalary is null or @mysalary between @mymin and @mymax)
	begin
		print @mytname+'的职称为:'+@myprof_title+'。工资应该在'+str(@mymin)+' 到'+ str(@mymax)+'之间。'
		rollback
	end
--teacher表中的prof-titile要和insertde表中的相同,然后比较大小
--触发：
insert into teacher values(1,'郑浩','教授',6800,null)
insert into teacher values(3,'啊平','讲师',3,null)
 

--2、在教师表上创建一个触发器，在对教师的工资进行录入和修改时，按职称级别进行限制
go
create trigger t1
on teacher
after insert
as
declare
@min int,
@max int,
@salary int
set @min=(select minsalary from salary_level where prof_title in (select prof_title from inserted))
set @max=(select maxsalary from salary_level where prof_title in (select prof_title from inserted))
set @salary=(select salary from inserted)
if not (@salary is not null or @salary between @min and @max)
begin
	print '工资不合理'
	rollback
end