数据库完整性，触发器

  本章主要介绍一下数据库完整性：实体，参照，用户自定义完整性

  数据库的完整性是指数据的正确性和相容性。数据库的正确性是指数据是符合现实世界语义、反映当前实际情况的；数据的相容性是指数据库同一对象在不同关系表中的数据是符合逻辑的。

一般数据库管理系统必须能够实现如下功能：

1.提供定义完整性约束条件的机制

2.提供完整性检查的方法

3.进行违约处理

一、实体完整性

1.定义实体完整性

【例 5.1】将Student表中的Sno属性定义为主码。

create table Student
	(
	Sno    char(9)  primary key,
	Sname  char(20) not null,     
	Ssex   char(2),
	Sage   smallint,
	Sdept  char(20)
	);
--或者
create table Student
	(
     Sno    char(9),  
	 Sname  char(20) not null,
	 Ssex   char(2),
	 Sage   smallint,
	 Sdept  char(20),
	 primary key(Sno)
	);

【例 5.2】 将SC表中的Sno，Cno属性组定义为码。

create table SC
	(
	Sno   char(9)  not null, 
	Cno   char(4)  not null,  
	Grade smallint,
	primary key(Sno,Cno)    
	); 

2.实体完整性检查和违约处理

  用primary key短语定义了关系的主码后，每当用户程序对基本表插入一条记录或对主码列进行更新操作时，关系数据库管理系统将按照实体完整性规则自动进行检查。包括：

（1）检查主码值是否唯一，如果不唯一则拒绝插入或修改。

（2）检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。，从而保证了实体完整性。

insert 
into Student
values('201912121','李刚','男',20,'CS');

简单验证啦一下，没什么问题，SC表同理。

 
二、参照完整性

1.定义参照完整性

  关系模型的参照完整性create table中用foreign key短语定义哪些列为外码，用references短语指明这些外码参照哪些表的主码。

   例：关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）为主码，Sno、Cno分别参照引用Student表的主码和Course表的主码。定义SC表中的参照完整性

【例 5.3】定义SC中的参照完整性。

drop table if exists SC;
create table SC
	(
	Sno    char(9)  not null, 
	Cno    char(4)  not null,  
	Grade  smallint,
	primary key (Sno, Cno),						/*在表级定义实体完整性*/
	foreign key (Sno) references Student(Sno),  /*在表级定义参照完整性*/
	foreign key (Cno) references Course(Cno)   	/*在表级定义参照完整性*/
	);

2.参照完整性检查和违约处理。

  当对照表和参照表进行增，删，改操作时有可能破坏参照完整性。

  一般的，当对表的操作违反啦参照完整性，系统会默认策略，即拒绝执行。但是也可以让系统采取其他策略，在创建参照表的时候显式地说明。

【例 5.4】显式说明参照完整性的违约处理示例。

CREATE TABLE SC
        (Sno   CHAR(9)  NOT NULL,
         Cno   CHAR(4)  NOT NULL,
         Grade SMALLINT,
         PRIMARY KEY(Sno,Cno), 			
         FOREIGN KEY (Sno) REFERENCES Student(Sno) 
		     ON DELETE CASCADE        /*级联删除SC表中相应的元组*/
             ON UPDATE CASCADE,      /*级联更新SC表中相应的元组*/
         FOREIGN KEY (Cno) REFERENCES Course(Cno)	                    
             ON DELETE NO ACTION 	
          /*当删除Course 表中的元组造成了与SC表不一致时拒绝删除*/
             ON UPDATE CASCADE   
      	  /*当更新Course表中的Cno时，级联更新SC表中相应的元组*/
        );

 
三、用户自定义完整性

1.属性上约束条件的定义

  create table中定义属性的同时，可以根据应用要求定义属性上的约束条件，即属性值限制，如：

（1）不允许取空值 （2）列值唯一 （3）用CHECK短语指定列值应该满足的条件

（1）不允许取空值

【例 5.5】在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

create table SC
	(
	Sno char(9)  not null,	/*Sno属性不能取空值*/
	Cno char(4)  not null,	
	Grade smallint not null,	
	primary key(Sno, Cno),  /* 在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义可不写 */
		...		
	); 

（2）列值唯一

【例 5.6】建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码。

create table DEPT
	(
	Deptno  numeric(2),
	Dname  vhar(9)  unique not null, /*要求Dname列值唯一, 并且不能取空值*/
	Location  char(10),
	primary key (Deptno)
	); 

（3）用CHECK短语指定列值应该满足的条件

【例 5.7】Student表的Ssex只允许取“男”或“女”。

