金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > DML触发器学习,14第十四章触发器

DML触发器学习,14第十四章触发器

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-09 02:15

循环/递归触发器的前提就是嵌套触发器,只有允许嵌套了才可以递归(递归也就是嵌套并触发自己),递归有直接和间接两种情况:

直接递归, 如 应用程序更新 T3 表,从而触发了 触发器 Trig3 , Trig3 再次更新表T3,从而再次出发了触发器Trig3

USE tempdb
--================================
--创建测试表
DROP TABLE TB1
GO
CREATE TABLE TB1
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    C2 INT NOT NULL,
    C3 VARCHAR(MAX)
)
GO
--================================
--创建Delete触发器
CREATE TRIGGER TR_TB1_DELETE
ON dbo.TB1
AFTER DELETE
AS
BEGIN
RETURN 
END
GO
--================================
--插入5w数据
INSERT INTO TB1(C2)
SELECT TOP(5000) 1 AS C2 FROM sys.all_columns T
GO 10

--================================
--查看表TB1使用的页
DBCC TRACEON(3604)
GO
DBCC IND('tempdb','TB1',1)
GO
--================================
--删除一半的数据
DELETE FROM  dbo.TB1
WHERE ID%2=0

GO
--================================
--查看表TB1使用的页
DBCC TRACEON(3604)
GO
DBCC IND('tempdb','TB1',1)
GO

创建触发器的通用语法如下所示:

  1. AFTER触发器,默认Nest Triggers值为1,即允许触发器嵌套,上限32层,间接递归也是可以的,直接递归需要开启数据库选项RECURSIVE_TRIGGERS;

  2. INSTEAD OF触发器,不受Nest Triggers选项影响,均可以嵌套,上限32层,间接递归也是可以的,直接递归无论是否开启数据库选项RECUSIVE_TRIGGERS,都无效;把上面两个脚本示例中的AFTER改为INSTEAD OF即可演示。

金沙棋牌app手机下载 1

需要注意的是:

       4> 建立替代触发器

  • 直接递归:就是A表的DML触发器再回来对A表进行DML操作,如上例;
  • 间接递归:就是A表DML触发器去操作B表,然后B表上触发器回来操作A表,如下例;

    --create table, sql server 2016 & higher drop table if exists A drop table if exists B GO create table A(id int) create table B(id int) GO

    --create DML trigger drop trigger if exists tri_01 drop trigger if exists tri_02 GO create TRIGGER tri_01 ON A AFTER INSERT, UPDATE, DELETE as begin

    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert B values(0)
    

    end GO

    create TRIGGER tri_02 ON B AFTER INSERT, UPDATE, DELETE as begin

    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert A values(0)
    

    end GO

    --test with nested triggers server option ON exec sp_configure 'nested triggers',1 RECONFIGURE

    --test with RECURSIVE_TRIGGERS off ALTER DATABASE dba set RECURSIVE_TRIGGERS off select is_recursive_triggers_on, * from sys.databases GO

    truncate table A truncate table B insert A values(1) select from A --16 rows select from B --16 rows

    --test with RECURSIVE_TRIGGERS on ALTER DATABASE dba set RECURSIVE金沙棋牌app手机下载,_TRIGGERS on select is_recursive_triggers_on, * from sys.databases GO

    truncate table A truncate table B insert A values(1) select from A --16 rows select from B --16 rows

    --test with nested triggers server option OFF exec sp_configure 'nested triggers',0 RECONFIGURE

    --test with RECURSIVE_TRIGGERS off ALTER DATABASE dba set RECURSIVE_TRIGGERS off select is_recursive_triggers_on, * from sys.databases GO

    truncate table A truncate table B insert A values(1) select from A --1 select from B --0

    --test with RECURSIVE_TRIGGERS on ALTER DATABASE dba set RECURSIVE_TRIGGERS on select is_recursive_triggers_on, * from sys.databases GO

    truncate table A truncate table B insert A values(1) select from A --1 select from B --0

    --删表会级联删除触发器,就像索引 drop table A, B

  • 可以看出数据库选项RECURSIVE_TRIGGERS,仅对直接递归有效,对间接递归无效;可以通过Nest Triggers的开关来控制是否允许嵌套,从而控制是否允许间接递归;

  • 不论直接递归,还是间接递归,递归次数都有32次嵌套的上限;

         --> INSTEAD OF  在 时间之前触发,相当于 bef

在SQL Server多中功能中使用到row version来保留多个版本的数据,这些功能有:

数据库触发器是存储于数据库的命名PL/SQL语句块,当触发事件发生时它们会隐含执行。

ORACLE DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

CREATE / ALTER /DROP DATABASE

从功能来看,INSTEAD OF触发器用来替换实际的数据修改操作,而AFTER触发器用来在实际操作完成后进行后续操作。例如对于DELETE操作,如果我们期望只修改数据状态来标示数据已被删除而不是将数据从表中删除,那么我们可以使用INSTEAD OF触发器来实现;如果我们期望在删除数据后在其他表记录删除操作的发生时间,那么我们可以使用AFTER触发器来实现。

       trigger语句块中不能含有事务处理语句,如commit和rollback。倘若需要需要事务处理语句,则必须用到自治事务。

 

