完整性约束

 

触发器

--1、使用DDL触发器limited来防止数据库中的任一表被修改或删除(自定义错误提示)。????
create trigger limited
on database
for update
as
print'不允许删除操作!'
rollback
--2、为学生表创建一个简单DML触发器，在插入和修改数据时，都会自动显示提示信息。
create trigger reminder
on student
for insert, update
as print'你在插入或修改学生表的数据'
--触发条件
insert
into student(sno)
values(3333333)
--3、为学生表创建一个DML触发器，在插入和修改数据时，都会自动显示所有学生的信息。
create trigger print_table1
on course
for insert, update
as
select *
from course
--触发条件
update course
set cname = '英语'
where cno = 1
--4、在学生表上创建一个DELETE类型的触发器，删除数据时，显示删除学生的个数。
create trigger del_count
on student
for delete
as
declare @count varchar(50)
set @count = str(@@ROWCOUNT) + '个学生被删除'
select @count
return
--触发条件
delete
from student
where sno = 123
--补充：
--INSERTED表保存了INSERT操作中新插入的数据和UPDATE操作中更新后的数据；
--DELETED保存了DELETE操作删除的数据和UPDATE操作中更新前的数据。

--先创建一个“dept_project”数据库（属性默认），和两个表dept和project，其结构如下
create database dept_project
drop database dept
create table dept
(
dno char(5),
dname nvarchar(20),
leader nvarchar(10)
primary key(dno)
)
create table project
(
pno char(5) primary key,
pname char(20),
leader char(10)
)
insert into dept values(1,'小型工程部','张三') Insert into dept values(2,'中型工程部','李斯') Insert into dept values(3,'大型工程部','王五')
Insert into project values(1,'阳光小区','张三')
Insert into project values(2,'华茂小区','张三')
Insert into project values(3,'世纪公寓','李斯')
Insert into project values(4,'淡蓝商务','王五')
Insert into project values(5,'冰海广场','王五')

--5、为dept表创建一个名为d_tr的触发器，当执行添加、更新或删除时，激活该触发器。通过此例，了解Inserted表和Deleted表的功能。
create trigger d_p
on dept
for insert, update, delete
as
select *
from inserted
select *
from deleted

update dept
set dname = '大型工程一部' where dname = '大型工程部'
--6、为project表创建一个名为g_tr的触发器，实现参照完整性（级联问题）。
create trigger p_tr
on project
for insert, update
as
if not exists
(
select *
from dept
where leader =
(
select leader
from inserted
)
)
begin
declare @lead varchar(10)
set @lead =
(
select leader
from inserted
)
print'你在project表中要插入的记录，在dept表中不存在这样的负责人：' + @lead
rollback
end
--触发
insert
into project
values('6', '度假村', '张力')
--7、为创建一个名为d_tr1的实现级联更新的update触发器。当更新dept表(leader列)时，激活该触发器同时更新project表中相应记录。
create trigger d_tr1
on dept
for update
as
update project
set leader = (select leader from inserted)
where leader = (select leader from deleted)
--触发
update dept
set leader = '张学'
where dname = '中型工程部'--这里只能返回一个元组
--select *
--from dept
--select *
--from project
--8、
--在教学库创建一个教师表，创建一个教师工资表
create database teacher
create table teacher
(
tno int primary key,
tname char(10),
title char(10)
)
create table salary
(
tno int primary key
foreign key references teacher(tno),
tname char(10),
salary int
)
insert
into teacher
values(1, 'a', '教授')
insert
into teacher
values(2, 'b', '副教授')
insert
into teacher
values(3, 'c', '讲师')
--创建一个工资级别表
create table salary_level
(
title char(10),
minn int,
maxx int,
primary key(title)
)
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)
--在教师工资表上创建一个触发器，用于实现复杂的约束：在对教师的工资进行录入和修改时，按职称级别进行约束。
alter trigger check_salary
on salary
for update, insert
as
declare @tno int, @salary int
declare @name char(10), @title char(10), @minn int, @maxx int
select @tno = tno, @salary = salary
from inserted
if not exists
(
select *
from teacher, salary_level
where teacher.title = salary_level.title and teacher.tno = @tno and @salary between minn and maxx
)
begin
print 'error'
print @tno + 'error'
rollback
end
--[例9]在教师工资表上创建一个触发器，用于实现特殊的约束：规定每月的10号前发工资，即对教师的工资进行录入时，触发此触发器，时间不对不能录入。
alter trigger teacher_salary
on salary
for insert, update
as
declare @d int
set @d = day(getdate())
if @d > 10
begin
print('errorrr')
rollback
end
drop teacher_salary
--触发
insert
into salary
values(2, '王伟', 5700)
update salary
set salary = 10000
where tno = 1
--[例10] 触发器中调用存储过程。
alter proc p1
as
select *
from teacher
create trigger tr1 on teacher
for insert, update, delete
as exec p1

insert
into teacher
values(5, '刘文涵', '博导')