澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

Oracle数据库之PL,创建触发器

2019-07-14 作者:数据库网络   |   浏览(85)

1.触发器简介

触发器是一种特殊的存储过程,它的执行不是由程序调用,也不是手动执行,而是由事件来触发。触发器是当对某一个表进行操作。例如:update、insert、delete这些操作的时候,系统会自动调用执行该表上对应的触发器。

触发器简介:

触发器简介:

触发器是一种特殊类型的存储过程。触发器可包含复杂的T-SQL语句。触发器不能通过名称被直接调用,也不允许设置参数。它是建立在触发事件上的。

Oracle数据库之PL/SQL触发器

1. 介绍

触发器(trigger)是数据库提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作(insert,delete,update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

Oracle触发器有三种类型,分别是:DML触发器、替代触发器和系统触发器。

DML触发器

顾名思义,DML触发器是由DML语句触发的。例如数据库的INSERT、UPDATE、DELETE操作都可以触发该类型的触发器。它们可以在这些语句之前或之后触发,或者在行级上触发(就是说对于每个受影响的行都触发一次)。

替代触发器

替代触发器只能使用在视图上,与DML不同的是,DML触发器是运行在DML之外的,而替代触发器是代替激发它的DML语句运行。替代触发器是行触发器。

系统触发器

这种触发器是发生在如数据库启动或关闭等系统事件时,不是在执行DML语句时发生,当然也可以在DDL时触发。

触发器功能强大,轻松可靠地实现许多复杂的功能,但是我们也应该慎用。为什么又要慎用呢?触发器本身没有过错,但如果我们滥用,会造成数据库及应用程序的维护困难。在数据库操作中,我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作,同时约束、缺省值也是保证数据完整性的重要保障。如果我们对触发器过分的依赖,势必影响数据库的结构,同时增加了维护的复杂程度。

2. 触发器组成

触发器主要由以下几个要素组成:

  1. 触发事件:引起触发器被触发的事件。
  2. 触发时间:触发器是在触发事件发生之前(BEFORE)还是之后(AFTER)触发,也就是触发事件和该触发器的操作顺序。
  3. 触发操作:触发器被触发之后的目的和意图,是触发器本身要做的事情。
  4. 触发对象:包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件,才会执行触发操作。
  5. 触发条件:由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时,遇到触发事件才会自动执行触发器,使其执行触发操作。
  6. 触发频率:说明触发器内定义的动作被执行的频率。即语句级(STATEMENT)触发器和行级(ROW)触发器: 
    语句级(STATEMENT)触发器:是指当某触发事件发生时,该触发器只执行一次; 
    行级(ROW)触发器:是指当某触发事件发生时,对受到该操作影响的每一行数据,触发器都单独执行一次。

3. 创建触发器

语法:

CREATE [ OR REPLACE ] TRIGGER plsql_trigger_source

plsql_trigger_source ::=

[schema.] trigger_name
  { simple_dml_trigger
  | instead_of_dml_trigger
  | compound_dml_trigger
  | system_trigger
  }

simple_dml_trigger ::=

{ BEFORE | AFTER } dml_event_clause [ referencing_clause ] [ FOR EACH ROW ]
  [ trigger_edition_clause ] [ trigger_ordering_clause ]
    [ ENABLE | DISABLE ] [ WHEN ( condition ) ] trigger_body

instead_of_dml_trigger ::=

INSTEAD OF { DELETE | INSERT | UPDATE } [ OR { DELETE | INSERT | UPDATE } ]...
ON [ NESTED TABLE nested_table_column OF ] [ schema. ] noneditioning_view
[ referencing_clause ] [ FOR EACH ROW ]
[ trigger_edition_clause ] [ trigger_ordering_clause ]
[ ENABLE | DISABLE ] trigger_body

system_trigger ::=

{ BEFORE | AFTER | INSTEAD OF }
{ ddl_event [OR ddl_event]...
| database_event [OR database_event]...
}
ON { [schema.] SCHEMA
   | DATABASE
   }
[ trigger_ordering clause ]

dml_event_clause ::=

{ DELETE | INSERT | UPDATE [ OF column [, column ]... ] }
[ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] }...
ON [ schema.] { table | view }

referencing_clause ::=

REFERENCING
 { OLD [ AS ] old
 | NEW [ AS ] new
 | PARENT [ AS ] parent
 }...

trigger_body ::=

{ plsql_block | CALL routine_clause }

完整的语法结构见:

说明:

BEFORE和AFTER指出触发器的触发时间分别为前触发和后触发方式,前触发是在执行触发事件之前触发当前所创建的触发器,后触发是在执行触发事件之后触发当前所创建的触发器。

REFERENCING子句说明相关名称,在行触发器的PL/SQL块和WHEN子句中可以使用相关名称参照当前的新、旧列值,默认的相关名称为OLD和NEW。触发器的PL/SQL块中应用相关名称时,必须在它们之前加冒号(:),但在WHEN子句中则不能加冒号。

NEW只在UPDATE、INSERT的DML触发器内可用,它包含了修改发生后被影响行的值。

OLD只在UPDATE、DELETE的DML触发器内可用,它包含了修改发生前被影响行的值。

FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在:行触发器要求当一个DML语句操走影响数据库中的多行数据时,对于其中的每个数据行,只要它们符合触发约束条件,均激活一次触发器;而语句触发器将整个语句操作作为触发事件,当它符合约束条件时,激活一次触发器。当省略FOR EACH ROW 选项时,BEFORE和AFTER触发器为语句触发器,而INSTEAD OF触发器则只能为行触发器。

WHEN子句说明触发约束条件。Condition为一个逻辑表达时,其中必须包含相关名称,而不能包含查询语句,也不能调用PL/SQL函数。WHEN子句指定的触发约束条件只能用在BEFORE和AFTER行触发器中,不能用在INSTEAD OF行触发器和其它类型的触发器中。

INSTEAD OF选项(创建替代触发器)使ORACLE激活触发器,而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器,而不能对表、模式和数据库建立INSTEAD OF触发器。

ddl_event:一个或多个DDL事件,事件间用OR分开。

database_event:一个或多个数据库事件,事件间用OR分开。

示例1,在插入数据时,自动使用序列编号:

CREATE OR REPLACE TRIGGER EMP_INSERT_ID
BEFORE INSERT ON employee FOR EACH ROW
BEGIN
   SELECT SEQ_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

示例2,在多表联接的视图中插入数据:

-- 创建视图
CREATE OR REPLACE VIEW vw_emp AS
SELECT e.name ename, e.address, d.name dname
FROM employee e, dept d
WHERE e.did = d.id;

-- 创建触发器
CREATE TRIGGER emp_insert_trigger
   INSTEAD OF INSERT ON vw_emp
DECLARE
   v_did dept.id%TYPE;
BEGIN
   SELECT id INTO v_did FROM dept WHERE name = :NEW.dname;
   INSERT INTO emp (name, address, did) VALUES (:NEW.ename, :NEW.address, v_did);
END emp_insert_trigger;

示例3,创建实例启动触发器:

-- 创建记录操作事件的表
CREATE TABLE event_table(
   event VARCHAR2(50),
   time DATE
);

-- 创建触发器
CREATE OR REPLACE TRIGGER tr_startup
   AFTER STARTUP
   ON DATABASE
