SQL SERVER触发器详解

目录
  • 一、触发器的定义
  • 二、触发器分类
  • 三、创建触发器
    • 1、创建insert类型的触发器
    • 2、delete触发器
    • 3、update触发器
    • 4、insteadof触发器
    • DDL触发器

触发器是一种特殊的存储过程,触发器主要是通过事件进行触发而被自动调用执行,而存储过程必须通过存储过程的名称被调用。

一、触发器的定义

触发器是在对表进行插入、更新或删除操作时自动执行的特殊存储过程。触发器通常用于强制业务规则,触发器是一种高级约束,可以定义比CHECK约束更为复杂的约束:可以执行复杂的SQL语句(if/while/case),可以引用其他表中的列。触发器定义在特定的表上,与表相关,自动触发执行,不能直接调用,是一个事务(可回滚)。

二、触发器分类

SQL SERVER中触发器可以分为两类:DML触发器和DDL触发器,DML触发器针对表,DDL触发器会影响多种数据定义语言语句而触发,这些语句有create、alter、drop语句。

DML触发器分为:

1、after触发器(之后触发)

  • a、insert触发器
  • b、update触发器
  • c、delete触发器

2、instead of触发器(之前触发)

after触发器要求只有执行某一操作(insert、update、delete)之后触发器才能被触发,且只能定义在表上。而instead of触发器表示并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身,其优先级高于触发语句的执行。

触发器有两个特殊的表:插入表(instered表)和删除表(deleted表)

这两张表是逻辑表也是虚表,触发器触发时系统自动在内存中创建这两张表,不会存储在数据库中。这两张表都是只读的,不允许修改。这两张表的结果总是与被触发器应用的表的结构相同。当触发器完成工作后,这两张表就会被删除。inserted表临时保存了插入或更新后的记录行,可以从inserted表中检查插入的数据是否满足业务需求,如果不满足,则向用户报告错误消息,并回滚插入操作。deleted表临时保存了删除或更新前的记录行,可以从deleted表中检查被删除的数据是否满足业务需求,如果不满足,则向用户报告错误消息,并回滚删除操作。update数据的时候是先删除表记录,然后插入一条记录,在inserted和deleted表就都有update后的数据记录了。

inserted表和deleted表存放的信息:


修改操作


inserted表


deleted表


增加(INSERT)记录


存放新增的记录



删除(DELETE)记录



存放被删除的记录


修改(UPDATE)记录


存放更新后的记录


存放更新前的记录

三、创建触发器

语法:

     CREATE TRIGGER trigger_name

     ON table_name

     [WITH ENCRYPTION]

     FOR [DELETE, INSERT, UPDATE]

     AS

       T-SQL语句

     GO

WITH ENCRYPTION表示加密触发器定义的SQL文本

DELETE, INSERT, UPDATE指定触发器的类型

1、创建insert类型的触发器

插入触发器

--GradeInfo表中插入一条数据,MyStudentInfo表中插入一条记录
IF (object_id('tr_insert','tr') is not null)
    drop trigger tr_insert
GO
CREATE trigger tr_insert
on GradeInfo
after insert --插入触发
as
 begin
   --定义变量
   declare @GradeId int
   --在inserted表中查询已经插入记录信息
   select @GradeId=id from INSERTED
   --MyStudentInfo表中插入数据
   insert INTO MyStudentInfo (GradeId) VALUES (@GradeId)
   print '插入成功!'
 end

插入数据

insert INTO GradeInfo VALUES (11,'C++')

查询数据

select * from MyStudentInfo where GradeId=11

2、delete触发器

删除MyStudentInfo表中的数据,插入备份表

--删除MyStudentInfo表中的数据,插入备份表
 IF (object_id('tr_Delete','tr') is not null)
    drop TRIGGER tr_Delete
 GO
 CREATE trigger tr_Delete
 on MyStudentInfo
 for delete
 as
  begin
    print '正在备份数据......'
 IF (object_id('MyStudentInfo_Back','U') is not null)
 --存在表,直接插入数据
   insert INTO MyStudentInfo_Back SELECT * from DELETED
 else
   select * into MyStudentInfo_Back from DELETED
 PRINT '备份完成'
  end

删除前查询MyStudentInfo表数据

select * from MyStudentInfo

删除id=9的数据

