SQL Server2012利用触发器和特殊表Insert表和Deleted表实现级联式数据修改

本文篇幅有点长，想直接看利用两个特殊表进行级联数据修改的可以直接划到后半部分。

---

在SQL Server2012数据库系统中，存储过程和触发器都是SQL语句和流程控制语句的集合。就本质而言，触发器也是一种存储过程，它是一种在基本表被修改时自动执行的内嵌过程，主要通过事件进行触发而执行，而存储过程可以通过存储过程名而被调用。

触发器分为：DML触发器和DDL触发器。DML触发器是当数据库服务器中发生数据操作语言（DML）事件时会自动执行的存储过程，DDL触发器是在响应数据定义语言（DDL）语句时触发，一般用于数据库中执行管理任务。区别：DML触发器是响应UPDATE、INSERT或DELETE语句而激活的，DDL触发器是响应CREATE、ALTER、DROP、GRANT、DENY、REVOKE等语句而激活的。

触发器可以实现：1.强制比CHECK约束更复杂的数据完整性；2.使用自定义的错误信息；3.实现数据库中多张表的级联修改；4.比较数据库修改前后数据的状态。5.调用存储过程；6.维护非规范化数据。

在SQL Server2012里，为每个DML触发器都定义了两个特殊表，一个是插入表（Inserted），一个是删除表（Deleted）。这两个表是建在数据库服务器的内存中，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只用读取的权限，没有修改的权限。

Inserted逻辑表：当向触发器所在的表中插入数据时，Insert触发器触发执行，新的记录插入到触发器所在的表和Inserted逻辑表中。

Deleted逻辑表：从触发器所在的表中删除的记录时，Delete触发器执行，被删除的记录保存到Deleted逻辑表中。

修改一条记录等于插入一新记录，同时删除旧记录。对定义Update触发器的表记录修改时，表中原来记录移动到Deleted逻辑表中，修改过的记录插入到Inserted逻辑表中以及触发器所在的表中。

以下是在bankcard数据库中使用流程控制语句创建inser触发器。当向交易记录表（Trecord）中添加一条交易信息时，如果今天某账号交易支出某个金额，则其账户余额减去此金额；如果今天某账号收入某个金额，则其账户余额加上此金额。

具体过程如下：

首先新建bankcard数据库，再新建depositor储户表，account账户表，Trecord交易记录表，具体代码如下：

USE bankcard
go
drop table Trecord
drop table account
drop table depositor

