【SQL Server系列】_06数据库完整性与触发器

一、数据库的安全性

数据库的安全性是指：保护数据库以防止非法使用所造成的数据泄露，更改或者破坏。

数据泄露-》数据被读取
更改-》增删改
破坏-》加密，删库

3A
【1】Authenticate：验证
【2】Authorize：授权
【3】Audit：审计——对所有访问的活动进行记录。

二、完整性控制

完整性包括
【1】数据库服务器完整性：数据库在哪一台服务器上面
【2】数据库完整性：整个数据库服务功能的完整性
【3】数据对象完整性：数据的完整性

1、数据库完整性的含义

指保护数据库中数据的正确性，有效性和相容性，防止错误的数据进入数据库造成无效的操作。

数据库的完整性和安全性是数据库保护的两个不同的方面。

【1】数据库的安全性：是指保护数据库以防止非法使用所造成的数据的泄露，更改，破坏。
安全性的防范对象是非法用户和非法操作。

【2】数据库的完整性：是指防止合法用户使用数据库时向数据库中加入不符合语义的数据。
完整性的防范对象是不合语义的数据。

2、完整性规则的组成

完整性规则主要有三部分组成（主要是为了保护数据的质量）

【1】触发条件：确定系统什么时候使用完整性规则来检查数据。

【2】约束条件：规定系统检查用户发出的操作请求违背了什么样的完整性约束条件。

【3】违约响应：规定系统如果发现用户发出的操作请求违背了完整性约束条件，应该采取一定的动作来保证数据的完整性，即违约时要做的事情。

---

完整性规则从执行时间上分为：立即执行约束和延迟执行约束

【1】立即执行约束：指在执行用户事务的过程中，某一条语句执行完成后，系统立即对此数据进行完整性约束条件检查。

【2】延迟执行约束：指在整个事务执行结束后，再对约束条件进行完整性检查，结果正确后才能提交，而如果结果不正确，则要进行事务的回滚操作。

如果用户操作请求违背了立即执行约束，则可以拒绝该操作，以保护数据的完整性。

如果用户材质请求违背了延迟执行约束，而不知道哪个事务的操作破坏了完整性，则只能拒绝整个事务，把数据库恢复到该事务执行前的状态。

---

一条完整性规则可以用一个五元组来表示：（D,O,A,C,P）
【1】D（DATA）：代表约束作用的数据对象

【2】O（OPERATION）:代表触发完整性检查的数据库操作，即当用户发出什么操作请求时需要检查该完整性规则，是立即执行还是延迟执行

【3】A（Asseration）:代表数据对象必须满足的语义约束，是规则的主体

【4】C（Condition）:代表数据对象值的谓词，也就是采取的操作

【5】P（PROCEDURE）:代表违法完整性规则时触发执行的操作过程

3、完整性约束条件的分类

【1】值的约束

值的约束即对数据类型，数据格式，取值范围和空值进行规定

【1】对数据类型的约束：包括数据的类型，长度，单位和精度。

【2】对数据格式的约束：如日期的格式

【3】对取值范围的约束：如月份是1-12（使用check）

【4】对空值的约束：not null

【2】结构的约束

结构的约束：对数据之间联系的约束。

【1】函数依赖约束

【2】实体完整性约束

【3】参照完整性约束

【4】统计约束

【3】静态约束

指对数据库每一个确定的状态所应满足的约束条件，值的约束和结构的约束都属于静态约束。

【4】动态约束

指数据库从一种状态转变为另一种状态时，数据库状态变换的约束。

4、数据库完整性的实施

【1】声明式数据完整性

声明式数据完整性是将数据所需符号的条件融入到对象的定义中，这样SQL会自动确保数据符合事先制定的条件。比如：NOT NULL，CONSTRAINT，RULER…

特点：

1、通过针对表和字段定义声明的约束，可使声明式数据库完整性成为数据定义的一部分
2、使用约束、默认值与规则实施声明式数据完整性

【2】程序化数据完整性

程序化数据完整性指符合所需条件以及该条件的实施均通过所编写的程序代码来完成。

特点：

1、程序化数据完整性可以通过相关的程序语言及工具在客户端或者服务器端实施
2、SQL可以使用存储过程或触发器实施程序化数据完整性

---

总结：实施数据完整性的方法有五种：
【1】约束（constraint）
【2】默认值（default）
【3】规则（rule）
【4】存储过程（store procedure）
【5】触发器（trigger）

三、SQL的数据安全机制

SQL的安全模型分为三层结构：

【1】服务器安全管理
【2】数据库安全管理
【3】数据库对象的访问权限管理

