数据库基础--触发器

触发器是在对表进行插入、更新或删除操作时自动执行的存储过程
触发器通常用于强制业务规则
触发器是一种高级约束,可以定义比 CHECK 约束更为复杂的约束
  可执行复杂的 SQL 语句(if/while/case)
  可引用其它表中的列

触发器定义在特定的表上,与表相关
自动触发执行
不能直接调用
是一个事务(可回滚)

触发器的类型
1.AFTER 触发器
  在执行了 INSERT、UPDATE 或 DELETE 语句操作之后执行 AFTER 触发器。AFTER触发器只能在表上指定。
  DELETE 触发器
  INSERT 触发器
  UPDATE 触发器
2.INSTEAD OF 触发器
  在指定的操作(INSERT、UPDATE 或 DELETE 语句)之前被执行,它的功能是不执行指定的操作,而是执行 INSTEAD OF 触发器中定义的操作。可以在表和视图上定义
  INSTEAD OF 触发器。每张表或视图上只能为每个触发动作(INSERT, UPDATE, or DELETE)创建一个 INSTEAD OF 触发器。
  DELETE 触发器
  INSERT 触发器
  UPDATE 触发器

触发器触发时:
  系统自动在内存中创建 deleted 表或 inserted 表
  只读,不允许修改;触发器执行完成后,自动删除
inserted 表
  临时保存了插入或更新后的记录行
  可以从 inserted 表中检查插入的数据是否满足业务需求
    如果不满足,则向用户报告错误消息,并回滚插入操作deleted 表
  临时保存了删除或更新前的记录行
  可以从 deleted 表中检查被删除的数据是否满足业务需求
  如果不满足,则向用户报告错误消息,并回滚删除操作

inserted 和 deleted 表存放的信息

/****************触发器实例****************/
CREATE TABLE [dbo].[bank]
(
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [CardID] [char](10) NOT NULL,
  [transType] [char](4) NULL,
  [currentMoney] [money] NULL,
  [transMoney] [money] NULL
)
CREATE TABLE [dbo].[transInfo]
(
  [cardID] [char](10) NOT NULL,
  [transType] [char](4) NULL,
  [transMoney] [money] NULL
)
--INSERT触发器实例
CREATE TRIGGER trig_transInfo
ON transInfo
FOR INSERT
AS
DECLARE @type char(4),@outMoney MONEY
DECLARE @myCardID char(10),@balance MONEY
SELECT @type=transType,@outMoney=transMoney,@myCardID=cardID
FROM inserted
IF (@type='支取')
  UPDATE bank
  SET currentMoney=currentMoney-@outMoney,
    transType=@type,transMoney=@outMoney WHERE cardID=@myCardID
ELSE
  UPDATE bank
  SET currentMoney=currentMoney+@outMoney,transType=@type,transMoney=@outMoney WHERE cardID=@myCardID
GO
INSERT INTO bank VALUES('123456','',1000,'')
INSERT INTO transInfo VALUES('123456','支取',200)
INSERT INTO transInfo VALUES('123456','存款',10000)
--DELETE触发器
CREATE TRIGGER trig_delete_transInfo
ON transInfo
FOR DELETE
AS
print '开始备份数据,请稍后......'
IF NOT EXISTS(SELECT * FROM sysobjects WHERE name='backupTable')
  SELECT * INTO backupTable FROM deleted
ELSE
  INSERT INTO backupTable SELECT * FROM deleted
  print '备份数据成功,备份表中的数据为:'
DELETE FROM transInfo
SELECT * FROM backupTable
GO
--UPDATE触发器
CREATE TRIGGER trig_update_bank
ON bank
FOR UPDATE
AS
DECLARE @beforeMoney MONEY,@afterMoney MONEY
SELECT @beforeMoney=currentMoney FROM deleted
SELECT @afterMoney=currentMoney FROM inserted
IF ABS(@afterMoney-@beforeMoney)>20000
BEGIN
  print '交易金额:'+convert(varchar(8),
  ABS(@afterMoney-@beforeMoney))
  RAISERROR ('每笔交易不能超过万元,交易失败',16,1)
  ROLLBACK TRANSACTION
END
INSERT INTO transInfo VALUES('123456','支取',20001)

触发器小结
1.如果一个表的外键在 DELETE、UPDATE 操作上定义了级联,则不能在该表上定义INSTEAD OF DELETE、INSTEAD OF UPDATE 触发器
2.如果定义视图时使用了 WITH CHECK OPTION,则不允许在其上创建 INSTEAD OF 触发器
3.触发器是“后过滤器”,它在数据修改通过所有规则、默认值之后才执行
4.首先考虑使用约束维护数据库完整性,仅在必要时才使用触发器
5.触发器是在对表进行插入、更新或删除操作时自动执行的存储过程,触发器通常用于强制业务规则
6.触发器还是一个特殊的事务单元,当出现错误时,可以执行 ROLLBACK TRANSACTION回滚撤销操作