BEGIN
   INSERT INTO event_table(event, time)
    VALUES(ora_sysevent, SYSDATE);
END;

4. DML触发器

DML触发器对我们开发人员来说是最常用的。DML触发器是由数据库的INSERT、UPDATE、DELETE操作触发,该类触发器可以在上述语句之前或之后执行,也可以每个受影响的行执行一次。

条件谓词:当在触发器中包含多个触发事件(INSERT、UPDATE、DELETE)的组合时,为了分别针对不同的事件进行不同的处理,需要使用ORACLE提供的条件谓词:

  1. INSERTING:当触发事件是INSERT时,取值为TRUE,否则为FALSE。
  2. UPDATING [(column_1,column_2,…,column_x)]:当触发事件是UPDATE时,如果修改了column_x列,则取值为TRUE,否则为FALSE。
  3. DELETING:当触发事件是DELETE时,则取值为TRUE,否则为FALSE。

示例:

CREATE OR REPLACE TRIGGER emp_sal_trigger
   BEFORE UPDATE OF salary OR DELETE
   ON employee FOR EACH ROW
   WHEN (old.did = 1)
BEGIN
  CASE
     WHEN UPDATING ('salary') THEN
        IF :NEW.salary < :old.salary THEN
           RAISE_APPLICATION_ERROR(-20001, '部门1的员工工资不能降');
        END IF;
     WHEN DELETING THEN
          RAISE_APPLICATION_ERROR(-20002, '不能删除部门1的员工记录');
  END CASE;
END emp_sal_trigger;

5. 替代触发器

INSTEAD OF用于对视图的DML触发,由于视图有可能是由多个表联结(JOIN)而成,因而并非所有的视图都是可更新的,但可以按照所需的方式执行更新。

创建INSTEAD OF触发器需要注意以下几点:

  1. 只能被创建在视图上,并且该视图没有指定WITH CHECK OPTION选项。
  2. 不能指定BEFORE或AFTER选项。
  3. FOR EACH ROW子句是可选的。
  4. 没有必要在针对一个表的视图上创建INSTEAD OF触发器,只要创建DML触发器就可以了。

示例:

CREATE OR REPLACE TRIGGER emp_delete_trigger
   INSTEAD OF DELETE ON vw_emp FOR EACH ROW
DECLARE
   v_did dept.id%TYPE;
BEGIN
   SELEC id INTO v_did FROM dept WHERE name=:OLD.dname;
   DELETE FROM employee WHERE did= v_did;
END emp_delete_trigger;

6. 系统触发器

系统触发器可以在DDL或数据库系统上被触发,数据库系统事件包括数据库服务器的启动或关闭,用户的登录与退出、数据库服务错误等。

系统事件触发器既可以建立在一个模式上,又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时,只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器,默认时为当前用户模式。当建立在数据库(DATABASE)之上时,该数据库所有用户的DDL操作和他们所导致的错误,以及数据库的启动和关闭均可激活触发器。

系统触发器的种类和事件出现的时机:

事件 触发时机 说明
STARTUP AFTER 启动数据库实例之后触发
SHUTDOWN BEFORE 关闭数据库实例之前触发
SERVERERROR AFTER 数据库服务器发生错误之后触发
LOGON AFTER 成功登录到数据库后触发
LOGOFF BEFORE 断开数据库连接之前触发
DDL BEFORE,AFTER 在执行大多数DDL语句之前、之后触发
CREATE / ALTER / DROP BEFORE,AFTER 在执行CREATE或ALTER或DROP语句创建数据库对象之前、之后触发
RENAME BEFORE,AFTER 执行RENAME语句更改数据库对象名称之前、之后触发
GRANT / REVOKE BEFORE,AFTER 执行GRANT语句授予权限或REVOKE撤销权限之前、之后触发
AUDIT / NOAUDIT BEFORE,AFTER 执行AUDIT或NOAUDIT进行审计或停止审计之前、之后触发

示例:

-- 创建记录用户登录注销日志的表
CREATE TABLE log_on_off_log
(user_name VARCHAR2(20),
 logon_date timestamp,
 logoff_date timestamp);

-- 创建登录触发器
CREATE OR REPLACE TRIGGER logon_trigger
   AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO log_on_off_log (user_name, logon_date) VALUES (ora_login_user, systimestamp);
END logon_trigger;

-- 创建退出触发器
CREATE OR REPLACE TRIGGER logoff_trigger
   BEFORE LOGOFF ON DATABASE
BEGIN
   INSERT INTO log_on_off_log (user_name, logoff_date) VALUES (ora_login_user, systimestamp);
END logoff_trigger;

 

2.触发器类型

1、DML( 数据操纵语言 Data Manipulation Language)触发器:是指触发器在数据库中发生 DML 事件时将启用。DML事件是指在表或视图中对数据进行的 insert、update、delete 操作的语句。

2、DDL(数据定义语言 Data Definition Language)触发器:是指当服务器或数据库中发生 DDL 事件时将启用。DDL事件是指在表或索引中的 create、alter、drop 操作语句。

3、登陆触发器:是指当用户登录 SQL SERVER 实例建立会话时触发。如果身份验证失败,登录触发器不会触发。

其中 DML 触发器比较常用,根据 DML 触发器触发的方式不同又分为以下两种情况:

after 触发器(之后触发):其中 after 触发器要求只有执行 insert、update、delete 某一操作之后触发器才会被触发,且只能定义在表上。

instead of 触发器 (之前触发):instead of 触发器并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身。可以在表或视图上定义 instead of 触发器。

触发器是一种特殊的存储过程,它的执行不是由程序调用,也不是手动执行,而是由事件来触发。触发器是当对某一个表进行操作。例如:update、insert、delete这些操作的时候,系统会自动调用执行该表上对应的触发器。

触发器是一种特殊的存储过程,它的执行不是由程序调用,也不是手动执行,而是由事件来触发。触发器是当对某一个表进行操作。例如:update、insert、delete这些操作的时候,系统会自动调用执行该表上对应的触发器。

 

3.触发器语法结构

AFTER 触发器语法:

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
AS { sql_statement  [ ; ] [ ,...n ] }  

<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ EXECUTE AS Clause ]

INSTEAD OF 触发器语法:

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  

<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  

<method_specifier> ::=  
    assembly_name.class_name.method_name

DDL 触发器语法:

CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  

<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

登陆触发器语法:

CREATE [ OR ALTER ] TRIGGER trigger_name   
ON ALL SERVER   
[ WITH <logon_trigger_option> [ ,...n ] ]  
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  