SQL的身份验证模式：

【1】Windows身份验证模式
这种验证模式是一种“受信连接”。这种连接调用的是当前账户的凭据

【2】混合身份验证模式
允许以SQL身份验证迷失或者Windows身份验证模式来进行验证

角色：是对权限集中管理的一种机制，将不同权限组合在一起就形成了一种角色。因此不同的角色就代表了具有不同权限集合的组。

四、触发器

1、触发器概述

触发器是一种特殊的存储过程。是在满足一定的条件下自动激活而执行的。

触发器的优点：

【1】触发器是在某个事件发生是自动激活而执行的
【2】触发器可以实现比约束更为复杂的完整性要求
【3】触发器可以根据表数据修改前后的状态，根据其差异而采取相应的措施
【4】触发器可以防止恶意的或者错误的增删改

触发器的种类：

【1】DML触发器

DML触发器是在执行数据操纵语言（DML）事件时被激活而自动执行的触发器。即当数据库服务器发生对数据表中的数据进行插入（INSERT），修改（UPDATE），删除（DELETE）操作事件自动执行的存储过程。

DML触发器根据代码执行的时机，分为：After触发器和Instead of触发器。

After触发器：在执行插入（INSERT），修改（UPDATE），删除（DELETE）语句操作之后激活执行的触发器，即在记录已经改变完之后，才会被激活执行，主要用于记录数据更改后的处理或者检查。After触发器只能用在表上面，不能用在视图上面。

Instead of触发器：代替激活触发器的DML操作。即不去执行插入（INSERT），修改（UPDATE），删除（DELETE）语句，而是执行触发器中的代码所定义的操作。Instead of触发器能用在表上面，也能用在视图上面。

【2】DDL触发器
是响应各种数据定义语言事件时激活执行的存储过程，这些事件主要与CREATE,ALTER,DROP等语句进行对应。DDL触发器一般用于执行数据库中的管理任务，如审核和规范数据库操作，防止数据库表结构被修改。

【3】登录触发器

由登录事件而激活的触发器。

2、代码演示

创建一张临时表employee_test并插入数据：

 CREATE TABLE employee_test
(
	emp_id int identity,
	emp_name varchar(100),
	emp_sal decimal(10,2)
)
--插入5个变量
	INSERT INTO Employee_Test VALUES('Anees',1000);
	INSERT INTO Employee_Test VALUES('Rick' ,1200);
	INSERT INTO Employee_Test VALUES('John',1100);
	INSERT INTO Employee_Test VALUES('Stephen',1300);
	INSERT INTO Employee_Test VALUES('Maria',1400);

创建一张Employee_Test_Audit表，专门存放我们对表操作的记录：

CREATE TABLE Employee_Test_Audit
(
	Emp_ID int,
	Emp_name varchar(100),
	Emp_Sal decimal (10,2),
	Audit_Action varchar(100),
	Audit_Timestamp datetime
)

【1】创建AFTER触发器——insert

	--创建After触发器
	--创建一个触发器，名字叫trgAfterInsert
CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test] 
FOR INSERT
AS
	declare @empid int;
	declare @empname varchar(100);
	declare @empsal decimal(10,2);
	declare @audit_action varchar(100);--定义变量
	select @empid=i.Emp_ID from inserted i;	
	select @empname=i.Emp_Name from inserted i;	
	select @empsal=i.Emp_Sal from inserted i;	
	set @audit_action='Inserted Record -- After Insert Trigger.';--设定变量值
	insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
	values(@empid,@empname,@empsal,@audit_action,getdate());--使用默认函数getdate()，来记录对表进行操作的时间
	PRINT 'AFTER INSERT trigger fired.'
GO

 --插入记录
 insert into Employee_Test values('Chris',1500);

 SELECT * FROM employee_test
 SELECT * FROM Employee_Test_Audit

【2】创建AFTER触发器——update

创建update触发器：

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test] 
FOR UPDATE
AS
	declare @empid int;
	declare @empname varchar(100);
	declare @empsal decimal(10,2);
	declare @audit_action varchar(100);
	select @empid=i.Emp_ID from inserted i;	
	select @empname=i.Emp_Name from inserted i;	
	select @empsal=i.Emp_Sal from inserted i;	
	if update(Emp_Name)
		set @audit_action='Updated Record -- After Update Trigger.';
	if update(Emp_Sal)
		set @audit_action='Updated Record -- After Update Trigger.';
	insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
	values(@empid,@empname,@empsal,@audit_action,getdate());
	PRINT 'AFTER UPDATE Trigger fired.'
GO

