SQL server 触发器_select @beforemoney=current money from deleted 语法错

一、什么是触发器？

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

二、触发器主要提供的功能

• 在数据库中的相关表上实现级联更改
• 事务功能，撤销或回滚违反引用完整性的操作，防止非法修改操作
• 执行比 CHECK 约束更复杂的约束操作
• 在一张表的同一类型操作（update,insert,delete）上设置多个触发器，从而可以对同样的修改语句执行不同的多种操作。

三、使用触发器的说明

• 和特定表关联，自动调用。
  
  • 当试图在某个表插入、更新或删除数据，而在那个表上定义了针对所做动作的触发器，那么触发器会自动执行。
• 只有表的拥有者才可以在表上创建或删除触发器，这种权限不许转授。
• 不能再在视图或临时表上创建触发器，但可以在触发器中引用视图或临时表。
• 是一个事务的部分。如果触发器执行不成功，则整个修改事务回滚。
• 不像普通的存储过程，触发器不能被直接调用，也不传递或接受参数，是自动调用的
• 当使用约束、规则、默认值就可以实现预定的数据完整性时，应该优先使用前3种措施。

四、触发器类型

• DELETE 触发器
• INSERT 触发器
• UPDATE 触发器
• 触发器触发时：系统自动在内存中创建deleted表或inserted表只读，不允许修改；触发器执行完成后，自动删除

• inserted 表

  • 临时保存了插入或更新后的记录
  • 行可以从inserted表中检查插入的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚操作

• deleted 表

  • 临时保存了删除或更新前的记录行
  • 可以从deleted表中检查被删除的数据是否满足业务需求， 如果不满足，则向用户报告错误消息，并回滚操作

五、创建触发器

创建触发器的语法：

CREATE TRIGGER  trigger_name
 ON  table_name
 [WITH ENCRYPTION]
  FOR/AFTER/INSTEAD OF
     [DELETE, INSERT, UPDATE]
 AS 
  T-SQL语句
GO
--WITH ENCRYPTION表示加密触发器定义的SQL文本
--DELETE, INSERT, UPDATE指定触发器的类型
1
2
3
4
5
6
7
8
9
10
11

注意： SQL Server 不允许在触发器中使用下列语句：
ALTER DATABASE、CREATE DATABASE、DISK INIT、DISK RESIZE、DROP DATABASE、LOAD DATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG

六、具体实例
一个典型的应用：银行的取款系统
1.创建帐户信息表bank和交易表transInfo

CREATE TABLE bank  --帐户信息表
(
  customerName CHAR(8) NOT NULL,  --顾客姓名
  cardID  CHAR(10) NOT NULL ,       --卡号
  currentMoney MONEY  NOT NULL     --当前余额
)
CREATE TABLE transInfo  --交易信息表
(
  cardID  CHAR(10) NOT NULL,    --卡号
  transType  CHAR(4) NOT NULL,  --交易类型（存入/支取）
  transMoney  MONEY NOT NULL,   --交易金额
  transDate  DATETIME NOT NULL, --交易日期
)
1
2
3
4
5
6
7
8
9
10
11
12
13

2.创建 INSERT 触发器
当用户想往银行卡中存入或者支出的时候，就要向transInfo表中插入一条操作数据，此时，bank 表的currentMoney 余额就应该根据操作类型进行相应的变化。

/*
创建一个transInfo 表的insert触发器，当对transInfo 表进行插入操作的时候，就会调用该触发器，将bank表中的余额改掉
*/
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 WHERE cardID=@myCardID
   ELSE
      UPDATE bank SET currentMoney=currentMoney+@outMoney WHERE cardID=@myCardID
GO 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.创建delete触发器
当删除交易信息表时，有时候需要备份要删除的数据，创建触发器，自动备份被删除的数据到表backupTable中 。

/*在交易信息表上创建DELETE触发器 
被删除的数据可以从deleted表中获取
*/
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 '备份数据成功，备份表中的数据为:'
      SELECT * FROM backupTable 
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4创建update触发器
跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示，在bank表上创建UPDATE触发器 ，修改前的数据可以从deleted表中获取，修改后的数据可以从inserted表中获取

CREATE TRIGGER trig_update_bank
 ON bank  FOR UPDATE
   AS
      DECLARE @beforeMoney MONEY,@afterMoney MONEY  
      --提示：update操作是将原有数据先删除，然后重新插入数据。
      SELECT @beforeMoney=currentMoney FROM deleted   
      SELECT @afterMoney=currentMoney FROM inserted    
      IF ABS(@afterMoney-@beforeMoney)>20000 
        BEGIN
            print '交易金额:'+convert(varchar(8),
                ABS(@afterMoney-@beforeMoney))
           print '每笔交易不能超过2万元，交易失败'
            ROLLBACK TRANSACTION
         END
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

UPDATE触发器除了跟踪数据的变化（修改）外，还可以检查是否修改了某列的数据 ，使用UPDATE（列）函数检测是否修改了某列

CREATE TRIGGER trig_update_transInfo
 ON transInfo
  FOR UPDATE
   AS
      IF UPDATE(transDate)
         BEGIN
            print '交易失败.....'
            ROLLBACK TRANSACTION    
         END
GO 
1
2
3
4
5
6
7
8
9
10
11