<logon_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

 参数介绍:

  • CREATE OR ALTER:创建或者有条件的修改触发器(即要修改的触发器必须已经存在)
  • schema_name:ML触发器所属的模式的名称(即所有者,例如:dbo)。
  • trigger_name:触发器的名称
  • table | view:执行 DML 触发器的表或视图,有时称为触发器表或触发器视图。指定表格或视图的完全限定名称是可选的。视图只能由 INSTEAD OF 触发器引用
  • DATABASE:将 DDL 触发器的范围应用于当前数据库。如果指定,触发器会在当前数据库中发生 event_type 或 event_group 时触发。
  • ALL SERVER:将 DDL 或登录触发器的作用域应用于当前服务器。如果指定,触发器会在当前服务器的任何地方发生 event_type 或 event_group 时触发
  • WITH ENCRYPTION:加密 CREATE TRIGGER 语句的文本。使用 WITH ENCRYPTION 可以防止触发器作为 SQL Server 复制的一部分进行发布。无法为 CLR 触发器指定 WITH ENCRYPTION。
  • EXECUTE AS:指定执行触发器的安全上下文。以便能够控制 SQL Server 实例用于验证触发器引用的任何数据库对象的权限的用户帐户。
  • NATIVE_COMPILATION:表示触发器是本地编译的。
  • SCHEMABINDING:指定触发器引用的表不能被删除或更改。
  • FOR | AFTER:AFTER 指定仅在触发 SQL 语句中指定的所有操作成功执行时触发 DML 触发器。所有引用级联操作和约束检查在此触发器触发之前也必须成功。当 FOR 是指定的唯一关键字时,AFTER 是默认值。视图无法定义AFTER触发器。
  • INSTEAD OF:指定执行 DML 触发器而不是触发 SQL 语句,因此覆盖触发语句的操作。无法为 DDL 或登录触发器指定 INSTEAD OF。对于 INSTEAD OF 触发器,在具有指定级联动作 ON DELETE 的引用关系的表上不允许使用 DELETE 选项。类似地,在具有指定级联动作 ON UPDATE 的引用关系的表上,不允许 UPDATE 选项。
  • {[DELETE] [,] [INSERT] [,] [UPDATE]} :指定在针对此表或视图进行尝试时激活 DML 触发器的数据修改语句。必须至少指定一个选项。在触发器定义中允许以任何顺序对这些选项进行任意组合。
  • event_type:是执行后导致 DDL 触发器触发的 Transact-SQL 语言事件的名称。
  • event_group:是 Transact-SQL 语言事件的预定义分组的名称。属于任何 Transact-SQL 语言事件执行后的 DDL 触发器触发 event_group。
  • sql_statement:是触发条件和动作。触发条件指定附加条件,以确定尝试的 DML,DDL 或登录事件是否导致执行触发器操作。
  • method_specifier:对于 CLR 触发器,指定要与触发器绑定的程序集的方法。该方法不得不引用任何参数并返回 void。class_name 必须是有效的 SQL Server 标识符,并且必须作为具有程序集可见性的程序集中的类存在。

触发器分类:

触发器分类:

触发器可以强制执行一定的业务规则,以保持数据完整性、检查数据有效性、实现数据库管理任务和一些附加功能。

DML触发器例子

  1. 创建AFTER INSERT 触发器:当下单后更新tbOrderTotalPrice 的统计信息

准备一张订单表(tbOrder),包含订单ID,订单交易金额,订单创建时间,准备订单交易分段时间总额(tbOrderTotalPrice ),用来统计每个月的交易合计金额

澳门新萄京官方网站 1澳门新萄京官方网站 2

USE [TEST]
GO