go
CREATE TABLE depositor
(
IDNO char(18) NOT NULL PRIMARY KEY ,
Dname nvarchar(10) NOT NULL,
Telephone char(11) NOT NULL CHECK (Telephone LIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
VIP nchar(1) NOT NULL CHECK (VIP='是' OR VIP='否')
)
go
CREATE TABLE account
(
AccNO char(20) PRIMARY KEY  CHECK (AccNO LIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
IDNO char(18) NOT NULL CONSTRAINT de_ac FOREIGN KEY REFERENCES depositor(IDNO) ,
Password char(6) NOT NULL  CHECK (Password LIKE'[0-9][0-9][0-9][0-9][0-9][0-9]') ,
OpenDate date NOT NULL DEFAULT getdate(),
CardType nchar(3) NOT NULL CHECK (CardType = '信用卡' OR cardType = '借记卡') ,
MoneyType nvarchar(10) NOT NULL  CHECK (MoneyType = '人民币' OR MoneyType = '美元') ,
Balance money NOT NULL,
ExpiryDate date NOT NULL,
 CHECK (ExpiryDate>OpenDate)
)
go
CREATE TABLE Trecord
(
ID int PRIMARY KEY IDENTITY(1,1),
TDate date NOT NULL DEFAULT getdate(), 
AccNO char(20) CONSTRAINT tr_Ac FOREIGN KEY REFERENCES account(AccNO) CHECK (AccNO LIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ,
Expense money,
Income money,
OppAccNO char(20) CONSTRAINT tr_Acc FOREIGN KEY REFERENCES account(AccNO) CHECK (OppAccNO LIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ,
Place nvarchar(30),
Abstract nvarchar(20)
)

insert into depositor(IDNO,Dname,Telephone,VIP) 
values
('121***195809250110','李华','13988880000','是'),
('121***196809030111','郑洪洋','18199990000','否'),
('130***197312120120','刘小玉','13066660000','否'),
('130***198003090928','张伟','13933330000','是'),
('133***198708150101','张萌萌','13011110000','是')

insert into account(AccNO,IDNO,Password,OpenDate,CardType,MoneyType,Balance,ExpiryDate)
values
('41254280033512010000','133***198708150101','555555','2016-09-01','信用卡','人民币','19000.0000','2021-09-01'), 
('41254280033512020000','130***197312120120','666666','2016-09-20','信用卡','人民币','0.0000','2021-09-01'), 
('41254280033512060000','130***198003090928','222222','2016-08-15','信用卡','人民币','12000.5500','2021-08-01'), 
('41254280033512080000','121***196809030111','654321','2016-08-13','信用卡','人民币','4367.1200','2021-08-01'), 
('43674280033512050000','133***198708150101','333333','2016-08-15','借记卡','人民币','2309.0000','2021-08-01'), 
('43674280033512070000','130***197312120120','111111','2016-09-12','借记卡','人民币','9878.3300','2021-09-01'), 
('43674280033512090000','121***195809250110','123456','2016-08-13','借记卡','人民币','5678.0900','2021-08-01')

insert into Trecord(TDate,AccNO,Expense,Income,OppAccNO,Place,Abstract) 
values
('2016-08-20','41254280033512060000','600.0000',NULL,'41254280033512080000','友谊大街分行','转账支出'),
('2016-08-20','41254280033512080000','103.0000',NULL,'43674280033512050000','北国超市','消费支出'),
('2016-08-20','41254280033512080000',NULL,'600.0000','41254280033512060000','友谊大街分行','转账收入'),
('2016-08-20','43674280033512050000',NULL,'103.0000','41254280033512080000','北国超市','销售收入'),
('2016-08-23','43674280033512070000','125.0000',NULL,'43674280033512090000','新华书店','消费支出'),
('2016-08-23','43674280033512090000',NULL,'125.0000','43674280033512070000','新华书店','销售收入'),
('2016-08-25','43674280033512090000',NULL,'500.0000',NULL,'槐北路分行','ATM存款'),
('2016-08-25','43674280033512050000','8000.0000',NULL,NULL,'槐北路分行','ATM取款'),
('2016-08-26','43674280033512070000',NULL,'200.0000','43674280033512050000',NULL,'防暑费收入'),
('2016-08-26','43674280033512050000','200.0000',NULL,'43674280033512070000',NULL,'防暑费支出'),
('2016-08-26','43674280033512090000','800.0000',NULL,'43674280033512070000',NULL,'转账支出'),
('2016-08-26','43674280033512070000',NULL,'800.0000','43674280033512090000',NULL,'转账收入'),
('2016-08-28','41254280033512060000',NULL,'6780.0000','43674280033512050000',NULL,'9月份工资收入'),
('2016-08-28','43674280033512050000','6780.0000',NULL,'41254280033512060000',NULL,'9月份工资支出')

数据库建好之后，根据题意创建一个insert触发器，代码如下：

CREATE TRIGGER Transactions 
ON Trecord
FOR INSERT
AS
IF (SELECT Expense FROM inserted) IS NOT NULL
   UPDATE account SET Balance =Balance-(SELECT Expense FROM inserted)WHERE AccNO= (SELECT AccNO FROM inserted)
ELSE     
   UPDATE account SET Balance =Balance+ (SELECT Income FROM inserted)WHERE AccNO= (SELECT AccNO FROM inserted)
GO

然后，我们查看account表中账号为“41254280033512060000”的账户信息Balance的余额，如下图：

select * from account where AccNO='41254280033512060000'

 向Trecord添加一条交易信息，向账号为“41254280033512060000”的账户支出2000元，如下图所示：

insert Trecord values(getdate(),'41254280033512060000',2000,NULL,'43674280033512050000','背锅超市','消费支出')

 执行成功后，再查询账号为“41254280033512060000”的账户信息，可以知道，account账户表的余额以改变，结果如下：

 

 最后查看Trecord表中账号为“41254280033512060000”的账户信息，可知以有交易信息：

成功执行触发器代码后进行测试，插入数据成功后应该先查询交易记录表（Trecord）里的数据，然后查询账户表（account）的Balance金额是否改变，上面编写的略有一些出入。