触发器
(一)理解:
是一种特殊的存储过程,它不能显式地调用,而是进行insert、delete、update时被自动低激活。总结一句话就是:用来实现对标实施完整性约束的。
SQL Server为每个触发器都创建了两个表:inserted和deleted表。它们是系统维护的,它们存在在内存中,不是在数据库中,这连个标的结构总是与被该触发器作用的标的结构相同。触发器执行与该触发器相连的这两个表也被删除。
(二)类型:
Instead of 和After触发器
instead of 触发器用于替代(可以取代激发它的操作来执行)引起触发器执行的T-SQL语句。(用于视图和表)
after触发器用于语句执行之后。(用于表)
(三)示例:
下面我用机房收费系统来说明一下存储过程怎样用
(1)创建一个After触发器:执行insert
判断卡号是否存在,存在不能插入:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 邱慕夏
-- Create date: 2014-08-04
-- Description: 判断卡号是否存在
-- =============================================
CREATE TRIGGER TrigT_CardInsert
ON T_Card
AFTER INSERT
AS
if exists(select * from T_Card,inserted where T_Card.CardNo=inserted.CardNo)--判断是否存在该卡号
BEGIN
print '此卡号已经存在'
rollback transaction --回滚事物
END
GO
在新建查询中执行:
insert into T_Card_DAL(CardNo,RegisterDateTime,cash,Head,type,status) values('8','2014-07-30','300','001','固定用户','使用')
表中存在CardNo='8'的字段,如果存在主键约束的话,就会出现这样的提示:消息 2627,级别 14,状态 1,第 1 行违反了 PRIMARY KEY 约束 'PK_T_Card'。不能在对象 'dbo.T_Card' 中插入重复键。
所以可见insert首先执行主外键约束,之后,执行insert语句,将insert语句存放在inserted表中。
(2)创建Instead of 触发器:delete
退卡:退卡需要将T_Card表中CardNo为要删除的卡号相同的记录删除,T_Student表中删除CardNo相同的删除,向T_CancelCard表添加一条记录。
主外键的关系图:
从表中我们可以看到主键为StudentNo,外键为CardNo,就是T_student表中只能添加T_Card中已经存在的CardNo。
如果我们删除T_Card表中的CardNo的值时,会遇到约束,因为T_Student表中有一条该卡号的记录。因此我们不能直接删除,用Instead of来替代删除操作,首先删除T_Student表中的数据,之后再删除T_Card表中的数据。
USE [ReconsitutionChargev1.0_sys]
GO
/****** Object: Trigger [dbo].[TrigT_CardDelete] Script Date: 08/04/2014 15:16:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 邱慕夏
-- Create date: 2014-07-30
-- Description: 退卡
-- =============================================
ALTER TRIGGER [dbo].[TrigT_CardDelete]
ON [dbo].[T_Card]
instead of DELETE
AS
--声明变量
declare @cardNo varchar(8)
declare @Cash numeric(18,0)
declare @CancelHead varchar(10)
declare @CancelDate varchar(10)
--给变量赋值
select @cardNo =cardNo from deleted
select @Cash = cash from deleted
select @CancelHead = CancelHead from deleted
set @CancelDate=CONVERT([varchar],getdate(),(120))
BEGIN
delete T_student where cardno=@cardNo
delete T_Card where CardNo=@cardNo
insert into T_CancelCard(CardNo,Cash,CancelHead,CancelDate) values(@CardNo,@Cash,@CancelHead,@CancelDate)
END
删除激发触发器的语句我们只写:
delete T_Card where CardNo='8'
就可以执行上面的语句,如果用after语句,是要先执行主外键约束的,因此行不通。而Instead of语句是之后执行主外键约束的,通过上面的操作,大家就可以明白了。
(3)比较
触发器 | 作用 | 执行程序 | 针对 |
Instead of | 可以替代执行语句操作 | 约束在后 | 表和视图 |
After | 首先执行语句 | 约束在前 | 表 |
比较学习总是给我们很大收获,无论我们学习什么都要不断思考、实践、总结,之后这些东西才能都成为我们自己的,知识不是记忆的,而是总结的。上面只是一个小小的入门,有什么写的不对的地方,请大家多多指教。