/****** Object:  Trigger [dbo].[tSumTotalOrderPrice]    Script Date: 2018/5/15 11:10:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tSumTotalOrderPrice] on [dbo].[tbOrder]
AFTER INSERT
AS
BEGIN
 DECLARE @DT INT
 SET @DT= CONVERT(varchar(6),GETDATE(),112)

 DECLARE @SUM DECIMAL(18, 2)
 SET @SUM=(SELECT SUM(OPrice) FROM tbOrder WHERE @DT = CONVERT(varchar(6),CreateDT,112))

 IF(EXISTS(SELECT * FROM tbOrderTotalPrice WHERE YearMoth=@DT))
 BEGIN
  UPDATE tbOrderTotalPrice SET TotalPrice=@SUM WHERE YearMoth=@DT
 END
 ELSE
 BEGIN
  INSERT INTO tbOrderTotalPrice VALUES (@DT,@SUM)
 END
END

GO

执行INSERT 行为:INSERT INTO [dbo].[tbOrder]([OPrice],[CreateDT])VALUES(9,GETDATE())

澳门新萄京官方网站 3

澳门新萄京官方网站 4

 2.创建 instead of  insert 触发器,当添加用户成绩记录时候,验证学号和学科存在才能进行添加操作

准备学生表(tbStudent),学科表(tbSubject),成绩表(tbStuSubScore)

澳门新萄京官方网站 5 澳门新萄京官方网站 6 

澳门新萄京官方网站 7

在学生表中添加测试学生,在学科标准添加测试学科

澳门新萄京官方网站 8 澳门新萄京官方网站 9

新增instead of insert 触发器,在执行INSERT 语句前验证学号和学科存在才能进行添加操作

USE [TEST]
GO

/****** Object:  Trigger [dbo].[tCheckStuSubScore]    Script Date: 2018/5/17 14:20:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  yangyi
-- Create date: 2018/5/17
-- Description: 新增学科分数
-- =============================================
CREATE TRIGGER [dbo].[tCheckStuSubScore] ON [dbo].[tbStuSubScore]
   INSTEAD OF INSERT
AS 
BEGIN
 --验证学号是否存在
 IF(NOT EXISTS(SELECT * FROM tbStudent WHERE StuID=(SELECT StuID FROM inserted)))
 BEGIN
  PRINT('验证学号不否存在')
  ROLLBACK TRANSACTION
 END
 ELSE
 BEGIN
  --验证学科是否存在
  IF(NOT EXISTS(SELECT * FROM tbSubject WHERE SubID=(SELECT SubID FROM inserted)))
  BEGIN
   PRINT('验证学科不否存在')
   ROLLBACK TRANSACTION
  END
  ELSE
  BEGIN
   --执行INSERT
   INSERT INTO tbStuSubScore SELECT * FROM inserted
  END
 END
END

GO

注意:

在触发器语句中用两个特殊的表一个是deleted表和inserted。它们是通过触发器操作自动创建驻留在内存中的临时表。
Deleted表用于存储 DELETE和 UPDATE语句所影响的行的复本。在执行DELETE或 UPDATE语句时,行从触发器表中删除,并传输到 deleted表中。Deleted表和触发器表通常没有相同的行。
Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中,新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本

1.插入操作(Insert) Inserted表有数据,Deleted表无数据
2.删除操作(Delete) Inserted表无数据,Deleted表有数据
3.更新操作(Update) Inserted表有数据(新数据),Deleted表有数据(旧数据)

测试:

1>.执行INSERT 添加一条学科不存不正常在的学生成绩记录,执行失败

INSERT INTO [dbo].[tbStuSubScore]([StuID],[Score],[SubID],[CreateDT])VALUES('001',100,0,GETDATE())  

2.>执行INSERT 添加一条学号不存不正常在的学生成绩记录,执行失败

INSERT INTO [dbo].[tbStuSubScore]([StuID],[Score],[SubID],[CreateDT])VALUES('004',100,1,GETDATE())

澳门新萄京官方网站 10 澳门新萄京官方网站 11

3.>执行一条正常的学生成绩记录,执行成功

INSERT INTO [dbo].[tbStuSubScore]([StuID],[Score],[SubID],[CreateDT])VALUES('001',100,1,GETDATE())

澳门新萄京官方网站 12

1、DML( 数据操纵语言 Data Manipulation Language)触发器:是指触发器在数据库中发生 DML 事件时将启用。DML事件是指在表或视图中对数据进行的 insert、update、delete 操作的语句。

1、DML( 数据操纵语言 Data Manipulation Language)触发器:是指触发器在数据库中发生 DML 事件时将启用。DML事件是指在表或视图中对数据进行的 insert、update、delete 操作的语句。

 

2、DDL(数据定义语言 Data Definition Language)触发器:是指当服务器或数据库中发生 DDL 事件时将启用。DDL事件是指在表或索引中的 create、alter、drop 操作语句。

2、DDL(数据定义语言 Data Definition Language)触发器:是指当服务器或数据库中发生 DDL 事件时将启用。DDL事件是指在表或索引中的 create、alter、drop 操作语句。

触发器的分类: DML、 DDL、 登录触发器

3、登陆触发器:是指当用户登录 SQL SERVER 实例建立会话时触发。如果身份验证失败,登录触发器不会触发。

3、登陆触发器:是指当用户登录 SQL SERVER 实例建立会话时触发。如果身份验证失败,登录触发器不会触发。

 

其中 DML 触发器比较常用,根据 DML 触发器触发的方式不同又分为以下两种情况:

其中 DML 触发器比较常用,根据 DML 触发器触发的方式不同又分为以下两种情况:

 

after 触发器(之后触发):其中 after 触发器要求只有执行 insert、update、delete 某一操作之后触发器才会被触发,且只能定义在表上。

after 触发器(之后触发):其中 after 触发器要求只有执行 insert、update、delete 某一操作之后触发器才会被触发,且只能定义在表上。

创建触发器需要指定的选项:

instead of 触发器 (之前触发):instead of 触发器并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身。可以在表或视图上定义 instead of 触发器。

instead of 触发器 (之前触发):instead of 触发器并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身。可以在表或视图上定义 instead of 触发器。

 

DML 触发器有两个特殊的表:插入表(instered)和删除表(deleted),这两张表是逻辑表。这两个表是建立在数据库服务器的内存中,而且两张表的都是只读的。这两张表的结构和触发器所在的数据表的结构是一样的。当触发器完成工作后,这两张表就会被删除。Inserted 表的数据是插入或是修改后的数据,而 deleted 表的数据是更新前的或是已删除的数据。

DML 触发器有两个特殊的表:插入表(instered)和删除表(deleted),这两张表是逻辑表。这两个表是建立在数据库服务器的内存中,而且两张表的都是只读的。这两张表的结构和触发器所在的数据表的结构是一样的。当触发器完成工作后,这两张表就会被删除。Inserted 表的数据是插入或是修改后的数据,而 deleted 表的数据是更新前的或是已删除的数据。

 1.触发器的名称。

澳门新萄京官方网站 13

澳门新萄京官方网站 14

 2.在其上定义触发器的表。

AFTER 触发器语法:

AFTER 触发器语法:

 3.触发器将何时激发。

 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 AS { sql_statement  [ ; ] [ ,...n ] }  
 7 
 8 <dml_trigger_option> ::=  
 9     [ NATIVE_COMPILATION ]  
10     [ SCHEMABINDING ]  
11     [ EXECUTE AS Clause ]
 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 AS { sql_statement  [ ; ] [ ,...n ] }  
 7 
 8 <dml_trigger_option> ::=  
 9     [ NATIVE_COMPILATION ]  
10     [ SCHEMABINDING ]  
11     [ EXECUTE AS Clause ]

 4.激活触发器的数据修改语句。

INSTEAD OF 触发器语法:

INSTEAD OF 触发器语法:

 5.执行触发操作的编程语句。

 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table | view }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER | INSTEAD OF }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 [ WITH APPEND ]  
 7 [ NOT FOR REPLICATION ]   
 8 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  
 9 
10 <dml_trigger_option> ::=  
11     [ ENCRYPTION ]  
12     [ EXECUTE AS Clause ]  
13 
14 <method_specifier> ::=  
15     assembly_name.class_name.method_name  
 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table | view }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER | INSTEAD OF }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 [ WITH APPEND ]  
 7 [ NOT FOR REPLICATION ]   
 8 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  
 9 
10 <dml_trigger_option> ::=  
11     [ ENCRYPTION ]  
12     [ EXECUTE AS Clause ]  
13 
14 <method_specifier> ::=  
15     assembly_name.class_name.method_name  

 

DDL 触发器语法:

DDL 触发器语法:

 

1 CREATE [ OR ALTER ] TRIGGER trigger_name   
2 ON { ALL SERVER | DATABASE }   
3 [ WITH <ddl_trigger_option> [ ,...n ] ]  
4 { FOR | AFTER } { event_type | event_group } [ ,...n ]  
5 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
6 
7 <ddl_trigger_option> ::=  
8     [ ENCRYPTION ]  
9     [ EXECUTE AS Clause ]  
1 CREATE [ OR ALTER ] TRIGGER trigger_name   
2 ON { ALL SERVER | DATABASE }   
3 [ WITH <ddl_trigger_option> [ ,...n ] ]  
4 { FOR | AFTER } { event_type | event_group } [ ,...n ]  
5 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
6 
7 <ddl_trigger_option> ::=  
8     [ ENCRYPTION ]  
9     [ EXECUTE AS Clause ]  

CREATE TRIGGER语句基本语法格式如下:

登陆触发器语法:

登陆触发器语法:

 

1 CREATE [ OR ALTER ] TRIGGER trigger_name   
2 ON ALL SERVER   
3 [ WITH <logon_trigger_option> [ ,...n ] ]  
4 { FOR| AFTER } LOGON    
5 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
6 
7 <logon_trigger_option> ::=  
8     [ ENCRYPTION ]  
9     [ EXECUTE AS Clause ]  
1 CREATE [ OR ALTER ] TRIGGER trigger_name   
2 ON ALL SERVER   
3 [ WITH <logon_trigger_option> [ ,...n ] ]  
4 { FOR| AFTER } LOGON    
5 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
6 
7 <logon_trigger_option> ::=  
8     [ ENCRYPTION ]  
9     [ EXECUTE AS Clause ]  

CREATE TRIGGER     触发器名称

参数:

参数:

ON {表名 | 视图名}

CREATE OR ALTER:

CREATE OR ALTER:

[with encryption]

创建或者有条件的修改触发器(即要修改的触发器必须已经存在)。

创建或者有条件的修改触发器(即要修改的触发器必须已经存在)。

{

schema_name:
DML触发器所属的模式的名称(即所有者,例如:dbo)。

schema_name:
DML触发器所属的模式的名称(即所有者,例如:dbo)。

   { {FOR | AFTER | INSTEAD OF}

trigger_name:
是触发器的名称。

trigger_name:
是触发器的名称。

   {[DELETE] [,][INSERT] [,] [UPDATE]}

table | view:

table | view:

   AS

是执行 DML 触发器的表或视图,有时称为触发器表或触发器视图。指定表格或视图的完全限定名称是可选的。视图只能由 INSTEAD OF 触发器引用。

是执行 DML 触发器的表或视图,有时称为触发器表或触发器视图。指定表格或视图的完全限定名称是可选的。视图只能由 INSTEAD OF 触发器引用。

   sql_statement […n ]

DATABASE:
将 DDL 触发器的范围应用于当前数据库。如果指定,触发器会在当前数据库中发生 event_type 或 event_group 时触发。

DATABASE:
将 DDL 触发器的范围应用于当前数据库。如果指定,触发器会在当前数据库中发生 event_type 或 event_group 时触发。

ALL SERVER:

ALL SERVER:

 

将 DDL 或登录触发器的作用域应用于当前服务器。如果指定,触发器会在当前服务器的任何地方发生 event_type 或 event_group 时触发。

将 DDL 或登录触发器的作用域应用于当前服务器。如果指定,触发器会在当前服务器的任何地方发生 event_type 或 event_group 时触发。

其中:

WITH ENCRYPTION:

WITH ENCRYPTION:

AFTER

加密 CREATE TRIGGER 语句的文本。使用 WITH ENCRYPTION 可以防止触发器作为 SQL Server 复制的一部分进行发布。无法为 CLR 触发器指定 WITH ENCRYPTION。

加密 CREATE TRIGGER 语句的文本。使用 WITH ENCRYPTION 可以防止触发器作为 SQL Server 复制的一部分进行发布。无法为 CLR 触发器指定 WITH ENCRYPTION。

指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后,才能执行此触发器。如果仅指定 FOR 关键字,则 AFTER 是默认设置。

EXECUTE AS:
指定执行触发器的安全上下文。以便能够控制 SQL Server 实例用于验证触发器引用的任何数据库对象的权限的用户帐户。

EXECUTE AS:
指定执行触发器的安全上下文。以便能够控制 SQL Server 实例用于验证触发器引用的任何数据库对象的权限的用户帐户。

INSTEAD OF

NATIVE_COMPILATION:
表示触发器是本地编译的。

NATIVE_COMPILATION:
表示触发器是本地编译的。

指定执行触发器而不是执行触发 SQL 语句,从而替代触发语句的操作。

SCHEMABINDING:
指定触发器引用的表不能被删除或更改。

SCHEMABINDING:
指定触发器引用的表不能被删除或更改。

 

FOR | AFTER:
AFTER 指定仅在触发 SQL 语句中指定的所有操作成功执行时触发 DML 触发器。所有引用级联操作和约束检查在此触发器触发之前也必须成功。当 FOR 是指定的唯一关键字时,AFTER 是默认值。视图无法定义AFTER触发器。

FOR | AFTER:
AFTER 指定仅在触发 SQL 语句中指定的所有操作成功执行时触发 DML 触发器。所有引用级联操作和约束检查在此触发器触发之前也必须成功。当 FOR 是指定的唯一关键字时,AFTER 是默认值。视图无法定义AFTER触发器。

例如:

INSTEAD OF:
指定执行 DML 触发器而不是触发 SQL 语句,因此覆盖触发语句的操作。无法为 DDL 或登录触发器指定 INSTEAD OF。

INSTEAD OF:
指定执行 DML 触发器而不是触发 SQL 语句,因此覆盖触发语句的操作。无法为 DDL 或登录触发器指定 INSTEAD OF。

 1 /*在student表上创建触发器,
 2 在用户插入、修改和删除记录时,都会自动显示表中的内容:*/
 3 
 4 use test
 5 go
 6 
 7 create trigger trig_1 on student
 8 after insert,delete,update
 9 as 