create table Student
	(
	Sno   char(9) primary key,
	Sname char(8) not null,                     
	Ssex  char(2)  check(Ssex in ('男','女')), /*性别属性Ssex只允许取'男'或'女' */
	Sage  smallint,
	Sdept  char(20)
	);

【例 5.8】 SC表的Grade的值应该在0和100之间。

create table  SC
	(
	Sno    char(9),
	Cno    char(4),
	Grade  smallint check(Grade>=0 and Grade <=100),/*Grade取值范围是0到100*/
	primary key (Sno,Cno),
	foreign key (Sno) references Student(Sno),
	foreign key (Cno) references Course(Cno)
	);

2.属性列约束条件的检查和违约处理

当往表中插入元组或者修改属性的值时，关系数据库管理系统将检查属性上的约束条件是否被满足，若不满足则操作拒绝执行。

二，元祖上的约束条件

1.元祖上约束条件的定义

元组级的限制可以设置不同属性之间的取值的相互约束条件

【例 5.9】当学生的性别是男时，其名字不能以Ms.打头。

create table Student
	(
	Sno    char(9), 
	Sname  char(8) not null,
	Ssex   char(2),
	Sage   smallint,
	Sdept  char(20),
	primary key (Sno),
	check (Ssex='女' or Sname not like 'Ms.%')/*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/
	);

2.元祖上约束条件的检查和违约处理

当往表中插入元组或者修改属性的值时，关系数据库管理系统将检查属性上的约束条件是否被满足，若不满足则操作拒绝执行。

 
四、完整性约束命名子句

  SQL还在CREATE TABLE 语句中提供拉完整性约束命名子句 CONSTRAINT ，用来对完整性约束条件命名，灵活地增加，删除一个完整性约束条件。

1.完整性约束命名子句

constraint <完整性约束条件名> <完整性约束条件>

<完整性约束条件>名包括：not null, unique, primary key, foreign key, check短语等。

【例 5.10】建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。

create table Student
	(
	Sno  numeric(6)
	constraint C1 check (Sno between 90000 and 99999),
	Sname  char(20)  
	constraint C2 NOT NULL,
	Sage  numeric(3)
	constraint C3 check (Sage < 30),
	Ssex  char(2)
	constraint C4 check(Ssex in( '男','女')),
	constraint StudentKey primary key(Sno)
	);

【例 5.11】 建立教师表TEACHER，要求每个教师的应发工资不低于3000元。

create table TEACHER
	(
	Eno    numeric(4)  primary key,   /*在列级定义主码*/
	Ename  char(10),
	Job    char(8),
	Sal    numeric(7,2),
	Deduct numeric(7,2),
	Deptno numeric(2),
	constraint TEACHERFKry foreign key (Deptno) 	references DEPT(Deptno),
	constraint C1 check(Sal + Deduct >= 3000) /*应发工资是工资列Sal与扣除项Deduct之和。*/
	);

2.修改表中的完整性限制

【例 5.12】去掉例5.10 Student表中对性别的限制。

alter table Student 
     drop constraint C4;

【例5.13】修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40。

alter table Student
	drop constraint C1;
alter table Student
	add constraint C1 check(Sno between 900000 and 999999),
alter table Student
	drop constraint C3;
alter table Student
	add constraint C3 check(Sage < 40);

 
七、触发器

  触发器是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，触发器将被保存在数据库服务器中。任何用户对表的赠，删，该操作均由服务器自动激活。

1.定义触发器

触发器又叫事件-条件-动作规则。

一般格式为：

create trigger <触发器名>  
{before | after} <触发事件> on <表名>
referencing new|old row as<变量>
for each  {row | statement}
[when<触发条件>]<触发动作体>

  嗯，第一行的内容为创建一个触发器并起个名字，第二行说明触发事件，并且指出触发时机，第三行为给新旧变量起个别名，嗯至少我是这么理解的，哈哈，第四行表明是行级触发器还是语句级触发器，第五行表明触发条件，并且指出执行动作。嗯，差不多就是这样。看下面例子来理解吧！！！

【例 5.21】 当对表SC的Grade属性进行修改时，若分数增加了10%，则将此次操作记录到另一个表SC_U（Sno,Cno,Oldgrade,Newgrade）中，其中Oldgrade是修改前的分数，Newgrade是修改后的分数。

首先，我们建立一个这个SC_U基本表，用之前学过的SQL语句，应该不难哈

create table SC_U
		(
		Sno char(15),
		Cno char(10),
		Old int,
		New int
		);

然后，就建立题目中所说的触发器。

create trigger  SC_T		
after update of Grade on SC
referencing
	 old row  as  OldTuple,
	 new row  as  NewTuple
for each row
when(NewTuple.Grade >= 1.1 * OldTuple.Grade)
	 insert into SC_U(Sno,Cno,OldGrade,NewGrade) 				
	 values(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade);