delete FROM MyStudentInfo where Id=9

查询备份表数据

select * from MyStudentInfo_Back

3、update触发器

  IF (object_id('tr_Update','tr') is not null)
     drop TRIGGER tr_Update
  GO
  CREATE trigger tr_Update
  on MyStudentInfo
  for update
  as
    begin
   --声明变量,存储更新前和更新后的姓名
   declare @OldName varchar(16),@NewName varchar(16)
   select @OldName=name from DELETED
   print '更新前姓名:'+@OldName
   select @NewName=name from INSERTED
   print '更新后姓名:'+@NewName
 end

把张三更新为"张三测试"

update MyStudentInfo SET Name='张三测试' where Id=1

update更新列级触发器

 --update更新列级触发器
   IF (object_id('tr_update_column','tr') is not null)
      drop TRIGGER tr_update_column
   GO
   CREATE trigger tr_update_column
   on GradeInfo
   for update
   as
     begin
    IF(update(id))
      begin
     print '系统提示:主键ID不能更新'
     rollback
   end
  end

更新id列

update GradeInfo SET Id=15 where Id=4

4、instead of触发器

instead of触发器表示并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身的内容,其优先级高于定义的SQL语句的执行

语法:

create trigger tgr_name
on table_name
with encryption
    instead of update...
as
    begin
  T-SQL
 end

创建instead of触发器

--创建instead of触发器
/*MyStudentInfo表里面插入数据之前,先判断GradeInfo表中是否有对应的班级ID,如果没有,不允许插入,如果存在,则插入 */
IF (object_id('tr_insteadOf','tr') is not null)
   drop TRIGGER tr_insteadOf
GO
CREATE trigger tr_insteadOf
on MyStudentInfo
instead of insert
as
  begin
     IF exists(SELECT * FROM GradeInfo WHERE Id=(SELECT GradeId FROM INSERTED))
      print '该班级存在,可以插入'
  else
    begin
      print '该班级不存在,不可以插入'
   rollback
    end
  end

测试1,插入不存在的班级id

insert INTO MyStudentInfo (GradeId) VALUES (15)

测试2,插入存在的班级id

insert INTO MyStudentInfo (GradeId) VALUES (5)

DDL触发器

  create trigger tr_DDL on database
  for DROP_TABLE,ALTER_TABLE
  as
    begin
      print '别想着删库!好好打你的代码'
   rollback --回滚
 end

测试删除表

drop TABLE MyStudentInfo

测试修改表结构

alter table MyStudentInfo
alter column Name varchar(32)

禁用DML触发器

disable trigger tr_insteadOf on MyStudentInfo

启用DML触发器

enable trigger tr_insteadOf on MyStudentInfo

禁用DDL触发器

disable trigger tr_DDL on database

启用DDL触发器

enable trigger tr_DDL on database

到此这篇关于SQL SERVER触发器详解的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持我们。

(0)

