SQL----触发器练习

• 任务描述
• 相关知识
  • 触发器的定义
    • 触发器和存储过程的区别
    • 触发器的优点
  • 触发器的作用
  • 触发器的分类
    • DML触发器
    • DDL触发器
    • 登录触发器
  • 触发器的工作原理
    • inserted表
    • deleted表
  • 创建触发器
• 编程要求

---

任务描述

本关任务：本关通过一个简单触发器的创建，建立对数据完整性的理解，掌握创建和使用触发器的基本方法。

相关知识

触发器的定义

触发器是建立在触发事件上的。例如，对表执行 INSERT 、 UPDATE 或 DELETE 等操作时， SQL Server 就会自动执行建立在这些操作上的触发器。在触发器中，包含了一系列用于定义业务规则的 SQL 语句，用来强制用户实现这些规则，从而确保数据的完整性。

触发器和存储过程的区别

触发器与存储过程的区别是运行方式的不同，触发器不能用 EXECUTE 语句调用，而是在用户执行 SQL 语句时，自动触发执行。而存储过程需要用户、应用程序或者触发器来显示地调用并执行。

触发器的优点

• 触发器是自动的。当对表中的数据做了任何修改之后，立即被激活；

• 触发器可以通过数据库中的相关表，进行层叠修改；

• 触发器可以强制限制。这些限制比用 CHECK 约束所定义的更复杂，与 CHECK 约束不同的是，触发器可以引用其他表中的列。

触发器的作用

触发器的主要作用就是，其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，提供比 CHECK 约束更复杂的数据完整性，并自定义错误消息。触发器的主要作用主要有以下几个方面：

• 强制数据库间的引用完整性；

• 级联修改数据库中所有相关的表，自动触发其它与之相关的操作；

• 跟踪变化，撤销或回滚违法操作，防止非法修改数据；

• 返回自定义的错误消息，约束无法返回信息，而触发器可以；

• 触发器可以调用更多的存储过程。

触发器的分类

SQL Server 包括三种常规类型的触发器： DML 触发器、 DDL 触发器和登录触发器。

DML触发器

DML 触发器是一些附加在特定表或视图上的操作代码。当数据库服务器中，发生数据操作语言事件时，执行这些操作。 SQL Server 中的 DML 触发器有三种：

INSERT 触发器：向表中插入数据时被触发；

DELETE 触发器：从表中删除数据时被触发；

UPDATE 触发器：修改表中数据时被触发。

当遇到下列情形时，应考虑使用 DML 触发器：

• 通过数据库中的相关表实现级联更改；

• 防止恶意或者错误的 INSERT 、 DELETE 和 UPDATE 操作，并强制执行 CHECK 约束定义的限制更为复杂的其他限制；

• 评估数据修改前后表的状态，并根据该差异采取措施。

DDL触发器

DDL 触发器是当服务器或者数据库中发生数据定义语言（主要是以 CREATE ， DROP ， ALTER 开头的语句）事件时，被激活使用。使用 DDL 触发器，可以防止对数据架构进行的某些更改，还可以防止对记录数据中的更改或事件操作。

登录触发器

登录触发器会为响应 LOGIN 事件而激发存储过程，与 SQL Server 实例建立用户会话时，将引发此事件。

登录触发器将在登录的身份验证阶段完成之后，且用户会话实际建立之前激发。因此，来自触发器内部，且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。

触发器的工作原理

触发器触发时：

• 系统自动在内存中创建 deleted 表或 inserted 表；

• 只读，不允许修改，触发器执行完成后，自动删除。

inserted表

• 临时保存插入或更新后的记录行；

• 可以从 inserted 表中，检查插入的数据是否满足业务需求；

• 如果不满足，则向用户报告错误消息，并回滚插入操作。

deleted表

• 临时保存删除或更新前的记录行；

• 可以从 deleted 表中，检查被删除的数据是否满足业务需求；

• 如果不满足，则向用户报告错误消息，并回滚插入操作。

  inserted 表和 deleted 表对照：

操作	inserted表	deleted表
insert	存放新增的记录	……
delete	……	存放被删除的记录
update	存放更新后的记录	存放更新前的记录

创建触发器

创建触发器语法示例如下：

1. CREATE TRIGGER trigger_name
2. ON table_name
3. [WITH ENCRYPTION]
4. FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
5. AS
6. BEGIN
7. SQL语句
8. END

我们以 INSERT 触发器的创建为例，讲解触发器的创建和使用。首先创建测试数据表：

1. --创建学生表
2. create table student(
3. stu_id int identity(1,1) primary key,
4. stu_name varchar(10),
5. stu_gender char(2),
6. stu_age int
7. )

为 student 表创建 INSERT 触发器：

1. --创建insert触发器
2. create trigger trig_insert
3. on student
4. after insert
5. as
6. begin
7. if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在
8. create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表
9. declare @stuNumber int;
10. select @stuNumber = count(*)from student;
11. if not exists (select * from student_sum)--判断表中是否有记录
12. insert into student_sum values(0);
13. update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中
14. end

测试触发器：

1. --测试触发器trig_insert-->功能是向student插入数据的同时级联插入到student_sum表中，更新stuCount
2. --因为是后触发器，所以先插入数据后，才触发触发器trig_insert;
3. insert into student(stu_name,stu_gender,stu_age)values('吕布','男',30);
4. select stuCount 学生总人数 from student_sum;
5. insert into student(stu_name,stu_gender,stu_age)values('貂蝉','女',30);
6. select stuCount 学生总人数 from student_sum;
7. insert into student(stu_name,stu_gender,stu_age)values('曹阿瞒','男',40);
8. select stuCount 学生总人数 from student_sum;

执行上面的语句后，结果如下图所示：

另外，因为定义学生总数表 student_sum ，是向 student 表中插入数据后，才计算的学生总数。所以，学生总数表应该禁止用户，向其中插入数据。

1. --创建insert_forbidden,禁止用户向student_sum表中插入数据
2. create trigger insert_forbidden
3. on student_sum
4. after insert
5. as
6. begin
7. RAISERROR('禁止直接向该表中插入记录，操作被禁止',1,1)--raiserror 是用于抛出一个错误
8. rollback transaction
9. end

结果如下：

编程要求

本关使用的关系模式如下，模拟银行账户存取款 account(id,accountno,total) 表示客户账户关系，属性为（id，账户号，余额） operator(id,accno,type,num) 表示客户存取款操作，属性为（id，账户号，操作类型，数额），操作类型有in 和 out 两种，表示存入或取出。 关系中的id为自增变量，用于唯一标识一个元组

我们已经为你建好了数据库与数据表，并添加了相应的数据内容。 你只需：

• 补全右侧代码片段中 create trigger_insert_operator 下的 Begin-End 区域间的代码，向 operator 表建立一个插入触发器。保证向 operator 表中插入元组时：
• 1.若插入元组type为in时，检查account关系：
  • 若关系中，有该accno，则修改余额为total+num；
  • 若accno不存在，则应插入一个元组，表示新建一个该账户，使其余额为num；
• 1. 若插入元组type为out时，检查account关系：
  • 若关系中无accno对应的账户，表明该取款操作不合法，应拒绝插入（拒绝插入的提示：插入的账户异常，操作被禁止）；
  • 若account关系中有对应账户，余额不足以支付取款数额时也应拒绝，并提示。
  • 若能取款，则修改account关系该账户的余额。

USE accountDB
go

SET NOCOUNT ON 
go
--********** create trigger_insert_operator  **********--
--********** Begin **********--
create trigger triggrt_insert_opertor
on operator
after insert
as
Begin
  declare @accno varchar(50),@type varchar(10),@num float,@count_num int,@count_num1 int
  select @accno=accno,@type=type,@num=num from inserted
  if @type='in'
    Begin
      select @count_num =count(accountno) from account where @accno=accountno 
      if @count_num>0
      Begin
        update account set total=total+@num where @accno=accountno
        end
      else 
      Begin
        insert into account(accountno,total) values(@accno,@num)
      End
    End
  else if (@type='out')
    Begin
      select @count_num =count(accountno) from account where @accno=accountno
      select @count_num1 =count(accountno) from account where @accno=accountno and total >@num
      if (@count_num=0 or @count_num1=0)
          Begin
            raiserror("插入的账户异常，操作被禁止",1,1)
            
          end 
      else  
        Begin
          update account set total=total-@num where accountno=@accno
        End
    End
  End;





--********** End **********--
go

--以下代码请勿删除
insert into operator(accno,type,num) values('002','in',878)
go
insert into operator(accno,type,num) values('003','in',608)
go
insert into operator(accno,type,num) values('002','out',90)
go
insert into operator(accno,type,num) values('001','out',50)
go
insert into operator(accno,type,num) values('004','out',50)
go
select * from account
go