注意要删除一个 DDL 触发器,需要制定触发器的作用域范围,否则将默认为要删除DML 触发器。

触发器按类型分为三类:

 AFTER LOGON ON DATABASE 也可替换为 BEFORE SHUTDOWN ON DATABASE,BEFORE LOGON ON SCHEMA,ALTER LOGON ON SCHEMA等等。

  • 大批量导入操作,如:BULK INSERT, bcp/INSERT... SELECT * FROM OPENROWSET,都有FIRE_TRIGGERS/IGNORE_TRIGGERS选项,可以设置是否触发触发器;
  • 导入导出向导/SSIS,如果目标是表,也有FIRE_TRIGGERS的设置选项;
  • 另外truncate操作也不会触发;

递归分为 

测试代码:

       上述范例均属于行触发器。

IF OBJECT_ID('login_history','U') is not null
    DROP TABLE login_history
GO

CREATE TABLE login_history
(
FACT_ID         bigint IDENTITY(1,1) primary key,
LOGIN_NAME      nvarchar(1024),
LOGIN_TIME      datetime
)
GO

IF EXISTS(select 1 from sys.server_triggers where name = 'login_history_trigger')
    DROP TRIGGER login_history_trigger ON ALL SERVER
GO

CREATE TRIGGER login_history_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
    --IF SUSER_NAME() NOT LIKE 'NT AUTHORITY%' AND 
    --   SUSER_NAME() NOT LIKE 'NT SERVICE%'
    IF ORIGINAL_LOGIN() NOT LIKE 'NT AUTHORITY%' AND
       ORIGINAL_LOGIN() NOT LIKE 'NT SERVICE%'
    BEGIN
        INSERT INTO DBA..login_history
        VALUES(ORIGINAL_LOGIN(),GETDATE());
    END;
END;
GO

--view login history after logon
SELECT * FROM login_history

instead of 给 insert 跟 update 都必须为不能为空的列指定值,但是在触发器中需要忽略掉这些值。

 

触发器按照不同的分法,有不同的分类,主要有以下两种:

 

递归 P349  有个经典例子 由于有 update() 函数检测,作为递归终止条件。

--================================
--在TB1和TB2上创建触发器,当TB1上TR_TB1_INSERT1被
--触发时,TR_TB1_INSERT1中的语句执行导致TB2上
--TR_TB2_INSERT1被触发,即属于Nested触发器
CREATE TRIGGER TR_TB1_INSERT1
ON dbo.TB1
AFTER INSERT
AS
BEGIN
INSERT INTO TB2(C1)
SELECT C1 FROM inserted

END
GO

CREATE TRIGGER TR_TB2_INSERT1
ON dbo.TB2
AFTER INSERT
AS
BEGIN

SELECT 1

END

      2> 查询视图

select * from sys.trigger_event_types
where type_name not like '%CREATE%'
  and type_name not like '%ALTER%'
  and type_name not like '%DROP%'

可以通过nested triggers 服务器配置选项来空值是否可以嵌套AFTER 触发器。 INSTEAD OF 触发器嵌套不受此选项影响。参考下面的语句:

 

       EMPNO ENAME DEPTNO DNAME
       ---------- ---------- ---------- --------------
       7782 CLARK 10 ACCOUNTING
       7839 KING 10 ACCOUNTING
       ...

2. DDL触发器中捕获的信息都由EVENTDATA()函数返回,返回类型为XML格式,需要用XQuery来读取;

嵌套和递归触发器

 

按触发的类型可分为:行触发器,语句触发器和INSTEAD OF触发器

Why we can‘t use commit in trigger, can anyone give proper explanation

金沙棋牌app手机下载 2

--==============================================================

1> 处理数据库表的DML语句(如INSERT,UPDATE或者DELETE)。在触发事件发生之前或者之后,触发器会执行。

1. 语句级触发器/行级触发器

自动事务处理模式下,还是在隐式或显示事务处理模式下,只要在 触发器中发出 BEGIN TRANSACTION 语句,实际上就开始了一个嵌套事务,当触发器中使用 ROLLBACK TRANSACTION 语句回滚嵌套事务时,触发器本身发出的所有的 BEGIN TRANSACTION 语句回滚嵌套事务时,触发器本身发出的额所有 BEGIN TRANSACTION 语句豆浆被忽略, ROLLBACK 将回滚到最外部的 BEGIN TRANSACTION 。而在 这 最外部的 之前的 事务都已经提交的就不会收到影响,

因此在使用触发器时,应考虑到可能会为表增加额外14bytes的行版本存储指针

可见,对某列也可使用触发器

在SQL Server和Oracle中都是这样,触发器作为整个事务的一部分存在,但是并不控制整个事务的提交/回滚,为保证数据一致性,事务逻辑由触发器外层的语句来控制。

FOR CREATE_TABLE