相关推荐

  • SQLSever中的触发器基本语法与作用

    什么是触发器? 触发器是在对表进行插入.更新或删除操作时自动执行的存储过程. 触发器对表进行插入.更新.删除的时候会自动执行的特殊存储过程.触发器一般用在check约束更加复杂的约束上面.触发器和普通的存储过程的区别是:触发器是当对某一个表进行操作.诸如:update.insert.delete这些操作的时候,系统会自动调用执行该表上对应的触发器.SQL Server 2005中触发器可以分为两类:DML触发器和DDL触发器,其中DDL触发器它们会影响多种数据定义语言语句而激发,这些语句有cre

  • 深入浅析SQL Server 触发器

    触发器是一种特殊类型的存储过程,它不同于之前的我们介绍的存储过程.触发器主要是通过事件进行触发被自动调用执行的.而存储过程可以通过存储过程的名称被调用. Ø 什么是触发器 触发器对表进行插入.更新.删除的时候会自动执行的特殊存储过程.触发器一般用在check约束更加复杂的约束上面.触发器和普通的存储过程的区别是:触发器是当对某一个表进行操作.诸如:update.insert.delete这些操作的时候,系统会自动调用执行该表上对应的触发器.SQL Server 2005中触发器可以分为两类:DM

  • SqlServer触发器详解

    触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行. 触发器经常用于加强数据的完整性约束和业务规则等. 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到.SQL3的触发器是一个能由系统自动执行对数据库修改的语句. 触发器可以查询其他表,

  • SQL Server的触发器详解

    目录 什么是触发器 DML触发器分为: 创建触发器 创建insert类型触发器 创建delete类型触发器 创建update类型触发器 update更新列级触发器 instead of类型触发器 创建instead of触发器 显示自定义消息raiserror 修改触发器 启用.禁用触发器 查询创建的触发器信息 示例,验证插入数据 示例,操作日志 总结 触发器是一种特殊类型的存储过程,它不同于之前的我们介绍的存储过程.触发器主要是通过事件进行触发被自动调用执行的.而存储过程可以通过存储过程的名称

  • SQLSERVER对加密的存储过程、视图、触发器进行解密(推荐)

    加密测试的存储过程 IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE TYPE='P' AND NAME='P_TEST') DROP PROCEDURE P_TEST GO CREATE PROCEDURE P_TEST(@USERNAME VARCHAR(20),@MSG VARCHAR(20) OUTPUT) WITH ENCRYPTION AS BEGIN IF(SELECT COUNT(1) FROM Custs WHERE NAME=@USERNAME

  • SQL Server 触发器实例详解

    Microsoft SQL Server™ 2000 提供了两种主要机制来强制业务规则和数据完整性:约束和触发器.触发器是一种特殊类型的存储过程,它不同于之前的我们介绍的存储过程.触发器主要是通过事件进行触发被自动调用执行的.而存储过程可以通过存储过程的名称被调用. Ø 什么是触发器 触发器对表进行插入.更新.删除的时候会自动执行的特殊存储过程.触发器一般用在check约束更加复杂的约束上面.触发器和普通的存储过程的区别是:触发器是当对某一个表进行操作.诸如:update.insert.dele

  • SQL Server 触发器详情

    目录 1.什么是触发器 2. DML触发器分为 2.1 after触发器(之后触发) 2.2 instead of 触发器 (之前触发) 3. 创建触发器 3.1 创建insert类型触发器 3.2  创建delete类型触发器 3.3  创建update类型触发器 3.4 update更新列级触发器 4.instead of类型触发器 4.1 创建instead of触发器 4.2  显示自定义消息raiserror 4.3  修改触发器 4.4 启用.禁用触发器 4.5 查询创建的触发器信息

  • SQL Server:触发器实例详解

    1. 概述 触发器是一种特殊的存储过程,它不能被显式地调用,而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活. 所以触发器可以用来实现对表实施复杂的完整性约束. 2. 触发器的分类 SQL Server2000提供了两种触发器:"Instead of" 和"After" 触发器. 一个表或视图的每一个修改动作(Insert.Update和Delete)都可以有一个"Instead of" 触发器,一个表的每个修改动作都可以有多个"

  • SQL Server的触发器你了解多少

    目录 什么是触发器 DML触发器分为: 创建触发器 创建insert类型触发器 创建delete类型触发器 创建update类型触发器 update更新列级触发器 instead of类型触发器 创建instead of触发器 显示自定义消息raiserror 修改触发器 启用.禁用触发器 查询创建的触发器信息 示例,验证插入数据 示例,操作日志 总结 触发器是一种特殊类型的存储过程,它不同于之前的我们介绍的存储过程.触发器主要是通过事件进行触发被自动调用执行的.而存储过程可以通过存储过程的名称

  • SQL SERVER触发器详解

    目录 一.触发器的定义 二.触发器分类 三.创建触发器 1.创建insert类型的触发器 2.delete触发器 3.update触发器 4.insteadof触发器 DDL触发器 触发器是一种特殊的存储过程,触发器主要是通过事件进行触发而被自动调用执行,而存储过程必须通过存储过程的名称被调用. 一.触发器的定义 触发器是在对表进行插入.更新或删除操作时自动执行的特殊存储过程.触发器通常用于强制业务规则,触发器是一种高级约束,可以定义比CHECK约束更为复杂的约束:可以执行复杂的SQL语句(if

  • 关于喜忧参半的SQL Server触发器详解

    前言 SQL Server触发器在非常有争议的主题.它们能以较低的成本提供便利,但经常被开发人员.DBA误用,导致性能瓶颈或维护性挑战. 本文简要回顾了触发器,并深入讨论了如何有效地使用触发器,以及何时触发器会使开发人员陷入难以逃脱的困境. 虽然本文中的所有演示都是在SQL Server中进行的,但这里提供的建议是大多数数据库通用的.触发器带来的挑战在MySQL.PostgreSQL.MongoDB和许多其他应用中也可以看到. 什么是触发器 可以在数据库或表上定义SQL Server触发器,它允

  • Java中调用SQL Server存储过程详解

    本文作者介绍了通过Java如何去调用SQL Server的存储过程,详解了5种不同的存储.详细请看下文 1.使用不带参数的存储过程 使用 JDBC 驱动程序调用不带参数的存储过程时,必须使用 call SQL 转义序列.不带参数的 call 转义序列的语法如下所示: 复制代码 代码如下: {call procedure-name} 作为实例,在 SQL Server 2005 AdventureWorks 示例数据库中创建以下存储过程: 复制代码 代码如下: CREATE PROCEDURE G

  • SQL SERVER 数据类型详解补充2

    数据类型 类型 描 述 bit 整型 bit 数据类型是整型,其值只能是0.1或空值.这种数据类型用于存储只有两种可能值的数据,如Yes 或No.True 或Fa lse .On 或Off int 整型 int 数据类型可以存储从- 231(-2147483648)到231 (2147483 647)之间的整数.存储到数据库的几乎所有数值型的数据都可以用这种数据类型.这种数据类型在数据库里占用4个字节 smallint 整型 smallint 数据类型可以存储从- 215(-32768)到215

  • Sql Server存储过程详解

    从存储过程返回数据 - SQL Server | Microsoft 官方文档 存储过程它是真正的脚本,更准确地说,它是批处理(batch),但都不是很确切,它存储与数据库而不是单独的文件中. 存储过程中有输入参数,输出参数以及返回值等. 一.创建存储过程:CREATE PROC 创建存储过程的方法除了他使用AS关键字外,和创建数据库中任何其他对象一样.存储过程的基本语法如下: 在语法中,PROC是PROCEDURE的缩写,两个选项的意思一样.在对存储过程命名完之后,接着是参数列表.参数是可选

  • ASP.NET2.0 SQL Server数据库连接详解

    Connection类有四种:SqlConnection,OleDbConnection,OdbcConnection和OracleConnection. SqlConnection类的对象连接SQL Server数据库:OracleConnection 类的对象连接Oracle数据库: OleDbConnection类的对象连接支持OLE DB的数据库,如Access:而OdbcConnection类的对象连接任何支持ODBC的数据库.与数据库的所有通讯最终都是通过Connection对象来完

  • SQL Server FileStream详解

    FILESTREAM是SQL Server 2008中的一个新特性,允许以独立文件的形式存放大对象数据,而不是以往一样将所有数据都保存到数据文件中.以往在对业务系统的文件进行管理时有两种方法,一种是将文件保存到服务器文件系统中,数据库中只保存了该文件的路径,在使用该文件时应用程序连接到服务器读取文件:另一种是将文件以varbinary(max)或image数据类型保存到SQL Server中.而SQL Server 2008提供了FILESTREAM,结合这两种方式的优点. FILESTREAM

  • sql server关键字详解大全(图文)

    下面把sqlserver中cross apply和outer apply关键字具体介绍展示如下: 1.CROSS APPLY 和OUTER APPLY MSDN解释如下(个人理解不是很清晰): 使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数.表值函数作为右输入,外部表表达式作为左输入. 通过对右输入求值来获得左输入每一行的计算结果,生成的行被组合起来作为最终输出. APPLY 运算符生成的列的列表是左输入中的列集,后跟右输入返回的列的列表. APPLY 有两种形

  • MySQL中的SQL标准语句详解

    目录 前言 对数据库的操作 对表的操作 表的创建 表的插入 表的修改 表的删除 表的查询 条件查询 前言 例如MySQL中的LIMIT语句就是MySQL独有的方言,其它数据库都不支持!当然,Oracle或SQL Server都有自己的方言. 语法要求: SQL语句可以单行或多行书写,以分号结尾: 可以用空格和缩进来来增强语句的可读性: 关键字不区别大小写,建议使用大写: 对数据库的操作 #语法: CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHAR

随机推荐