很显然，是要出错的，早在意料之中，因为这是在TSQL中，上课老师也讲啦。

直接百度，看啦很多，总之要用 if 和 begin等控制，另外声明变量要用declare@加上名字，然后在用select进行变量赋值：

create trigger SC_T
on SC
after update
as
begin 
	declare @old int,@new int,@sno char(15),@cno char(10)--声明变量
	if(update(Grade))
	begin
	select @old=Grade from deleted
	select @new=Grade from inserted
	select @sno=Sno from inserted
	select @cno=Cno from inserted   --给变量赋值
	if(@new>=1.1*@old)
		insert into SC_U(Sno,Cno,Old,New)  --执行语句，嗯，就是前面学的insert语句
		values(@sno,@cno,@old,@new)
	end
end;

  使用select给自己建立的变量赋值，嗯，这个 if 控制很容易理解，多加上个end就可以啦，标准SQL里面是REFERENCING类似这里面的declare的使用，差不多就是这么个理。

实践一下

update SC
set Grade=100
where Sno='201215121' and Cno='2';

原本成绩是85，改为100，查看SC_U表，成功啦，good。

【例 5.22】将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。

首先新建表StudentInsertLog和表StudentInsertLogUser。

CREATE TABLE StudentInsertLog
(
Numbers INT
)
CREATE TABLE StudentInsertLogUser
(
  UserName nchar(10),
  DateAndTime datetime
)

然后：

--新建触发器Student_Count
create trigger Student_Count
on Student  	         
after
insert
as 
    insert into StudentInsertLog(Numbers)
	select count(*) from Student;

-- 新建触发器Student_Time
create trigger Student_Time
on Student
after
insert
as 
	declare @UserName nchar(10)
	declare @DateTime datetime

	select @UserName = system_user
	select @DateTime = convert(datetime,getdate(),120)

	insert into StudentInsertLogUser(UserName,DateAndTime)
	values(@UserName,@DateTime);

发现一个问题，不能同时创建这两个触发器，必须一个一个弄，amazing！！!

create trigger Student_Count
after update on Student  	         
referencing
 	new table as DELTA
for each statement 
    insert into StudentInsertLog (Numbers)
	select count(*) from DELTA

这个是标准SQL，肯定也是要报错的，参考老师给的T-SQL语句：

测试一下：

INSERT
INTO  Student
VALUES ('201215135','王五','男',18,'CS');
SELECT * FROM Student
SELECT * FROM StudentInsertLog
SELECT * FROM StudentInsertLogUser

测试成功

【例 5.23】 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元。

先建一个Teacher表：

create table Teacher
	(
	Tno char(15) primary key, 
	Tname char(10), 
    Job char(8), 
 	Sal int, 
	);

标准SQL语句如下：

create trigger Insert_Or_Update_Sal 
before  insert or update on Teacher  /*触发事件是插入或更新操作*/
for each row        /*行级触发器*/
begin               /*定义触发动作体，是PL/SQL过程块*/
  if(new.Job = '教授') and (new.Sal < 4000) 
     then  new.Sal := 4000;                
   	 end if;
end; 

这个是标准SQL，肯定也是要报错的，我们看T-SQL语句：

create trigger Insert_Or_Update_Sal
on Teacher
for update,insert
as
begin
	declare @job char(8)
	declare @sal int
	if update(Sal)
	begin	
	select @sal = Sal from inserted
	select @job = Job from inserted
	if(@sal < 4000 and @job = '教授')
		update Teacher
		set Sal = 4000
		where Sal < 4000 and Job = '教授'
	end
end;

测试一下：

insert 
into Teacher(Tno,Tname,Job,Sal)
values('201215121','张三','教授','3500');

2.激活触发器

  触发器的执行是由触发事件激活，并由数据库服务器自动执行的。一个数据表上可能定义了多个触发器，如多个BEFORE触发器，多个AFTER触发器等，同一个表的多个触发器遵循如下的执行顺序。

（1）执行该表上的 BEFORE 触发器

（2）激活触发器的 SQL 语句

（3）执行该表上的 AFTER 触发器

3.删除触发器

删除触发器的SQL语句如下：

drop trigger <触发器名> on <表名>;

注意，该触发器必须存在哦！！！

 
8.3 存储过程和函数

  过程化SQL块主要有两种类型，即命名块和匿名块，之前学的都是匿名块，匿名块每次执行时都要进行编译，它不能被存储到数据库中，也不能在其他过程化SQL块中调用。过程和函数是命名块，它们被编译后保存在数据库中，称为持久性存储模块，可以被反复调用，运行速度较快。

2.存储过程的用户接口

格式如下：

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,…])
AS <过程化SQL块>；