10 begin
11     set nocount on
12     select * from student
13 end
14 
15 insert student(sno) values(5)
16 
17 delete student where sno=5
18 
19 
20 exec sp_helptext trig_1    --查看触发器内容 
21 exec sp_helptrigger student   --查看表上的触发器的属性
22 select * from sysobjects where xtype='TR'  --查看数据库中已有的触发器
23 
24 
25 drop trigger trig_1

对于 INSTEAD OF 触发器,在具有指定级联动作 ON DELETE 的引用关系的表上不允许使用 DELETE 选项。类似地,在具有指定级联动作 ON UPDATE 的引用关系的表上,不允许 UPDATE 选项。

对于 INSTEAD OF 触发器,在具有指定级联动作 ON DELETE 的引用关系的表上不允许使用 DELETE 选项。类似地,在具有指定级联动作 ON UPDATE 的引用关系的表上,不允许 UPDATE 选项。

 

{[DELETE] [,] [INSERT] [,] [UPDATE]} :
指定在针对此表或视图进行尝试时激活 DML 触发器的数据修改语句。必须至少指定一个选项。在触发器定义中允许以任何顺序对这些选项进行任意组合。

{[DELETE] [,] [INSERT] [,] [UPDATE]} :
指定在针对此表或视图进行尝试时激活 DML 触发器的数据修改语句。必须至少指定一个选项。在触发器定义中允许以任何顺序对这些选项进行任意组合。

 

event_type:
是执行后导致 DDL 触发器触发的 Transact-SQL 语言事件的名称。

event_type:
是执行后导致 DDL 触发器触发的 Transact-SQL 语言事件的名称。

inserted表和deleted表

event_group:
是 Transact-SQL 语言事件的预定义分组的名称。属于任何 Transact-SQL 语言事件执行后的 DDL 触发器触发 event_group。

event_group:
是 Transact-SQL 语言事件的预定义分组的名称。属于任何 Transact-SQL 语言事件执行后的 DDL 触发器触发 event_group。

 

sql_statement:
是触发条件和动作。触发条件指定附加条件,以确定尝试的 DML,DDL 或登录事件是否导致执行触发器操作。

sql_statement:
是触发条件和动作。触发条件指定附加条件,以确定尝试的 DML,DDL 或登录事件是否导致执行触发器操作。

触发器执行的时候,产生两个临时表:inserted表和deleted表。它们的结构和所在的表的结构相同,可使用这两个表测试某些数据修改的效果和设置触发器操作的条件,但不能对表中的数据进行更改。

<method_specifier>:

<method_specifier>:

deleted表用于存储DELETE和UPDATE语句所影响的行的副本。在执行delete或update语句时,行从触发器表中删除,并传输到deleted表中。

对于 CLR 触发器,指定要与触发器绑定的程序集的方法。该方法不得不引用任何参数并返回 void。class_name 必须是有效的 SQL Server 标识符,并且必须作为具有程序集可见性的程序集中的类存在。

对于 CLR 触发器,指定要与触发器绑定的程序集的方法。该方法不得不引用任何参数并返回 void。class_name 必须是有效的 SQL Server 标识符,并且必须作为具有程序集可见性的程序集中的类存在。

inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在插入和更新时,新建行被同时添加到inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。

 

 

 

以下是DML触发器的使用,先看看示例数据:

以下是DML触发器的使用,先看看示例数据:

在对具有触发器的表(触发器表)进行操作时,有:

澳门新萄京官方网站 15

澳门新萄京官方网站 16

执行INSERT操作,插入到触发器表中的新行被插入到inserted表中。

澳门新萄京官方网站, 

 

执行DELETE操作,从触发器表中删除的行被插入到deleted表中。

insert 触发器:

insert 触发器:

执行UPDATE操作,先从触发器表中删除旧行,然后再插入新行。删除的旧行插入到deleted表中;更改后的新行被插入到inserted 表中。

 1 if(OBJECT_ID('trigger_Stu_Insert') is not null)        -- 判断名为 trigger_Stu_Insert 的触发器是否存在
 2 drop trigger trigger_Stu_Insert        -- 删除触发器
 3 go
 4 create trigger trigger_Stu_Insert
 5 on Student        -- 指定创建触发器的表
 6 for insert        -- insert 触发器,也可以写为 after insert
 7 as
 8 
 9 declare @C_Id    int