对chris的工资进行修改(从1500改成1550)：

	update Employee_Test set Emp_Sal=1550 where Emp_ID=6

【3】创建AFTER触发器——delete

CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test] 
AFTER DELETE
AS
	declare @empid int;
	declare @empname varchar(100);
	declare @empsal decimal(10,2);
	declare @audit_action varchar(100);
	select @empid=d.Emp_ID from deleted d;	
	select @empname=d.Emp_Name from deleted d;	
	select @empsal=d.Emp_Sal from deleted d;	
	set @audit_action='Deleted -- After Delete Trigger.';
	insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
	values(@empid,@empname,@empsal,@audit_action,getdate());
	PRINT 'AFTER DELETE TRIGGER fired.'
GO

删除最后一行记录：

delete Employee_Test  where Emp_ID=6

【4】创建instead of触发器——insert

创建instead of触发器：

CREATE TRIGGER trgInsteadOfInsert ON [dbo].[Employee_Test] 
INSTEAD OF INSERT
AS
	declare @emp_id int;
	declare @emp_name varchar(100);
	declare @emp_sal int;
	select @emp_id=i.Emp_ID from inserted i;
	select @emp_name=i.Emp_Name from inserted i;
	select @emp_sal=i.Emp_Sal from inserted i;
	BEGIN
		if(@emp_sal>12000)
		begin
			RAISERROR('Cannot insert where salary > 12000',16,1);--抛出异常
			ROLLBACK;
	        insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
			values(@emp_id,@emp_name,@emp_sal,'Inserted -- Instead Of Inserted Trigger.Salary too high',getdate());		
		end
		else
		begin
			insert into Employee_Test (Emp_name,Emp_Sal) values (@emp_name,@emp_sal)
			COMMIT;
			PRINT 'Insert Record -- Instead Of Inserted Trigger.'
		end
	END
GO

insert into Employee_Test (Emp_name,Emp_Sal) values ('Smith',13000)

【5】创建instead of触发器——update

 CREATE TRIGGER trgInsteadOfUpdate ON [dbo].[Employee_Test] 
INSTEAD OF UPDATE
AS
	declare @emp_id int;
	declare @emp_name varchar(100);
	declare @emp_sal int;
	
	select @emp_id=i.Emp_ID from inserted i;
	select @emp_name=i.Emp_Name from inserted i;
	select @emp_sal=i.Emp_Sal from inserted i;

	BEGIN
		if(@emp_sal>12000)
		begin
			RAISERROR('Can not update where salary > 12000',16,1);
			ROLLBACK;
	     insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
			values(@emp_id,@emp_name,@emp_sal,'Update -- Instead Of Update Trigger.Salary too high',getdate());		
		end
		else
		begin
			update Employee_Test set Emp_Sal=@emp_sal where Emp_ID=@emp_id
			COMMIT;
			PRINT 'Record Updated -- Instead Of Updated Trigger.'
		end
	END
GO

	update Employee_Test set Emp_Sal=1550 where Emp_ID=5

【6】创建instead of触发器——delete

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test] 
INSTEAD OF DELETE
AS
	declare @emp_id int;
	declare @emp_name varchar(100);
	declare @emp_sal int;
	
	select @emp_id=d.Emp_ID from deleted d;
	select @emp_name=d.Emp_Name from deleted d;
	select @emp_sal=d.Emp_Sal from deleted d;

	BEGIN
		if(@emp_sal>1200)
		begin
			RAISERROR('Cannot delete where salary <1200',16,1);
			ROLLBACK;--回滚，不允许删除
		end
		else
		begin
		delete from Employee_Test where Emp_ID=@emp_id;
		COMMIT;
		insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
			values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
			PRINT 'Record Deleted -- Instead Of Delete Trigger.'
		end
	END
GO

--将其中ID=2的进行删除
 delete Employee_Test  where Emp_ID=2

此时查看两张表：

也可以使用in来删除表中的数据：

  delete from Employee_Test  where Emp_ID in (3,4,5)

3、DDL触发器

注意：执行下面代码之后就无法添加新的数据表或者删除和更改数据表了，必须手动对触发器进行禁用之后才可以

  --DDL触发器
	CREATE TRIGGER DDL_CREATE
	ON DATABASE
	for create_table
	as
	begin
		print '不能添加新的数据表!'
		rollback
	end

	CREATE TRIGGER DDL_ALTER_DROP
	ON DATABASE
	for drop_table,alter_table
	as
	begin
	  print '不允许删除或更改数据表'
	  rollback
	end

CREATE TABLE TTTT
(ID INT)

 --禁用触发器之后才可以重新删除和创建数据库