在TB1和TB2上创建触发器,当TB1上TR_TB1_INSERT1被触发时,TR_TB1_INSERT1中的语句执行导致TB2上TR_TB2_INSERT1被触发,而TB2上TR_TB2_INSERT1的触发器执行时又导致TB1上TR_TB1_INSERT1被触发,从而引发循环。

        insert into v_test values(1234,'VICTOR',40,'IT')
        *
        ERROR at line 1:
        ORA-01776: cannot modify more than one base table through a join view

Transact-SQL statements

IF (exists(select * from dbo.Test11 where name = (select name from inserted)))

递归(Recursive)触发器可分为直接递归(Directed Recursive)触发器和间接递归(Indirect Recursive)触发器

触发事件可以是如下任何一种:

 

ENABLE TRIGGER Safety 

在TB1创建触发器,当TB1上TR_TB1_INSERT1被触发时,TR_TB1_INSERT1中的语句执行导致TB1上TR_TB1_INSERT1再次被触发

create or replace trigger emp_update
before update or delete or insert on emp
for each row
begin
   if updating or deleting or inserting then
      raise_application_error(-20001,'The table emp can not be modified');
   end if;
end;

在ORACLE中,实例级触发器可支持更多事件 (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)。

DROP TRIGGER mYoTHERtRIGGER

PS:上面说的Fire only once只是针对执行的SQL语句,并不包含该触发器内部的SQL语句

       到目前为止,我们所涉及的触发器都是基于表的,其实,Oracle还提供了另一种触发器,这种触发器是在数据库视图上创建的,即替代触发器。替代触发器会代替基于视图的DML操作(INSERT、UPDATE、DELETE),而直接作用于底层的数据库表。我们来看看下面的实验:

在SQL Server中,顾名思义,LOGON触发器,只支持LOGON事件;

修改,删除和禁用触发器