10 declare @S_Id    int
11 
12 select @C_Id=C_Id from Course where C_Name='SQL'    -- 获取课程为 SQL 的ID
13 select @S_Id=S_Id from inserted        --插入一条学生的数据,那么这条数据就存在 inserted 这个表中
14 
15 select @C_Id
16 select @S_Id
17 
18 select * from inserted
19 
20 update Student set C_S_Id=@C_Id where S_Id=@S_Id
21 go
22 
23 insert into Student(S_StuNo,S_Name,S_Sex,S_Height,S_BirthDate)
24 values('016','大熊','男','210','2017-01-01')
25 
26 select * from Student
27 select * from Course
 1 if(OBJECT_ID('trigger_Stu_Insert') is not null)        -- 判断名为 trigger_Stu_Insert 的触发器是否存在
 2 drop trigger trigger_Stu_Insert        -- 删除触发器
 3 go
 4 create trigger trigger_Stu_Insert
 5 on Student        -- 指定创建触发器的表
 6 for insert        -- insert 触发器,也可以写为 after insert
 7 as
 8 
 9 declare @C_Id    int
10 declare @S_Id    int
11 
12 select @C_Id=C_Id from Course where C_Name='SQL'    -- 获取课程为 SQL 的ID
13 select @S_Id=S_Id from inserted        --插入一条学生的数据,那么这条数据就存在 inserted 这个表中
14 
15 select @C_Id
16 select @S_Id
17 
18 select * from inserted
19 
20 update Student set C_S_Id=@C_Id where S_Id=@S_Id
21 go
22 
23 insert into Student(S_StuNo,S_Name,S_Sex,S_Height,S_BirthDate)
24 values('016','大熊','男','210','2017-01-01')
25 
26 select * from Student
27 select * from Course

 

澳门新萄京官方网站 17

澳门新萄京官方网站 18

 

澳门新萄京官方网站 19

澳门新萄京官方网站 20

 

这个例子是:当 Student 表新增一条数据时,修改这条数据的课程ID。

这个例子是:当 Student 表新增一条数据时,修改这条数据的课程ID。

使用DML触发器

delete 触发器:

delete 触发器:

 

 1 if(OBJECT_ID('trigger_Stu_Delete') is not null)        -- 判断名为 trigger_Stu_Delete 的触发器是否存在
 2 drop trigger trigger_Stu_Delete        -- 删除触发器
 3 go
 4 create trigger trigger_Stu_Delete
 5 on Student        -- 指定创建触发器的表
 6 for delete        -- delete 触发器,也可以写为 after delete
 7 as
 8 
 9 declare @C_S_Id    int
10 
11 select @C_S_Id=C_S_Id from deleted        --删除的学生的数据就存在 deleted 这个表中
12 
13 select @C_S_Id
14 
15 select * from deleted
16 
17 delete from Course where C_Id=@C_S_Id        -- 删除具有删除的学生的课程ID的课程
18 go
19 
20 delete from Student where C_S_Id='1'
21 
22 select * from Student
23 select * from Course
 1 if(OBJECT_ID('trigger_Stu_Delete') is not null)        -- 判断名为 trigger_Stu_Delete 的触发器是否存在
 2 drop trigger trigger_Stu_Delete        -- 删除触发器
 3 go
 4 create trigger trigger_Stu_Delete
 5 on Student        -- 指定创建触发器的表
 6 for delete        -- delete 触发器,也可以写为 after delete
 7 as
 8 
 9 declare @C_S_Id    int
10 
11 select @C_S_Id=C_S_Id from deleted        --删除的学生的数据就存在 deleted 这个表中
12 
13 select @C_S_Id
14 
15 select * from deleted
16 
17 delete from Course where C_Id=@C_S_Id        -- 删除具有删除的学生的课程ID的课程
18 go
19 
20 delete from Student where C_S_Id='1'
21 
22 select * from Student
23 select * from Course

1. INSERT和UPDATE触发器

澳门新萄京官方网站 21

澳门新萄京官方网站 22

     当向表中插入或者更新记录时,INSERT或者UPDATE触发器被激活。一般情况下,这两种触发器常用来检查插入或者修改后的数据是否满足要求。 

澳门新萄京官方网站 23

澳门新萄京官方网站 24

INSERT触发器被触发时,新的记录增加到触发器的对应表中,并且同时也添加到一个inserted表中。

这个例子是:删除指定课程ID的学生时,并删除指定课程ID的课程。

这个例子是:删除指定课程ID的学生时,并删除指定课程ID的课程。

修改一个记录等于插入了一个新的记录并且删除一个旧的记录。当在一个有UPDATE触发器的表中修改记录时,表中原来的记录被移动到deleted表中,修改过的记录插入到了插入表中,触发器可以参考deleted表和inserted表以及被修改的表,以确定如何完成数据库操作。

update 触发器:

update 触发器:

 

 1 if(OBJECT_ID('trigger_Cou_Update') is not null)        -- 判断名为 trigger_Cou_Update 的触发器是否存在
 2 drop trigger trigger_Cou_Update        -- 删除触发器
 3 go
 4 create trigger trigger_Cou_Update
 5 on Course        -- 指定创建触发器的表
 6 for update        -- update 触发器,也可以写为 after update
 7 as
 8 
 9 declare @C_Id    int
10 
11 select @C_Id=C_Id from deleted        
12 
13 select * from deleted        -- 修改前的数据就存在 deleted 这个表中
14 
15 select * from inserted        -- 修改后的数据就存在 inserted 这个表中
16 
17 update Student set C_S_Id=@C_Id where C_S_Id is null 
18 go
19 
20 update Course set C_Name='C#' where C_Id='4'
21 
22 select * from Student
23 select * from Course
 1 if(OBJECT_ID('trigger_Cou_Update') is not null)        -- 判断名为 trigger_Cou_Update 的触发器是否存在
 2 drop trigger trigger_Cou_Update        -- 删除触发器
 3 go
 4 create trigger trigger_Cou_Update
 5 on Course        -- 指定创建触发器的表
 6 for update        -- update 触发器,也可以写为 after update
 7 as
 8 
 9 declare @C_Id    int
10 
11 select @C_Id=C_Id from deleted        
12 
13 select * from deleted        -- 修改前的数据就存在 deleted 这个表中
14 
15 select * from inserted        -- 修改后的数据就存在 inserted 这个表中
16 
17 update Student set C_S_Id=@C_Id where C_S_Id is null 
18 go
19 
20 update Course set C_Name='C#' where C_Id='4'
21 
22 select * from Student
23 select * from Course

2. DELETE触发器

澳门新萄京官方网站 25

澳门新萄京官方网站 26

DELETE触发器通常用于下面的情况:

澳门新萄京官方网站 27

澳门新萄京官方网站 28

防止那些确实要删除,但是可能会引起数据一致性问题的情况,一般是用于那些用作其他表的外部键记录。

这个例子是:修改课程名称时,把课程ID为空(null)的学生的课程ID默认为修改的课程ID。

这个例子是:修改课程名称时,把课程ID为空(null)的学生的课程ID默认为修改的课程ID。