【例 8.9】从账户1转指定数额的款项到账户2中。假设账户关系表Account（Accountnum，Tatal）。

先创建一个Account表：

create table Account(
	accountnum char(3),-- 编号
	total float -- 余额
);
insert into Account
values('101',50),('102',100);--插入两个数据
select * from Account;

然后开始建立对应的存储过程：

if (exists (select * from sys.objects where name = 'Proc_TRANSFER'))
    drop procedure Proc_TRANSFER
go
create procedure Proc_TRANSFER 
@inAccount int,@outAccount int,@amount float
 /*定义存储过程TRANSFER，参数为转入账户、转出账户、转账额度*/
as
begin transaction TRANS   
   	declare	/*定义变量*/
	@totalDepositOut float,
	@totalDepositIn float,
	@inAccountnum int;
	 /*检查转出账户的余额 */     
	select @totalDepositOut = total from Account where accountnum = @outAccount;
	/*如果转出账户不存在或账户中没有存款*/
	if @totalDepositOut is null               	   
		begin
			print '转出账户不存在或账户中没有存款'
			rollback transaction TRANS; 	   /*回滚事务*/
			return;
		end;
	/*如果账户存款不足*/
	if @totalDepositOut < @amount     	
		begin
			print '账户存款不足'
			rollback transaction TRANS; 				/*回滚事务*/
			return;
		end
	/*检查转入账户的状态 */  
	select @inAccountnum = accountnum  from Account	where accountnum = @inAccount;
	/*如果转入账户不存在*/ 
	if @inAccountnum is null   		                       
		begin
			print '转入账户不存在'
		    rollback transaction TRANS; 	         	 		/*回滚事务*/
			return;
		end;
	/*如果条件都没有异常，开始转账。*/ 
	begin
		update Account set total = total - @amount where accountnum = @outAccount; /* 修改转出账户余额，减去转出额 */
		update Account set total = total + @amount where accountnum = @inAccount; /* 修改转入账户余额，增加转入额 */
		print '转账完成，请取走银行卡'
		commit transaction TRANS;                       	/* 提交转账事务 */
		return;
	end

然后进行转账等操作，进行验证：

select * from Account;
exec Proc_TRANSFER
	@inAccount = 102, --转入账户
	@outAccount = 101, --转出账户
	@amount = 50 --转出金额
select * from Account;

我们在开始和结束都进行一次查询，进行验证，这里调用存储过程用exec，后面再加上对应的参数就行啦。

ok，成功！！！

（3）修改存储过程

格式如下：

alter procedure 过程1 rename to 过程2;

（4）删除存储过程

drop procedure 过程名();

 
8.3.2函数

  这章的函数也称为自定义函数。因为是用户自己使用过程化SQL设计定义的。函数和存储过程类似，都是持久性存储模块。不同之处是函数必须指定返回的类型。

1.函数的定义语句格式：

CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型> 
AS <过程化SQL块>;

2.函数的执行格式语句

CALL/SELECT 函数名 ([参数1,参数2,…]);

3.修改函数

可以使用 alter function 重命名一个自定义函数：

ALTER FUNCTION 过程名1 RENAME TO 过程名2;

可以使用 alter function 重新编译一个函数：

ALTER FUNCTION 过程名 COMPILE;

嗯，查看啦其他博主关于T-SQL里面的一些函数相关定义，基本格式为：

格式语句为：

create   function   [ owner_name ]  function_name 
( [ {@parameter_name [as ]  scalar_parameter_data_type [ =default ] }  [ ,n ] ])
 returns  scalar_return_data_type
 [ with <function_option> [,n ] ]
 [ as ] 
 begin 
function_body
 return   [ scalar_expression ] 
 end 

举个例子：

create function Greatest(@v1 float,@v2 float,@v3 float)
RETURNS float
AS
BEGIN	
	declare @ret float;
	set @ret=@v1;
	if @ret<@v2 
	begin 
		set @ret=@v2 
	end 
	if @ret<@v3
	begin 
	   set @ret=@v3
	end
	return @ret
END

（1）调用语句：

select dbo.Greatest(1.36,10.56,8.366);

（2）exec 直接执行

exec dbo.Greatest1.36,10.56,8.366;

 
四，感悟

  嗯，有些时候，坚持或许很难，不仅仅是学习，生活上也是。但当我们坚持下去，尽管我们最后没有在顶峰相见，但是我们回头望去，我们不是在前行吗？正如刚火的一篇中科院博士的论文中提到：“信念很简单，把书念下去，然后走出去，不枉活一生”，读完很是感动，我试想过，我若是那般处境，我可能都没那般毅力，但幸运的是，我很幸运，所以我们更应该努力，加油吧。