从执行来看,INSTEAD OF触发器和AFTER触发器的所处的执行时期不同,SQL Server中的触发顺序为:

       当该触发器所执行的操作不依赖于单独记录中的数据时,就应该使用语句触发器。例如,如果希望限制只能在上班时间访问emp表,就应该使用语句触发器。

  • INSTEAD OF触发器,可以嵌套,不受这个参数开关与否影响;
  • AFTER触发器,即使打开该选项,也不会自己嵌套自己(即递归),除非打开了RECURSIVE_TRIGGERS选项,也就是循环/递归触发器;

    --create table, sql server 2016 & higher drop table if exists A GO create table A(id int) GO

    --create DML trigger drop trigger if exists tri_01 GO create TRIGGER tri_01 ON A AFTER INSERT, UPDATE, DELETE as begin

    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert A values(0)
    

    end GO

    --check nested triggers server option exec sp_configure 'nested triggers' --name minimum maximum config_value run_value --nested triggers 0 1 1 1

    --test with RECURSIVE_TRIGGERS off ALTER DATABASE dba set RECURSIVE_TRIGGERS off select is_recursive_triggers_on, from sys.databases GO insert A values(1) select from A --id --1 --0

    --test with RECURSIVE_TRIGGERS on ALTER DATABASE dba set RECURSIVE_TRIGGERS on select is_recursive_triggers_on, * from sys.databases GO

    truncate table A insert A values(1) select * from A --32 rows

    --如果没有加@@NESTLEVEL判断并退出,会出现32层限制的报错,并且表里不会插入任何数据 /* Msg 217, Level 16, State 1, Procedure tri_01, Line 10 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    select from A --0 rows/

    --删表会级联删除触发器,就像索引 drop table A

或多个从左至右排序的字节。 P346 是一个很经典的应用。

新一年,换换口味,来点萌妹子吧!

CREATE [OR REPLACE] TRIGGER Trigger_name
{BEFORE |AFTER} Triggering_event ON table_name
[FOR EACH ROW]
[FOLLOWS another_trigger]
[ENABLE/DISABLE]
[WHEN condition]
DECLARE
     declaration statements
BEGIN
     executable statements
EXCEPTION 
     exception-handling statements
END;

金沙棋牌app手机下载 3

DML --> AFTER / FOR   UPDATE , INSERT , DELETE  -- 用来级联删除

3. 由于嵌套触发器会消耗大量资源(需要保留每层触发器的上下文以便回滚),因此默认限制最多嵌套32层。

      

触发器可以理解为由特定事件触发的存储过程, 和存储过程、函数一样,触发器也支持CLR,目前SQL Server共支持以下几种触发器:

DROP TRIGGER MyTrigger

间接递归(Indirect Recursive)触发器:

五、 语句触发器

RECONFIGURE; --使用新环境值

 

该触发器的代码部分有三个布尔函数-updating,deleting,inserting,如果对这个表执行update操作,则函数updating的值为TRUE;如果对这个表执行delete操作,则函数deleting的值为TRUE。insert操作亦然。

 

ALTER TABLE dbo.PriTable

直接递归(Directed Recursive)触发器:

其中,trigger_name是触发器的名称。BEFORE或者AFTER指明触发器何时执行,即在触发事件发生之前,还是之后,trigger_event是针对数据库表的DML语句。table_name是与该触发器相关的数据库表的名称。子句FOR EACH ROW指行触发器。FOLLOWS选项,指定触发器被触发的顺序。这个选项适用于在相同表上所定义的,并且会在相同时间点执行的触发器。ENABLE和DISABLE子句指定触发器是在启用,还是禁用状态下被创建的。默认情况下是启用的。

代码示例1: 记录所有login登录历史 (其实也可以通过修改login auditing选项,来记录成功和失败的登录在errorlog里)

    DISABLE TRIGGER PriTrigger;

--额外补充

SQL> create table log_table(sid number,serial# number,username varchar2(20),action varchar2(8),log_time varchar2(19));   -->> 首先创建表用于记录用户的登陆信息

CREATE OR REPLACE TRIGGER logon_db
AFTER LOGON ON DATABASE
DECLARE
  v_sid v$mystat.sid%type;
  v_serial# v$session.serial#%type;
  v_username v$session.username%type;
BEGIN
  SELECT sid INTO v_sid FROM v$mystat WHERE rownum=1;
  SELECT serial#,username
  INTO   v_serial#,v_username
  FROM v$session WHERE sid=v_sid;
  INSERT INTO log_table
  VALUES (v_sid,v_serial#,v_username,'logon',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
END logon_db;

SQL Server 2005开始支持DDL触发器,它不只限于对CREATE/ALTER/DROP操作有效,支持的DDL事件还有比如:权限的GRANT/DENY/REVOEK, 对象的RENAME, 更新统计信息等等,可通过DMV查看更多支持的事件类型如下:

sp_settriggerorder 

指定最先执行的AFTER触发器:

 

在SQL Server中,从定义来说只有语句级触发器,但如果有行级的逻辑要处理,有两个仅在触发器内有效的表 (inserted, deleted), 存放着受影响的行,可以从这两个表里取出特定的行并自行定义脚本处理;

注意:  在 触发器中 书写 COMMIT TRANSACTION 的 语句,如果之前有 BEGIN TRANSACTION 语句,会被认为是

因为INSTEAD OF 触发器改写了实际要发生的修改操作,因此每个表上每种修改类型(DELETE/INSERT/UPDATE)只能有一个INSTEAD OF 触发器;而AFTER 触发器没有类似限制,可以创建多个AFTER触发器。

范例五:

 

FIRST 和 LAST 触发器之间的执行并没有先后顺序:

因此如果期望修改操作顺利执行而不触发约束导致回滚的话,可以使用INSTEAD OF触发器来将实现(在INSTEAD OF 触发器中修改使数据满足约束条件)。

八、 复合触发器

代码示例1:记录所有table上的某些DDL操作

DDL 触发器是为相应一个或多个特定的数据定义语言语句的激发。并且 DDL 触发器只能在 SQL 语句完成之后才运行,无法作为 INSTEAD OF 触发器。

行版本(Row version)

二、 AFTER触发器

而这个 回滚操作也会终止 批处理中 对 该语句后面语句的执行。

  1. DML 触发器,在数据变更时触发;

  2. DDL 触发器,在修改数据库级别或实例级别对象时触发;

  3. Login 触发器,在用户登录时触发;

 

CREATE TRIGGER (Transact-SQL)

-- instead of update 同理

 

       SQL> create or replace view v_test as select e.empno,e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

注意:

ROLLBACK;

 

CREATE OR REPLACE TRIGGER TRIGGER_RESTRICT_LOGON
AFTER LOGON ON DATABASE
DECLARE
   RESTRICTED_USER VARCHAR2(32) := 'SCOTT';
   ALLOWED_IP      VARCHAR2(16) := '192.168.1.1';
   LOGON_USER      VARCHAR2(32);
   CLIENT_IP       VARCHAR2(16);
BEGIN
   LOGON_USER := SYS_CONTEXT('USERENV','SESSION_USER');         -->> SYS_CONTEXT是一个蛮有用的函数
   CLIENT_IP  := NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), 'NULL');
   IF LOGON_USER = RESTRICTED_USER AND CLIENT_IP <> ALLOWED_IP THEN
        RAISE_APPLICATION_ERROR(-20001, RESTRICTED_USER || ' is not allowed to connect from ' || CLIENT_IP);
   END IF;
END;

服务器本地,在cmd中通过DAC登录

ALTER DATABASE AdventureWorks 

 

       行触发器指的是触发器被触发的次数等同于触发语句所影响的数据行数量。当语句FOR EACH ROW出现在CREATE TRIGGER子句中,该触发器就是行触发器。

2. BEFORE/AFTER/INSTEAD OF

    DELETE FROM DetailTable

  1. MARS

  2. Triggers

  3. Online indexing

  4. Optimistic Transaction Isolation Levels

       SQL> select * from v_test;

 

sp_configure 'nested triggers',1 -- 设置 为 1 允许 after 触发器嵌套

--==================================================

         测试OK!

代码示例2:禁止特定角色的用户对特定的表做DROP操作

间接递归。 即中间经过另外的表中转还是触发了第一张表的触发器:

 

       对于语句触发器而言,每执行一次触发语句,该触发器就会执行一次,也就是说,不管触发语句影响多少数据行,该触发器只会执行一次。

这时,只能通过DAC登录SQL Server去禁用LOGON触发器/修改逻辑以允许登录,DAC登录方式有远程和本地两种,远程登录需要通过sp_configure 开启remote admin connections ,如果没有事先开启,那就只能选择本地登录方式:

AFTER INSERT

 

       下面试举几例

对于UPDATE,DELETE操作而言,均会触发触发器;而对于INSERT或者说IMPORT的情况,是可以控制不去触发的。

GO

如下面例子中,表中数据被删除一半,但由于数据只是表示为gost,尚未真正移除,而由于触发器存在,每行额外增加14byte的数据,从而导致页拆分,最终使得删除操作完成后表反而增大。

CREATE OR REPLACE TRIGGER emp_format
AFTER LOGON ON SCHEMA
DECLARE
   sqlstr VARCHAR2(60) :='alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';   注意:连续两个单引号表示转义
BEGIN
   execute immediate sqlstr;
END;
select * from sys.server_triggers where name = 'login_history_trigger'
select * from sys.server_trigger_events
select OBJECT_ID('login_history_trigger') --无法获取

DROP TRIGGER MyTrigger

1. 如果使用Merge并且设置了INSERT/DELETE/UPDATE方法,那么即使没有满足条件的数据进行INSERT/DELETE/UPDATE,也会触发INSERT/DELETE/UPDATE相关的触发器。

       

对于AFTER触发器有个两个开关分别控制嵌套触发和递归触发:

FOR DELETE

嵌套(Nested)触发器:在TB1和TB2上创建触发器,当TB1上TR_TB1_INSERT1被触发时,TR_TB1_INSERT1中的语句执行导致TB2上TR_TB2_INSERT1被触发

三、 自治事务

SQL Server 2005在SP2中悄悄引入了LOGON触发器,作为一个实例级的对象,它的系统视图,定义语句和DDL/DML触发器都是分开的。

 

--================================
--在TB1和TB2上创建触发器,当TB1上TR_TB1_INSERT1被
--触发时,TR_TB1_INSERT1中的语句执行导致TB2上
--TR_TB2_INSERT1被触发,而TB2上TR_TB2_INSERT1的
--触发器执行时又导致TB1上TR_TB1_INSERT1被触发,从而
--引发循环,即间接递归(Indirect Recursive)触发器
CREATE TRIGGER TR_TB1_INSERT1
ON dbo.TB1
AFTER INSERT
AS
BEGIN
    IF(@@NESTLEVEL<10)
    BEGIN
        INSERT INTO TB2(C1)
        SELECT C1 FROM inserted
    END

END
GO

CREATE TRIGGER TR_TB2_INSERT1
ON dbo.TB2
AFTER INSERT
AS
BEGIN
    IF(@@NESTLEVEL<10)
    BEGIN
        INSERT INTO TB1(C1)
        SELECT C1 FROM inserted
    END
END

       3> 插入数据

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[INS_TEST]'))

SQL server中有两种特殊的触发器:嵌套(Nested)触发器和递归(Recursive)触发器,由Demo来解释下:

禁用或启用触发器,可用以下命令:ALTER TRIGGER trigger_name DISABLE/ENABLE;

金沙棋牌app手机下载 4

AS

2. (Recursive)触发器在数据库级别配置,默认为关闭,即不允许直接递归(Directed Recursive)触发器,但不影响间接递归(Indirect Recursive)触发器,如果需要禁用递归(Indirect Recursive)触发器,需要同时禁用嵌套(Nested)触发器和(Recursive)触发器

3> 系统事件,如数据库启动或者关闭

代码示例2: 限制特定用户在特定时间范围登录、限制连接数

LOAD DATABASE / LOAD LOG / RECONFIGURE

  1. 嵌套(Nested)触发器在sys.configurations中配置,默认开启

 

Logon failed for login 'TestUser' due to trigger execution.

ON DATABASE 

最常见的是DML触发器,DML触发器又可以分为两类: INSTEAD OF触发器和AFTER触发器(部分书上有提到FOR触发器,其实就是AFTER 触发器,只是写法不同而已)。

按触发的时间可分为:BEFORE触发器和AFTER触发器

在SQL Server中,从定义来说只有AFTER/INSTEAD OF触发器,在表上支持AFTER触发器,在表/视图上支持INSTEAD OF触发器,对于BEFORE触发器的需求可以尝试通过INSEAD OF触发器来实现;

禁用启用也同样要指定作用域范围:

说完触发顺序,再来说道说道触发次数,装逼的说法为:DML trrigers have statement scope and only fire just once regardless of how many rows affected.通俗说法就是对于一条语句,不管语句修改了多少行(0行或者1000行),对应该操作类型的触发器都会被触发并且只触发一次。

更多相关内容,可参考官方文档:http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS020

 

ON PriTable

--指定针对INSERT操作最先触发的AFTER触发器
EXEC sys.sp_settriggerorder
@triggername='tr_TB1_INSERT',
@order='First',
@stmttype='INSERT'

        SQL> insert into v_test values(1234,'VICTOR',50,'IT');

注意:如果LOGON触发器把所有人都锁在外面了怎么办?

在创建 DML 触发器时,不能使用下列语句:

问题来了,在存在多个AFTER触发器情况下,AFTER触发器按什么顺序来执行呢?SQL Server允许针对每种修改类型(DELETE/INSERT/UPDATE)指定一个最先触发和最后触发的AFTER触发器,但不能控制其余的触发器触发顺序。

       1> 限制用户从指定IP登陆

 

因此,若要在 触发器中进行部分回滚,应当使用 SAVE TRANSACTION 语句设置一个事务保存点,这样就不会回滚到 外部的 事务中去了。

PS: 如果表中不存在LOB或者VARCHAR(MAX)之类的大字段,不存在ROW_OVERFLOW数据页,则SQL Server不会为每行增加14byte的行版本存储指针

2> 特定用户在特定模式下,或者任何用户执行的DDL语句(如CREATE或者ALTER)。这种触发器经常被用于审计目的。它们可以记录各种模式修改,何时执行,以及哪个用户执行的。

二. DDL触发器

ON dbo.test11 

金沙棋牌app手机下载 5

范例一:

exec sp_configure 'nested triggers'

 

--================================
--在TB1创建触发器,当TB1上TR_TB1_INSERT1被触发时,
--TR_TB1_INSERT1中的语句执行导致TB1上TR_TB1_INSERT1
--再次被触发,即属于直接递归(Directed Recursive)触发器。
ALTER TRIGGER TR_TB1_INSERT1
ON dbo.TB1
AFTER INSERT
AS
BEGIN
--限制递归层数为10层
    IF(@@NESTLEVEL<10)
    BEGIN
        INSERT INTO TB1(C1)
        SELECT C1+1 FROM inserted
    END
END
GO
CREATE OR REPLACE TRIGGER t_v_test
INSTEAD OF INSERT ON v_test
DECLARE
   duplicate_info EXCEPTION;
   PRAGMA EXCEPTION_INIT(duplicate_info,-00001);
BEGIN
   INSERT INTO  dept(deptno,dname)
       VALUES(:new.deptno,:new.dname);
   INSERT INTO  emp(empno,ename,deptno)
       VALUES(:new.deptno,:new.ename,:new.deptno);
EXCEPTION
   WHEN duplicate_info THEN 
       RAISE_APPLICATION_ERROR(-20001,'Duplicate empno or deptno');
END;

(1) 不能触发的情况

VALUES(4,'Frank',88.88)

  1. 触发INSTEAD OF触发器

  2. 触发DEFAULT 约束

  3. 触发主键/唯一/CHECK约束

  4. 触发外键约束

  5. 触发AFTER 触发器

4> 用户事件,如登陆和注销。即可以定义一个触发器,在用户登陆数据库时记录用户名和登陆事件。

 

Disable trigger dbo.PriTrigger ON dbo.PriTable; -- DDL 触发器的话不能包含架构名

dept和emp表存在外键约束,即dept表的主键deptno是emp表的外键,当我们删除dept表的行记录时,如果待删除的deptno在emp表中存在对应的记录,则会报ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found。构造触发器如上,当我们删除dept的行记录时,会自动删除deptno在emp表中对应的行记录。

 

指定 FIRST 触发器 和 LAST 触发器

        :NEW.student.id := v_student_id

三. LOGON 触发器

  1. 在INSTEAD OF 触发器中使用 TEXT, NTEXT 和 IMAGE 数据
create or replace trigger emp_record
before insert or update or delete on emp
declare
   v_day varchar2(10);
begin
   v_day := rtrim(to_char(sysdate,'DAY'));
   if v_day in ('SATURDAY','SUNDAY') then 
      raise_application_error(-20000,'The table can not be modified during off hours');
   end if;
end;

总结下来:

@triggername ='ud_trig/ins_trig/del_trig', @order = 'first/last', @stmttyp = 'update / insert / delete';

范例四:

 

insert into dbo.Students

create or replace trigger student_i
before insert on student
for each row
BEGIN
  :NEW.student.id := student_seq.nextval;
  :NEW.created_by := USER;
  :NEW.created_date := SYSDATE;
END;

Create Nested Triggers

--禁用 触发器  方法二

create or replace trigger emp_update
before update of sal on emp
for each row
begin
   if updating then
       raise_application_error(-20001,'Salary can not be modofied');
   end if;
end;
IF exists(select * from sys.triggers where name = 'NO_DROP_TABLE' and parent_class_desc = 'DATABASE')
    DROP TRIGGER [NO_DROP_TABLE] ON DATABASE;
GO

CREATE TRIGGER NO_DROP_TABLE
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    DECLARE @x                XML,
            @user_name        varchar(100),
            @db_name          varchar(100),  
            @schema_name      varchar(100),
            @object_name      varchar(200)

    --select eventdata()
    SET @x = EVENTDATA();
    SET @user_name = @x.value('(/EVENT_INSTANCE/UserName)[1]','varchar(100)');
    SET @db_name = @x.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(100)');
    SET @schema_name = @x.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(100)');
    SET @object_name = @x.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)');

    --PRINT 'Current User: '     + @user_name
    --PRINT 'Current Database: ' + @db_name
    --PRINT 'Schema Name: '      + @schema_name
    --PRINT 'Table Name: '       + @object_name

    IF is_rolemember('disallow_modify_tables',@user_name) = 1
       AND @db_name = 'YOUR_DB_NAME'
       AND @schema_name = 'YOUR_SCHEMA_NAME'
       AND @object_name like 'YOUR_TABLE_NAME%'
    BEGIN 
        PRINT 'Dropping tables is not allowed'
        ROLLBACK
    END
END
GO

AS

范例三:

 

DELETE FROM dbo.Students WHERE StudentName = 'Frank'

范例二:

  1. TRUNCATE不在DDL触发器的事件类型中,SQL Server中将Truncate 归为DML操作语句,虽然它也并不触发DML触发器,就像开启开关的大批量导入操作 (Bulk Import Operations) 一样;

AS

        通过PL/SQL表达式访问序列是Oracle 11g的新特性。在Oracle 11g之前,只能通过以下方式获取:

3. 触发条件

EVENTDATA().VALUE('(/EVENT_INSTANCE/TSQLCommand/cOMMANDtEXT)[1]','nvarchar(max)');

       5> 重新插入数据 

--记录所有create table操作
if OBJECT_ID('ddl_log','U') is not null
    drop table ddl_log
GO

create table ddl_log
(
LogID        int identity(1,1),
EventType    varchar(50), 
ObjectName   varchar(256),
ObjectType   varchar(25),
TSQLCommand  varchar(max),
LoginName    varchar(256)
)
GO

if exists(select * from sys.triggers where name = 'TABLE_DDL_LOG' and parent_class_desc = 'DATABASE')
    drop trigger TABLE_DDL_LOG on database;
GO

create trigger TABLE_DDL_LOG
on database
for create_table
as
begin
    set nocount on 

    declare @data xml
    set @data = EVENTDATA()

    insert into ddl_log
    values
    (@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
    @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
    @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
    )
end
GO

drop table if exists test_dll_trigger;
create table test_dll_trigger (id int)
select * from ddl_log

INSTEAD OF INSERT

注意:触发器包含伪记录:NEW,使得你可以访问正被插入student表的数据行。为访问伪记录:NEW的单独成员,需要使用点符号:

exec sp_configure 'nested triggers',0
RECONFIGURE

注意: 只有在设置 RECURSIVE_TRIGGERS 数据库选项为 ON 的情况下,才允许以递归方式调用AFTER 触发器:

禁用某个表上的所有触发器,可用如下命令:ALTER TABLE table_name DISABLE ALL TRIGGERS.

--create table, sql server 2016 & higher
drop table if exists A
GO
create table A(id int)
GO

--create DML trigger
drop trigger if exists tri_01
GO
create TRIGGER tri_01
ON A
AFTER INSERT, UPDATE, DELETE 
as
begin
    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert A values(0)
    commit
end
GO

begin tran
insert A values(1)
/*
Msg 3609, Level 16, State 1, Procedure tri_01, Line 10
The transaction ended in the trigger. The batch has been aborted.
*/