用于级联删除操作。

Oracle数据库之PL,创建触发器。禁止修改学生学号触发器,触发器进行数据回滚:

禁止修改学生学号触发器,触发器进行数据回滚:

 

 1 if(OBJECT_ID('trigger_Stu_Update') is not null)        -- 判断名为 trigger_Stu_Update 的触发器是否存在
 2 drop trigger trigger_Stu_Update        -- 删除触发器
 3 go
 4 create trigger trigger_Stu_Update
 5 on Student        -- 指定创建触发器的表
 6 for update        -- update 触发器,也可以写为 after update
 7 as
 8 begin try
 9     if(UPDATE(S_StuNo))        -- 列级触发器:判断是否更新了学生学号(学号不允许更改)
10     begin
11         raiserror(66666,16,1)
12     end
13 end try
14 begin catch
15     select * from deleted        -- 修改前的数据就存在 deleted 这个表中
16     select * from inserted        -- 修改后的数据就存在 inserted 这个表中
17     rollback tran;
18 end catch
19 go
20 
21 update  Student set S_StuNo='006' where S_Id='20'
22 
23 select * from Student
 1 if(OBJECT_ID('trigger_Stu_Update') is not null)        -- 判断名为 trigger_Stu_Update 的触发器是否存在
 2 drop trigger trigger_Stu_Update        -- 删除触发器
 3 go
 4 create trigger trigger_Stu_Update
 5 on Student        -- 指定创建触发器的表
 6 for update        -- update 触发器,也可以写为 after update
 7 as
 8 begin try
 9     if(UPDATE(S_StuNo))        -- 列级触发器:判断是否更新了学生学号(学号不允许更改)
10     begin
11         raiserror(66666,16,1)
12     end
13 end try
14 begin catch
15     select * from deleted        -- 修改前的数据就存在 deleted 这个表中
16     select * from inserted        -- 修改后的数据就存在 inserted 这个表中
17     rollback tran;
18 end catch
19 go
20 
21 update  Student set S_StuNo='006' where S_Id='20'
22 
23 select * from Student

例如:

澳门新萄京官方网站 29

澳门新萄京官方网站 30

 1 /*例:下例说明inserted表和deleted表的作用*/
 2 
 3 if exists(select name from sysobjects where name='trig_2' and type='TR')
 4     drop trigger trig_2
 5 go
 6 
 7 create trigger trig_2 
 8 on student
 9 after update                         --update触发器
10 as 
11     print 'inserted表'
12     select * from inserted
13     print 'deleted表'
14     select * from deleted
15 go
16 set nocount on
17 update student set sname='关二' where sno=2
18 --drop trigger trig_2
19 
20 
21 create trigger trig_3 
22 on student
23 after insert                         --insert触发器
24 as 
25     print 'inserted表'
26     select * from inserted
27     print 'deleted表'
28     select * from deleted
29 go
30 insert student values(100,'刘一百','男',25)
31 drop trigger trig_3
32 
33 
34 create trigger trig_4 
35 on student
36 after delete                         --delete触发器
37 as 
38     print 'inserted表'
39     select * from inserted
40     print 'deleted表'
41     select * from deleted
42 go
43 delete student where sno=100
44 drop trigger trig_4

after 触发器可以指定多个操作都可以触发该触发器。只需要在 for/after 后面添加逗号和触发器的类型,例如:

after 触发器可以指定多个操作都可以触发该触发器。只需要在 for/after 后面添加逗号和触发器的类型,例如:

 

1 for update,insert,delete 
2 
3 after update,insert,delete 
1 for update,insert,delete 
2 
3 after update,insert,delete 

 

instead of 触发器:

instead of 触发器:

修改触发器

这个触发器就好玩了,下面先看看数据。

这个触发器就好玩了,下面先看看数据。

 

澳门新萄京官方网站 31

澳门新萄京官方网站 32

语法格式:

 1 if(OBJECT_ID('trigger_Stu_InsteadOf') is not null)        -- 判断名为 trigger_Stu_InsteadOf 的触发器是否存在
 2 drop trigger trigger_Stu_InsteadOf        -- 删除触发器
 3 go
 4 create trigger trigger_Stu_InsteadOf
 5 on Student        -- 指定创建触发器的表
 6 instead of update,insert,delete         -- instead of 触发器
 7 as
 8     select * from deleted        -- 修改前的数据就存在 deleted 这个表中
 9     select * from inserted        -- 修改后的数据就存在 inserted 这个表中
10 go
11 
12 update Student set S_StuNo='006' where S_Id='20'
13 
14 insert into Student([S_StuNo],[S_Name],[S_Sex],[S_Height],[S_BirthDate])
15 values('017','清红','女','180','2017-01-01')
16 
17 delete from Student where C_S_Id='5'
18 
19 select * from Student
 1 if(OBJECT_ID('trigger_Stu_InsteadOf') is not null)        -- 判断名为 trigger_Stu_InsteadOf 的触发器是否存在
 2 drop trigger trigger_Stu_InsteadOf        -- 删除触发器
 3 go
 4 create trigger trigger_Stu_InsteadOf
 5 on Student        -- 指定创建触发器的表
 6 instead of update,insert,delete         -- instead of 触发器
 7 as
 8     select * from deleted        -- 修改前的数据就存在 deleted 这个表中
 9     select * from inserted        -- 修改后的数据就存在 inserted 这个表中
10 go
11 
12 update Student set S_StuNo='006' where S_Id='20'
13 
14 insert into Student([S_StuNo],[S_Name],[S_Sex],[S_Height],[S_BirthDate])
15 values('017','清红','女','180','2017-01-01')
16 
17 delete from Student where C_S_Id='5'
18 
19 select * from Student

ALTER TRIGGER trigger_name

澳门新萄京官方网站 33

澳门新萄京官方网站 34

ON ( table | view )

执行上面的语句之后,咦,数据怎么一点变化都没有?看看上面的介绍。instead of 触发器是之前触发。

执行上面的语句之后,咦,数据怎么一点变化都没有?看看上面的介绍。instead of 触发器是之前触发。

{

instead of 触发器并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身,并且会覆盖触发语句的操作,即 after 触发器 T-SQL 语句的操作,很明显我们上面定义的表 Student 的 after 触发器也没有效果了,现在理解了这句话了吧。

instead of 触发器并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身,并且会覆盖触发语句的操作,即 after 触发器 T-SQL 语句的操作,很明显我们上面定义的表 Student 的 after 触发器也没有效果了,现在理解了这句话了吧。

  { ( FOR | AFTER | INSTEAD OF ) }

修改触发器:

修改触发器:

  { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }

 1 alter trigger trigger_Stu_InsteadOf        -- 修改触发器
 2 on Student        -- 指定创建触发器的表
 3 instead of update,insert,delete         -- instead of 触发器
 4 as
 5     declare @Count1 int
 6     declare @Count2 int
 7 
 8     select @Count1=COUNT(1) from deleted        
 9     select @Count2=COUNT(1) from inserted        
10 
11     if(@Count1>0 and @Count2>0)
12     begin
13         select 'update操作'
14     end
15     else if(@Count1>0)
16     begin
17         select 'delete操作'
18     end
19     else if(@Count2>0)
20     begin
21         select 'insert操作'
22     end
23 go
24 
25 update Student set S_StuNo='006' where S_Id='20'
26 
27 insert into Student([S_StuNo],[S_Name],[S_Sex],[S_Height],[S_BirthDate])
28 values('017','清红','女','180','2017-01-01')
29 
30 delete from Student where C_S_Id='5'
31 
32 select * from Student
 1 alter trigger trigger_Stu_InsteadOf        -- 修改触发器
 2 on Student        -- 指定创建触发器的表
 3 instead of update,insert,delete         -- instead of 触发器
 4 as
 5     declare @Count1 int
 6     declare @Count2 int
 7 
 8     select @Count1=COUNT(1) from deleted        
 9     select @Count2=COUNT(1) from inserted        
10 
11     if(@Count1>0 and @Count2>0)
12     begin
13         select 'update操作'
14     end
15     else if(@Count1>0)
16     begin
17         select 'delete操作'
18     end
19     else if(@Count2>0)
20     begin
21         select 'insert操作'
22     end
23 go
24 
25 update Student set S_StuNo='006' where S_Id='20'
26 
27 insert into Student([S_StuNo],[S_Name],[S_Sex],[S_Height],[S_BirthDate])
28 values('017','清红','女','180','2017-01-01')
29 
30 delete from Student where C_S_Id='5'
31 
32 select * from Student

AS

澳门新萄京官方网站 35

澳门新萄京官方网站 36

  sql_statement […n ]

启用/禁用触发器:

启用/禁用触发器:

  }   

1 --禁用触发器
2 disable trigger trigger_Stu_InsteadOf on Student;    -- trigger_Stu_InsteadOf 触发器名称
3 --启用触发器
4 enable trigger trigger_Stu_InsteadOf on Student;    -- trigger_Stu_InsteadOf 触发器名称
1 --禁用触发器
2 disable trigger trigger_Stu_InsteadOf on Student;    -- trigger_Stu_InsteadOf 触发器名称
3 --启用触发器
4 enable trigger trigger_Stu_InsteadOf on Student;    -- trigger_Stu_InsteadOf 触发器名称

 

查询已存在的触发器:

查询已存在的触发器:

DDL触发器使用

1 -- 查询已存在的触发器
2 select * from sys.triggers;
3 select * from sys.objects where type = 'TR';
4 select * from sysobjects where xtype='TR'

 1 -- sys.trigger_events 触发器事件对象视图
 2 select * from sys.trigger_events 
 3 
 4 -- 查看触发器触发事件对象    
 5 select a.type_desc,b.* from sys.trigger_events a 
 6 inner join sys.triggers b on a.object_id = b.object_id
 7 where b.name = 'trigger_Stu_Insert';
 8 
 9 -- 查询创建触发器的 T-SQL 文本
10 exec sp_helptext 'trigger_Stu_Insert'
1 -- 查询已存在的触发器
2 select * from sys.triggers;
3 select * from sys.objects where type = 'TR';
4 select * from sysobjects where xtype='TR'

 1 -- sys.trigger_events 触发器事件对象视图
 2 select * from sys.trigger_events 
 3 
 4 -- 查看触发器触发事件对象    
 5 select a.type_desc,b.* from sys.trigger_events a 
 6 inner join sys.triggers b on a.object_id = b.object_id
 7 where b.name = 'trigger_Stu_Insert';
 8 
 9 -- 查询创建触发器的 T-SQL 文本
10 exec sp_helptext 'trigger_Stu_Insert'

 

 

 

例如:

参考:

参考:

 1 /*DDL触发器*/
 2 
 3 /*在test数据库上创建一个DDL触发器safe,
 4 用来防止数据库中的任一表被修改或删除。*/
 5 
 6 create trigger safetest
 7 on database                         --数据库DDL触发器
 8 after drop_table,alter_table
 9 as
10 begin
11     raiserror('不能修改表结构',16,2)
12     rollback
13 end
14 go
15  
16 --执行以下程序,观察结果
17 alter table student add nation char(10)
18 
19 disable trigger safetest on database
20 drop trigger safetest on database
21 ---------------------------------------------------------
22 
23 
24 /*在服务器上创建一个DDL触发器tablecreat,
25 用来防止在服务器上创建数据库*/
26 
27 create trigger trig_last
28 on all server
29 after create_database
30 as
31 begin
32     raiserror('不能创建新的数据库',16,2)
33     rollback
34 end
35 go
36 
37 --执行以下程序,观察结果
38 create database test_trig
39 
40 disable trigger trig_last on all server
41 drop trigger trig_last on all server

 

删除触发器

 

使用SQL Server Management Studio删除触发器

使用DROP TRIGGER语句来删除触发器。其语法格式如下:

DROP TRIGGER { trigger } [ , …n ]

 

触发器禁用和启用

 

例如:

 1 /*触发器禁用和启用*/
 2 
 3 /*禁用sc表上的触发器trig_g。*/
 4 alter table sc disable trigger trig_g
 5 disable trigger trig_g on sc
 6 go
 7 
 8 /*启用sc表上的触发器trig_g。*/
 9 alter table sc enable trigger trig_g
10 enable trigger trig_g on sc
11 go
12 
13 --执行以下程序,观察结果
14 insert into sc values(1,100,1,-50)
15 select * from sc

 

 

触发器具体应用

 

例如:

 1 /*具体应用*/
 2 
 3 /*创建触发器trig3,
 4 当删除student表中的学生记录时,
 5 应该同时删除sc表中对应的记录*/
 6 
 7 create trigger trig_5
 8 on student
 9 for delete
10 as 
11  delete sc
12  where sc.sno in(select sno from deleted)
13 go
14 
15 select * from student
16 select * from sc
17 go
18 
19 /*exec sp_help score   --查看其中外键
20 
21 alter table score         --删除外键
22 drop CONSTRAINT FK_score_course
23 
24 alter table score
25 drop CONSTRAINT FK_score_student*/
26 
27 
28 delete student where sno=3
29 go
30 
31 select * from student
32 select * from sc 
33 -------------------------------------------------------------
34 
35 
36 create trigger trig_g 
37 on sc
38 after insert,update
39 as
40 begin
41     declare @g int
42     select @g=grade from inserted
43     if @g<0
44     begin
45          select '成绩必须>=0'
46          rollback
47     end
48 end
49 go
50 
51 --执行以下程序,观察结果
52 insert into sc values(1,10,1,50)
53 select * from sc
54 
55 
56 /*例: 建立一个修改触发器trigno,
57 该触发器防止用户修改表student的学号*/
58 
59 create trigger trigno
60 on student
61 after update
62 as
63 if update(sno)
64     begin
65         raiserror('不能修改学号',16,2)
66         rollback
67     end
68 go
69 
70 --执行以下程序,观察结果
71 update student set sno='2' where sno='1'
72 select * from student
73 ------------------------------------------------------
74 
75 
76 /*INSTEAD OF触发器*/
77 
78 /*例:在student表上创建一个INSTEAD OF触发器trig_6,
79 当用户插入数据时注意观察触发器的执行。*/
80 
81 create trigger trig_6
82 on student
83 instead of insert
84 as
85     select * from student
86 go
87 
88 --执行以下程序,观察结果
89 insert into student(sno,sname) values('300','白扯')

 

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:Oracle数据库之PL,创建触发器

关键词: