接下来我们从代码中认识下几种触发器。
--#Update型触发器
If exits(select name from sysobjects where name=’tgr_update’)
Drop trigger tgr_update
Go
Create trigger tgr_update on student
for update
As
If (Update(student_ID))
Print ‘更改成功！’
Else
Begin
Raiserror(‘系统提示：更新发生错误’,16,1)
Rollback tran
End
Go
--测试
Update student set student_ID=10002 where student_ID=10001 |
注意：在创建触发器时，创建触发器必须是批处理的第一行，存储过程也是如此。
--# instead of 触发器
if (object_id('tgr_classes_inteadOf', 'TR') is not null)
drop trigger tgr_classes_inteadOf
go
create trigger tgr_classes_intead Of
on classes
instead of delete/*, update, insert*/
as
declare @id int, @name varchar(20);
--查询被删除的信息，病赋值
select @id = id, @name = name from deleted;
print 'id: ' + convert(varchar, @id) + ', name: ' + @name;
--先删除student的信息
delete student where cid = @id;
--再删除classes的信息
delete classes where id = @id;
print '删除[ id: ' + convert(varchar, @id) + ', name: ' + @name + ' ] 的信息成功！';
go
--test
select * from student order by id;
select * from classes;
delete classes where id = 7;
|
# 启用、禁用触发器
--禁用触发器
disable trigger tgr_message on student;
--启用触发器
enable trigger tgr_message on student;
<P style="BACKGROUND: white"><SPAN style="COLOR: #4b4b4b"> # </SPAN><SPAN style="COLOR: #4b4b4b">显示自定义消息</SPAN><SPAN style="COLOR: #4b4b4b">raiserror</SPAN></P> |
if (object_id('tgr_message', 'TR') is not null)
drop trigger tgr_message
go
create trigger tgr_message
on student
after insert, update
as raisError('tgr_message触发器被触发', 16, 10);
go
--test
insert into student values('lily', 22, 1, 7);
update student set sex = 0 where name = 'lucy';
select * from student order by id;
<SPAN style="BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial"></SPAN> |
二、业务规则
业务规则听起来很难理解，当然它也是值得我们深究东西，通俗的讲它其实是符合实际条件。如：某商店规定一个售货员在一个月内售出10个以上的热浴盆，那么奖励2000元；某公司的订单上必须含有客户的姓名和联系方式等等，这些都是简单的业务规则。从数据库的角度看，业务规则就是约束。
约束和触发器
MS SQL Server提供了两种主要的机制进行强制业务规则和数据的完整性：约束和触发器。在作用上约束支持的触发器都可以实现，它们两者是相容的关系，如下图。虽然两者在作用关系上有重合的地方，但是相较两者的执行效率和维护难易来说，触发器是远远不如约束的。所以约束能实现的情况下编程人员是不会选择触发器的。
一、约束，上篇博客我已经着重讲解了约束的概念，这里不再深究。
SQL Server中存在五种约束：
● 约束的目的：确保表中数据的完整型
● 常用的约束类型：
– 主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空
– 唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。
– 检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束
– 默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”
– 外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的那列
二、触发器，首先在下表中来看触发器的基本结构。
触发器是一种对表进行插入、删除、更改的时候自动运行的特殊的存储过程。它一般用在比核查约束更为复杂的约束中。但能用约束实现的功能，一般不用触发器。
触发器的应用种类繁多上面的几个示例都是比较常用的，当然最好的熟练方法就是多用，多练。
触发器和存储过程
触发器是一种特殊的存储过程，不是由用户直接调用。而存储过程是一组T-SQL语句，经过编译后可以被多次调用。类似于其它编程语言中的过程。它可以接收输入参数、输出参数、返回单个或多个结果集以及返回值。
存储过程分为三类：
1、系统存储过程：以sp_开头，用来进行系统的各项设定、取得信息。相关管理工作，如 sp_help就是取得指定对象的相关信息
2、扩展存储过程 以XP_开头，用来调用操作系统提供的功能
exec master..xp_cmdshell 'ping 10.8.16.1'
3、用户自定义的存储过程，这是我们所指的存储过程
常用格式
Create PRocedure procedue_name
[@parameter data_type][output]
[with]{recompile|encryption}
as
sql_statement
--解释:
--output：表示此参数是可传回的
--with {recompile|encryption}
--recompile:表示每次执行此存储过程时都重新编译一次
--encryption:所创建的存储过程的内容会被加密 |
举例：
有如下表量表
result_Info：
Student_Info
#创建返回参数的存储过程
If exists(select name from sysobjects where name=’proc_return’ and type=’P’)
Drop proc proc_return
Go
Create proc proc_return
@param1 int,
@param2 char(10),
@param3 char(10)
@param4 int output
With encryption --加密
As
Insert into student_Info(student_ID,name,result) values(@param1,@param2,@param3)
Select @param4=sum(result) from student_Info
Print ‘总分为：’ & convert(char,@param)
Go
--调用测试
Declare @sumresult int
Exec proc_return 12,’王刚’,80,@sumresult
Go |
存储过程的3种传回值：
1、以Return传回整数
2、以output格式传回参数
3、Recordset
传回值的区别：
output和return都可在批次程式中用变量接收，而recordset则传回到执行批次的客户端中
#创建一个存储过程，实现将表一和表二合并，该表只含Student_ID、Name、sex、result，将临时表存放在存储过程中。
If exists(select name from sysobjects where name=’proc_save’ and type=’P’)
Drop proc proc_return
Go
Create proc proc_save
As
Select r.student_ID,r.Name,r.result,s.sex into #temptable from result r inner join student s on r.student_ID=s.student_ID
If @@error=0
Print ‘Successed’
Else
Print ‘Failed’
Go |
存储过程的应用类型还有很多，这里我只介绍了在编程时常用的两种。
总结
在进行数据库程序设计时，数据的完整性是编程人员必须要考虑的，但是有时候这些知识的细节却让我们纠结的很，搞不清改用哪个。总之吧：能用存储过程实现的不用触发器；能用约束实现的不用触发器，约束和存储过程用哪个都可以。
有些不懂得地方在SQL Server中按F1，在SQL Server联机丛书的索引中查找可以解决我们的一切矛盾。