AS

        3> 记录用户的登陆信息

SQL Server DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

--禁用 触发器  方法一

四、 行触发器

  1. DML触发器, 表/视图级有效,可由DML语句 (INSERT, UPDATE, DELETE) 触发;

  2. DDL 触发器,数据库级有效,可由DDL语句 (CREATE, ALTER, DROP 等) 触发;

  3. LOGON 触发器, 实例级有效,可由用户账号登录(LOGON)数据库实例时触发;

CREATE TRIGGER ins_Stu

EMP表只能在工作日修改。

--限制下班时间不能登录
DROP TRIGGER IF EXISTS limit_user_login_time ON ALL SERVER
GO
CREATE TRIGGER limit_user_login_time
ON ALL SERVER FOR LOGON 
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'TestUser' 
       AND (DATEPART(HOUR, GETDATE()) < 9 OR DATEPART (HOUR, GETDATE()) > 18)
    BEGIN
        PRINT 'TestUser can only login during working hours!'
        ROLLBACK
    END
END
GO

--限制连接数
DROP TRIGGER IF EXISTS limit_user_connections ON ALL SERVER
GO
CREATE TRIGGER limit_user_connections
ON ALL SERVER 
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'TestUser' 
       AND (SELECT COUNT(*) FROM   sys.dm_exec_sessions
            WHERE  Is_User_Process = 1 
            AND Original_Login_Name = 'TestUser') > 2
    BEGIN
        PRINT 'TestUser can only have 1 active session!'
        ROLLBACK
    END
END

DELETE FROM dbo.Students WHERE StudentName = 'Frank'

       2> 通过用户级别触发器修改日期格式

ALTER TABLE dbo.PriTable

七、 系统触发器

嵌套触发器,就是一次操作触发了一个触发器,然后触发器里的语句继续触发其他触发器,如果继续回头触发了自己,那么就是递归触发器。

select name,gender from inserted

      1> 创建视图

参考:

ON DATABASE;

         

金沙棋牌app手机下载 6

由于 INSTEAD OF 触发器一直在对基础表进行更新前激发,因此不能讲 INSTEAD OF 触发器指定为 第一或 最后 一个触发器

create or replace trigger dept_delete
after delete on dept
for each row
begin
   if deleting then
      delete from emp where deptno = :old.deptno;
   end if;
end;

4. 触发器中无法commit/rollback事务

ON DATABASE;

        1 row created.

但这个参数有两个另外:

应用程序更新了 表 T1, 从而触发了触发器Trig1 , Trig1 更新了表T2,从而出发了触发器 Trig2.Trig2转而更新了 表T1 , 从而再次触发了 Trig1.

一、 BEFORE触发器

 

EXEC sp_configure 'nested triggers'; --查看 nested triggers 选项设置

下面,我们结合具体的实例来演示不同的触发器。

在ORACLE中, 对表做一次DML操作产生一次触发,叫语句级触发器,另外还可以通过指定[FOR EACH ROW]子句,对于表中受影响的每行数据均触发,叫行级触发器,原有行用:OLD表示,新行用:NEW表示;

GO

六、 替代触发器

--禁用/启用LOGON触发器
DISABLE TRIGGER limit_user_connections ON ALL SERVER
ENABLE TRIGGER limit_user_connections ON ALL SERVER

用来判断 更新的是哪列, COLUMNS_UPDATED() 测试多个列, 但这个列 是 按字节  加起来算的,这个函数返回一个

        select student_seq.nextval into v_student_id from dual;

这个参数默认值为1, 也就是说允许AFTER触发器嵌套,最多嵌套32层,设为0就是不允许AFTER触发器嵌套,如下:

DISABLE TRIGGER Safety

      

(2) 嵌套触发器 (Nested Triggers), 循环/递归触发器 (Recursive Triggers)

SELECT * FROM INSERTED

      

一. DML触发器

RESTORE DATABASE  / RESTORE LOG

        SQL> insert into v_test values(1234,'VICTOR',50,'IT');

Database PL/SQL Language Reference, Using Triggers

if (update(name)) 

服务器本地,在SSMS中通过DAC登录

update dbo.Students set ClassID=5 where StudentName='Frank'

在ORACLE中,在表上支持BEFORE/AFTER触发器,在视图上支持INSTEAD OF触发器,比如ORACLE中无法直接对视图做DML操作,可以通过INSTEAD OF触发器来变样完成;

insert into dbo.test11

 

仅提交该嵌套事务,如果 在 commit 之后仍然有 ROLLBACK TRANSACTION 那么仍然会回滚到最外部的 事务。

SELECT * FROM DELETED

DROP TRIGGER [dbo].[INS_TEST]

GO

SELECT * FROM DELETED

print 'exists already!!!'

--重新启用 触发器 方法一:

INSERTED, DELETED 两张表要好好利用。

金沙棋牌app手机下载 7

CREATE TRIGGER update_Stu 

14第十四章触发器

    WHERE OrderID in (SELECT OrderID from Deleted);

SET RECURSIVE_TRIGGERS ON;

on all server;

本文由豆约翰博客备份专家远程一键发布

ON dbo.Students

ON DATABASE;

CREATE TRIGGER Bef_Ins 

要修改一个 DDL 触发器,也应当使用 ALTER TRIGGER 语句。 例如:

------------- DDL 触发器 ----------------------------

AFTER DELETE 

如果使用了 ALTER TRIGGER 语句 更改了 First 或 Last 触发器,则会删除它们的顺序值,必须使用 sp_settriggerorder 来重新设置。

ENABLE TRIGGER dbo.PriTrigger ON dbo.PriTable

    SELECT 

GO

数据修改可能会涉及 text/ ntext /image 列。 在基表中, 存储在 text/ ntext 或 image 列中 的 值是文本指针,它只想保存数据的 页  P353

ALTER TRIGGER MyTRIGGER

else

ON dbo.Students

Instead of 触发器:

SELECT * FROM INSERTED

insert into dbo.Students

-- instead of insert

原文链接

AS

无论是 DML 触发器还是DDL 触发器,如果出现了一个触发器执行启动另一个触发器的操作都属于嵌套触发器。32层

--启用触发器:

ON dbo.Students

values (4,'Frank',88.88)

AS

        ENABLE TRIGGER PriTrigger;

    PRINT N'CREATE TABLE 出错';

FOR UPDATE

CREATE TRIGGER del_Stu 

可以通过 OBJECTPROPERTY()函数的ExecIsFirstDeleteTrigger , ExecIsFirstInsertTrigger,ExecIsFirstUPdate....等属性来确定触发器时 First 触发器,还是LAST 触发器。

ALTER TRIGGER Safety

sys.server_triggers 目录视图查询服务器范围内的 DDL 触发器的信息。P354

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:DML触发器学习,14第十四章触发器

关键词: