澳门金莎娱乐手机版 数据库 Server版本中有什么触发器,Server共支持以下几种触发器

Server版本中有什么触发器,Server共支持以下几种触发器



背景

  上生机勃勃篇中,笔者介绍了SQL Server
允许访问数据库的元数据,为何有元数据,如何使用元数据。那后生可畏篇中作者会介绍怎么着进一层找到种种有价值的音信。以触发器为例,因为它们往往一齐比非常多难点。

 

触发器能够精通为由特定事件触发的存款和储蓄进程,
和存款和储蓄进度、函数同样,触发器也支撑CL揽胜,近期SQL
Server共支持以下二种触发器:

Oracle数据库之PL/SQL触发器

1. 介绍

触发器(trigger)是数据库提须要程序猿和多少深入分析员来保证数据完整性的风流浪漫种办法,它是与表事件相关的奇异的囤积进程,它的实施不是由程序调用,亦非手工业运行,而是由事件来触发,譬如当对几个表伸开操作(insert,delete,update卡塔 尔(英语:State of Qatar)时就能够激活它实践。触发器平日用来抓实数据的完整性节制和职业准绳等。

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

DML触发器

看名称就能够想到其意义,DML触发器是由DML语句触发的。比如数据库的INSERT、UPDATE、DELETE操作都能够触发该类型的触发器。它们能够在此些讲话在此之前或之后触发,或许在行级上接触(正是说对于每一种受影响的行都触发贰回)。

代表触发器

代替触发器只好选取在视图上,与DML不相同的是,DML触发器是运营在DML之外的,而顶替触发器是顶替激发它的DML语句运维。代替触发器是行触发器。

系统触发器

这种触发器是产生在如数据库运行或关闭等系统事件时,不是在实行DML语句时发出,当然也足以在DDL时接触。

触发器成效强盛,轻易可信赖地促成无数复杂的功力,可是大家也应当慎用。为什么又要慎用呢?触发器自身并未过错,但万黄金年代大家滥用,会促成数据库及应用程序的保险困难。在数据库操作中,我们得以因此关系、触发器、存款和储蓄进度、应用程序等来实现数量操作,同时限制、缺省值也有限支撑数据完整性的根本保险。要是大家对触发器过分的依据,势必影响数据库的布局,同有的时候候扩大了爱抚的复杂程度。

2. 触发器组成

触发器首要由以下多少个成分构成:

  1. 接触事件:引起触发器被触发的平地风波。
  2. 接触时间:触发器是在触发事件爆发早前(BEFORE卡塔 尔(阿拉伯语:قطر‎照旧将来(AFTERAV4)触发,也正是触发事件和该触发器的操作顺序。
  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和AFTE路虎极光建议触发器的触及时间独家为前触发和后触发情势,前触发是在实行触发事件此前接触当前所创建的触发器,后触发是在实践触发事件今后触发当前所创制的触发器。

REFERENCING子句表达有关称号,在行触发器的PL/SQL块和WHEN子句中得以接收相关称号参照当前的新、旧列值,私下认可的连带称号为OLD和NEW。触发器的PL/SQL块中动用相关称号时,必得在它们此前加冒号(:),但在WHEN子句中则不可能加冒号。

NEW只在UPDATE、INSERT的DML触发器内可用,它包罗了改造爆发后被耳濡目染行的值。

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

FOTiguan EACH
ROW选项表达触发器为行触发器。行触发器和讲话触发器的分裂表现在:行触发器要求当四个DML语句操走影响数据库中的多行数据时,对于内部的每一个数据行,只要它们相符触发节制标准,均激活二遍触发器;而讲话触发器将全体讲话操作作为触发事件,当它符合约束标准时,激活一遍触发器。当省略FOR
EACH ROW 选项时,BEFORE和AFTERubicon触发器为语句触发器,而INSTEAD
OF触发器则只可以为行触发器。

WHEN子句表达触发约束原则。Condition为多少个逻辑表明时,在那之中必需满含相关称号,而不可能满含查询语句,也无法调用PL/SQL函数。WHEN子句钦点的触发节制规范只好用在BEFORE和AFTE翼虎行触发器中,不能够用在INSTEAD
OF行触发器和别的类型的触发器中。

INSTEAD
OF选项(创立取代触发器卡塔 尔(阿拉伯语:قطر‎使ORACLE激活触发器,而不奉行触发事件。只好对视图和指标视图创设INSTEAD
OF触发器,而无法对表、格局和数据库构造建设INSTEAD OF触发器。

ddl_event:二个或几个DDL事件,事件间用OKuga分开。

database_event:四个或两个数据库事件,事件间用O奇骏分开。

亲自去做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或AFTECRUISER选项。
  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;

 

那么如何找到触发器的多寡?

*  以sys.system_views*is表开首。让我们询问出数据库中应用触发器的音信。能够告诉您日前SQL
Server版本中有怎么着触发器。

SELECT schema_name(schema_ID)+'.'+ name

  FROM sys.system_views WHERE name LIKE '%trigger%'

 ----------------------------------------

sys.dm_exec_trigger_stats              

sys.server_trigger_events              

sys.server_triggers                    

sys.trigger_event_types                

sys.trigger_events                     

sys.triggers                           



(6 row(s) affected)

  此中sys.triggers看起来新闻相当多,它又蕴含怎样列?上边这几个查询十分轻松查到:

 SELECT Thecol.name+ ' '+ Type_name(TheCol.system_type_id)

  + CASE WHEN TheCol.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.system_views AS TheView

  INNER JOIN sys.system_columns AS TheCol

    ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers'

  ORDER BY column_ID;

结果如下:

 Column_Information

----------------------------------------

name nvarchar NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar NULL

parent_id int NOT NULL

type char NOT NULL

type_desc nvarchar NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

之所以我们多这几个音信有了更加好的知道,有了一个索引的目录。这么些定义有一点令人头晕,可是其他方面,它也是豆蔻梢头对一简单的。我们能够意识到元数据,再找个查询中,需求做的正是改造这些单词‘triggers’来查找你想要的视图名称。.

在二零一二及其现在版本,能够应用二个新的表值函数十分的大地简化上述查询,并得以制止各样连接。在底下的询问中,大家将寻觅sys.triggers
视图

中的列。能够选拔同生龙活虎的查询通过校正字符串中的对象名称来收获别的视图的概念。

 SELECT name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( N'SELECT * FROM sys.triggers;', NULL, 0) AS f

  ORDER BY column_ordinal;

询问结果如下:

 Column_Information

----------------------------------------

name nvarchar(128) NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar(60) NULL

parent_id int NOT NULL

type char(2) NOT NULL

type_desc nvarchar(60) NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

sys.dm_exec_describe_first_result_set函数的最大优势在于你能来看其他结果的列,不只有是表和视图、存款和储蓄进度大概贬值函数。

为了摸清任何列的音讯,你能够行使稍稍改善的本子,只供给改造代码中的字符串’sys.triggers’就能够,如下:

 Declare @TheParamater nvarchar(255)

Select @TheParamater = 'sys.triggers'

Select @TheParamater = 'SELECT * FROM ' + @TheParamater

SELECT

  name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( @TheParamater, NULL, 0) AS f

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

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

  3. LOGON 触发器, 实例级有效,可由顾客账号登陆(LOGON)数据库实例时接触;

而是当然叁个触发器是第一是三个对象,因而一定在sys.objects?

  在大家选拔sys.triggers的音信从前,须求来重新一次,全体的数据库对象都设有于sys.objects中,在SQL
Server 中的对象富含以下:聚合的CLPAJERO函数,check
节制,SQL标量函数,CLENVISION标量函数,CLLacrosse表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进程,CLR存款和储蓄进程,安顿指南,主键限制,老式准绳,复制过滤程序,系统底子表,同义词,系列对象,服务队列,CL库罗德DML
触发器,SQL表值函数,表类型,客商自定义表,唯生机勃勃节制,视图和扩张存款和储蓄进程等。

  触发器是目的所以底蕴音讯一定保存在sys.objects。不幸运的是,不经常大家须求相当的新闻,那么些音讯方可由此目录视图查询。这个额外数据有是什么吗?

 

  改革大家接收过的询问,来询问sys.triggers的列,这一次我们会见到额外音信。那一个额外列是来源于于sys.objects。

 SELECT coalesce(trigger_column.name,'NOT INCLUDED') AS In_Sys_Triggers,

       coalesce(object_column.name,'NOT INCLUDED') AS In_Sys_Objects

FROM

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers') trigger_column

FULL OUTER JOIN

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'objects') object_column

ON trigger_column.name=object_column.name

查询结果:

In_Sys_Triggers                In_Sys_Objects

------------------------------ ----------------------

name                           name

object_id                      object_id

NOT INCLUDED                   principal_id

NOT INCLUDED                   schema_id

NOT INCLUDED                   parent_object_id

type                           type

type_desc                      type_desc

create_date                    create_date

modify_date                    modify_date

is_ms_shipped                  is_ms_shipped

NOT INCLUDED                   is_published

NOT INCLUDED                   is_schema_published

is_not_for_replication         NOT INCLUDED

is_instead_of_trigger          NOT INCLUDED

parent_id                      NOT INCLUDED

is_disabled                    NOT INCLUDED

parent_class                   NOT INCLUDED

parent_class_desc              NOT INCLUDED

 

上述那一个让我们领略在sys.triggers的额外音讯,不过因为它始终是表的子对象,所以有个别不相干音讯是不会来得在那一个钦赐的视图恐怕sys.triggers中的。将来快要带我们去继续找找那么些消息。

 

触发器的标题

  触发器是一蹴而就的,可是因为它们在SSMS对象财富管理器窗格中不是可以知道的,所以日常用来提醒错误。触发器不经常候会略带微妙的地方让其出题目,比方,当导入进程中禁止使用了触发器,而且由于一些原因他们未有重启。

上边是贰个关于触发器的简要提醒:

  触发器能够在视图,表可能服务器上,任何那些指标上都能够有超过常规1个触发器。普通的DML触发器能被定义来试行代替一些数据修改(Insert,Update或许Delete卡塔尔或许在数码订正现在实施。每二个触发器与只与二个目的管理。DDL触发器与数据库关联恐怕被定义在服务器等级,那类触发器经常在Create,Alter只怕Drop那类SQL语句履行后触发。

  像DML触发器同样,能够有多个DDL触发器被成立在同贰个T-SQL语句上。一个DDL触发器和言语触发它的话语在同二个作业中运作,所以除了Alter
DATABASE之外都足以被回滚。DDL触发器运营在T-SQL语句施行完结后,也便是无法作为Instead
OF触发器使用。

  二种触发器都与事件有关,在DML触发器中,包罗INSERT, UPDATE,
和DELETE,然而不少事变都足以与DDL触发器关联,稍后大家将精通。

一. DML触发器

在数据库中列出触发器

那就是说怎么获取触发器列表?上面小编在AdventureWorks数据库中张开查询,注意该库的视图中尚无触发器。

率先个查询全部新闻都在sys.triggers 的目录视图中。

SELECT

  name AS TriggerName,

  coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')') AS TheParent

FROM sys.triggers;



TriggerName                    TheParent

------------------------------ ----------------------------------------

ddlDatabaseTriggerLog          Database (AdventureWorks2012)          

dEmployee                      HumanResources.Employee                

iuPerson                       Person.Person                          

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader         

iduSalesOrderDetail            Sales.SalesOrderDetail                 

uSalesOrderHeader              Sales.SalesOrderHeader                 

dVendor                        Purchasing.Vendor                      

iWorkOrder                     Production.WorkOrder                   

uWorkOrder                     Production.WorkOrder   

  作者利用元数据函数db_name()使SQL保持轻易。db_name()告诉作者数据库的称呼。object_schema_name()用来询问object_ID代表的目的的架构,以至object_name**()**查询对象名称。那么些对目的的引用指向触发器的主人,触发器能够是数据库本人,也得以是表:服务器触发器有投机的系列视图,稍后作者展销会示。

万意气风发想要看见全体触发器,那么我们最棒使用sys.objects 视图:

SELECT name as TriggerName, object_schema_name(parent_object_ID)+'.'

    +object_name(parent_object_ID) AS TheParent

            FROM   sys.objects

           WHERE  OBJECTPROPERTYEX(object_id,'IsTrigger') = 1

 

留心,输出不包涵数据库级其余触发器,因为全数的DML触发器都在sys.objects视图中,可是你会挂生机勃勃漏万在sys.triggers视图中的触发器。

地点查询结果:

name                           TheParent

------------------------------ -------------------------------

dEmployee                      HumanResources.Employee

iuPerson                       Person.Person

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader

iduSalesOrderDetail            Sales.SalesOrderDetail

uSalesOrderHeader              Sales.SalesOrderHeader

dVendor                        Purchasing.Vendor

iWorkOrder                     Production.WorkOrder

uWorkOrder                     Production.WorkOrder

 

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

笔者的表和视图有稍许个触发器?

自己想清楚种种表有多少个触发器,而且什么动静下接触它们。下边大家列出了具有触发器的表以致种种事件的触发器数量。每一个表也许视图对于触发器行为都有贰个INSTEAD
OF 触发器,大概是UPDATE, DELETE, 也许 INSERT

。可是多少个表能够有八个AFTEEvoque触发器行为。那些将显示在上面包车型客车查询中(消除视图卡塔尔国:

SELECT

convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS 'Table', triggers,[KD1] [AC2] 

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEDeleteTriggerCount')) AS 'Delete',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEInsertTriggerCount')) AS 'Insert',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEUpdateTriggerCount')) AS 'Update'

FROM (SELECT count(*) AS triggers, parent_ID FROM sys.triggers

      WHERE objectpropertyex(parent_ID, N'IsTable') =1

         GROUP BY parent_ID

          )TablesOnly;

--查询结果如下:

Table                            triggers    Delete Insert Update

-------------------------------- ----------- ------ ------ ------

Purchasing.Vendor                1           0      0      0

Production.WorkOrder             2           0      1      1

Purchasing.PurchaseOrderDetail   2           0      1      1

Purchasing.PurchaseOrderHeader   1           0      0      1

Sales.SalesOrderDetail           1           1      1      1

HumanResources.Employee          1           0      0      0

Sales.SalesOrderHeader           1           0      0      1

Person.Person                    1           0      1      1



(8 row(s) affected)

比如逾越一个触发器被触发在三个表上,它们不保险顺序,当然也得以选取sp_settriggerorder来决定顺序。通过动用objectpropertyex()元数据函数,要求基于事件输入参数‘ExecIsLastDeleteTrigger’,
‘ExecIsLastInsertTrigger’ 或许‘ExecIsLastUpdateTrigger’来确认谁是最后三个实践的触发器
。为了获得第三个触发器,酌情采取ObjectPropertyEx()
元数据函数,必要输入参数 ‘ExecIsFirstDeleteTrigger’,
‘ExecIsFirstInsertTrigger’ 或然 ‘ExecIsFirstUpdateTrigger’。

进而大家未来驾驭了表有何触发器,哪些事件触发这个触发器。能够选取objectpropertyex()元数据函数,这么些函数重回非常多两样音信,依照钦定的参数分歧。通过查阅MSDN中的文书档案,查看里面包车型大巴三个文书档案是或不是有帮衬元数据查询,总是值得检查的。

在SQL
Server中,从概念来讲唯有语句级触发器,但如若有行级的逻辑要管理,有多少个仅在触发器内有效的表
(inserted, deleted),
存放着受影响的行,能够从那多少个表里抽取特定的行并自行定义脚本管理;

触发器什么日期触发事件?

让大家看一下这几个触发器,DML触发器能够在拥有其他时间发出后触发,可是能够在限定被拍卖前并且触发INSTEAD
OF触发动作。上面大家就来看看全部的触及的终究是AFTE奥德赛 照旧INSTEAD OF
触发器,有事什么日子接触了触发器。

/* 列出触发器,无论它们是否启用,以及触发器事件。*/

SELECT

  convert(CHAR(25),name) AS triggerName,

  convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS TheParent,

       is_disabled,

       CASE WHEN is_instead_of_trigger=1 THEN 'INSTEAD OF ' ELSE 'AFTER ' END

       +Stuff (--get a list of events for each trigger

        (SELECT ', '+type_desc FROM sys.trigger_events te

           WHERE te.object_ID=sys.triggers.object_ID

         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS events

 FROM sys.triggers;

结果如下:

triggerName               TheParent                        is_disabled events

------------------------- -------------------------------- ----------- ---------

ddlDatabaseTriggerLog     Database (AdventureWorks2012)    1           AFTER CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_INDEX, ALTER_INDEX, DROP_INDEX, CREATE_XML_INDEX, ALTER_FULLTEXT_INDEX, CREATE_FULLTEXT_INDEX, DROP_FULLTEXT_INDEX, CREATE_SPATIAL_INDEX, CREATE_STATISTICS, UPDATE_STAT

t_AB                      dbo.AB                           0           INSTEAD OF INSERT

dEmployee                 HumanResources.Employee          0           INSTEAD OF DELETE

iuPerson                  Person.Person                    0           AFTER INSERT, UPDATE

iPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER INSERT

uPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER UPDATE

uPurchaseOrderHeader      Purchasing.PurchaseOrderHeader   0           AFTER UPDATE

iduSalesOrderDetail       Sales.SalesOrderDetail           0           AFTER INSERT, UPDATE, DELETE

uSalesOrderHeader         Sales.SalesOrderHeader           0           AFTER UPDATE

dVendor                   Purchasing.Vendor                0           INSTEAD OF DELETE

iWorkOrder                Production.WorkOrder             0           AFTER INSERT

uWorkOrder                Production.WorkOrder             0           AFTER UPDATE

 

As you will notice, we used a FOR XML PATH(‘’)
trick
here to make a list of the events for each trigger to make it easier to
read. These events were pulled from the sys.trigger_events view using
a correlated subquery.

在乎到大家利用了FOR XML
PATH(‘’)来列出事件的每三个触发器,更便于读取精通。sys.trigger_events选取相关子查询来查询那个事件。

在ORACLE中,
对表做叁次DML操作产生叁遍接触,叫语句级触发器,其余还足以经过点名[FOR
EACH
ROW]子句,对于表中受影响的每行数据均触发,叫行级触发器,原有行用:OLD表示,新行用:NEW表示;

触发器的多少长度?

多许多据库职员不赞成冗长触发器的概念,但她们大概会开掘,依据定义的长短排序的触发器列表是钻探数据库的意气风发种有用艺术。

SELECT convert(CHAR(32),coalesce(object_schema_name(t.object_ID)+'.','')

    +name) AS TheTrigger,

       convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS theParent,

       len(definition) AS length --the length of the definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

ORDER BY length DESC;

访问sys.SQL_modules视图可以查阅触发器定义的SQL
DDL,并按大小顺系列出它们,最上边是最大的。

结果:

TheTrigger                       theParent                        length

-------------------------------- -------------------------------- --------

Sales.iduSalesOrderDetail        Sales.SalesOrderDetail           3666

Sales.uSalesOrderHeader          Sales.SalesOrderHeader           2907

Purchasing.uPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   2657

Purchasing.iPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   1967

Person.iuPerson                  Person.Person                    1498

ddlDatabaseTriggerLog            Database (AdventureWorks2012)    1235

Purchasing.dVendor               Purchasing.Vendor                1103

Production.uWorkOrder            Production.WorkOrder             1103

Purchasing.uPurchaseOrderHeader  Purchasing.PurchaseOrderHeader   1085

Production.iWorkOrder            Production.WorkOrder             1011

HumanResources.dEmployee         HumanResources.Employee          604

 

行吗,小编也许太斥责了,不太喜欢太长的,不过逻辑一时候会不短。事实上,前三名在笔者眼里是不可信赖的,就算自个儿连连趋向于尽大概少地接受触发器。

 

那一个触发器访问了稍微对象

在代码中,每一种触发器要拜会多少对象(举个例子表和函数)?

大家只需求检查表明式依赖项。这些查询利用三个视图来列出“软”信任项(如触发器、视图和函数)。

SELECT coalesce(object_schema_name(parent_id)

          +'.','')+convert(CHAR(32),name) AS TheTrigger,

          count(*) AS Dependencies

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

GROUP BY name, parent_id

ORDER BY count(*) DESC;
--结果:

TheTrigger                               Dependencies

---------------------------------------- ------------

Sales.iduSalesOrderDetail                7

Sales.uSalesOrderHeader                  7

Purchasing.iPurchaseOrderDetail          5

Purchasing.uPurchaseOrderDetail          5

Purchasing.uPurchaseOrderHeader          3

Production.iWorkOrder                    3

Production.uWorkOrder                    3

dbo.t_AB                                 2

Purchasing.dVendor                       2

Person.iuPerson                          2

ddlDatabaseTriggerLog                    1

 

以至有七个触发器有7个借助!让我们就Sales.iduSalesOrderDetail来实在看一下,有哪些正视。

2. BEFORE/AFTER/INSTEAD OF

一定触发器访问照旧写入哪些对象?

作者们得以列出触发器在代码中引用的具有指标

SELECT

  convert(char(32),name) as TheTrigger,

  convert(char(32),coalesce([referenced_server_name]+'.','')

            +coalesce([referenced_database_name]+'.','')

       +coalesce([referenced_schema_name]+'.','')+[referenced_entity_name])
     as referencedObject

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

WHERE name LIKE 'iduSalesOrderDetail';

--查询结果:

TheTrigger                       referencedObject

-------------------------------- --------------------------------

iduSalesOrderDetail              Sales.Customer                 

iduSalesOrderDetail              Person.Person                  

iduSalesOrderDetail              Sales.SalesOrderDetail         

iduSalesOrderDetail              Sales.SalesOrderHeader          

iduSalesOrderDetail              Production.TransactionHistory  

iduSalesOrderDetail              dbo.uspLogError                

iduSalesOrderDetail              dbo.uspPrintError

 

在SQL Server中,从概念来讲唯有AFTE奥迪Q5/INSTEAD
OF触发器,在表上帮忙AFTERAV4触发器,在表/视图上扶助INSTEAD
OF触发器,对于BEFORE触发器的须要可以品味通过INSEAD OF触发器来兑现;

触发器里有何样代码?

前天让大家通过检查触发器的源代码来确认那或多或少。.

SELECT OBJECT_DEFINITION ( object_id('sales.iduSalesOrderDetail') ); 

我们事先的查询是不利的,扫描源码可以预知全数的依赖项。多量借助项表名对于数据库的重构等须求特别小心,举个例子,更正三个功底表的列。

据须求做什么样,您只怕希望检查来自元数据视图的定义,实际不是选用OBJECT_DEFINITION函数。

 SELECT definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

WHERE t.object_ID=object_id('sales.iduSalesOrderDetail');

SQL Server DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

招来触发器的代码

There are always plenty of ways of using the metadata views and
functions. I wonder if all these triggers are executing that
uspPrintError procedure?

有成都百货上千选用元数据视图和函数的办法。想知道是或不是有所这么些触发器都奉行uspPrintError存款和储蓄进度?

/* 在具有触发器中寻觅字符串 */

 

SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','')

    +name) AS TheTrigger, '...'+substring(definition, hit-20,120) +'...'

FROM

  (SELECT name, definition, t.object_ID, charindex('EXECUTE [dbo].[uspPrintError]',definition) AS hit

   FROM sys.SQL_modules m

     INNER JOIN sys.triggers t

       ON t.object_ID=m.object_ID)f

WHERE hit>0; 

 

结果如图:

图片 1

 

8个援引正在施行这么些进度。大家在sys.SQL_modules中寻找了全体的定义能够找到四个一定的字符串,这种艺术一点也不快很暴力,可是它是一蹴而就的!

在ORACLE中,在表上支持BEFORE/AFTEENVISION触发器,在视图上扶植INSTEAD
OF触发器,譬如ORACLE中不大概直接对视图做DML操作,能够透过INSTEAD
OF触发器来变样达成;

在享有目的中搜索字符串

本人想知道除了触发器之外是或不是还会有此外对象调用这么些历程?大家多少改良查询以寻觅sys.objects视图,而不是sys.triggers,以搜寻全部具有与之提到的代码的对象。我们还索要出示对象的门类

/* 在有着指标中追寻字符串 */

 SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','')

    +object_name(object_ID)) AS TheObject, type_desc, '...'+substring(definition,hit-20,120)+'...' as TheExtract

FROM

  (SELECT  type_desc, definition, o.object_ID, charindex('uspPrintError',definition) AS hit

   FROM sys.SQL_modules m

     INNER JOIN sys.objects o

       ON o.object_ID=m.object_ID)f

WHERE hit>0; 

询问结果如下图:

图片 2

 From this output we can see that, other than the procedure itself where
it is defined, and the triggers, only dbo.uspLogError is executing the
uspPrintError procedure. (see the first column, second line down)

从这一个输出中大家得以看来,除了在概念它的进程本身之外,还或许有触发器,只有dbo.uspLogError正值实践uspPrintError进程。(见第一列,第二行往下)

ORACLE DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

列出劳动器级触发器及其定义

我们得以经过系统视图精通它们啊?嗯,是的。以下是列出服务器触发器及其定义的说话

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

瞩目,只可以看看有权力看的触发器

 

总结

  本文探讨过触发器,并且你能意识到触发器,以致地下的难题。这里并未指向有关触发器的询问提供八个全面的工具箱,因为本人只是利用触发器作为示范来显示在询问系统视图时或者使用的有的本事。在我们上学了目录、列和参数之后,大家将重回触发器,并打听了编写制定访问系统视图和information
schema视图的询问的朝气蓬勃部分兴致索然用场。表是元数据的不在少数上边的根基。它们是二种档期的顺序的靶子的父类,其余元数据如索引是表的品质。咱们正在慢慢地质大学力去开掘具有有关表的信息。期待下一期

3. 接触条件

(1) 无法接触的气象

对于UPDATE,DELETE操作来讲,均会触发触发器;而对此INSERT或然说IMPORT的意况,是能够垄断不去接触的。

  • 大量导入操作,如:BULK INSERT, bcp/INSERT… SELECT * FROM
    OPENROWSET,都有FIRE_TRIGGERS/IGNORE_T奥德赛IGGE安德拉S选项,可以安装是或不是接触触发器;
  • 导入导出向导/SSIS,借使目的是表,也可能有FIRE_T宝马7系IGGETiggoS的设置选项;
  • 别的truncate操作也不会触发;

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

嵌套触发器,正是二回操作触发了三个触发器,然后触发器里的语句继续接触别的触发器,如若持续回头触发了友好,那么正是递归触发器。

对于AFTEHaval触发器有个五个按钮分别调整嵌套触发和递归触发:

exec sp_configure 'nested triggers'

以此参数暗中认可值为1,
也等于说允许AFTE奥德赛触发器嵌套,最多嵌套32层,设为0正是不允许AFTEOdyssey触发器嵌套,如下:

exec sp_configure 'nested triggers',0
RECONFIGURE

但以此参数有多个其它:

  • INSTEAD OF触发器,能够嵌套,不受这几个参数按键与否影响;
  • AFTECR-V触发器,即使张开该接收,也不会友善嵌套自身(即递归),除非展开了RECUMuranoSIVE_T帕杰罗IGGEGL450S选项,也便是循环/递归触发器;

    –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

 

循环/递归触发器的前提就是嵌套触发器,唯有同意嵌套了才足以递归(递归也正是嵌套并触及本人),递归有平昔和直接二种情景:

  • 一向递归:就是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_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

  • 能够阅览数据库选项RECUENVISIONSIVE_T汉兰达IGGE卡宴S,仅对向来递归有效,对间接递归无效;能够透过Nest
    Triggers的开关来决定是或不是同意嵌套,进而调整是还是不是同意直接递归;

  • 无论直接递归,依旧直接递归,递归次数都有三十陆遍嵌套的上限;

总括下来:

  1. AFTE瑞鹰触发器,暗中同意Nest
    Triggers值为1,即允许触发器嵌套,上限32层,直接递归也是可以的,直接递归需求开启数据库选项RECUCRUISERSIVE_TRIGGERS;

  2. INSTEAD OF触发器,不受Nest
    Triggers选项影响,均能够嵌套,上限32层,直接递归也是能够的,间接递归无论是或不是开启数据库选项RECUSIVE_T瑞虎IGGESportageS,都船到江心补漏迟;把地点七个本子示例中的AFTEENVISION改为INSTEAD
    OF就可以演示。

 

4.
触发器中不恐怕commit/rollback事务

--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.
*/

在SQL
Server和Oracle中都以这么,触发器作为所有的事情的生龙活虎部分存在,不过并不调控总体业务的交付/回滚,为保障数据后生可畏致性,事务逻辑由触发器外层的语句来调控。

 

二. DDL触发器

SQL Server
二〇〇六早前补助DDL触发器,它不只限于对CREATE/ALTE途乐/DROP操作可行,匡助的DDL事件还会有诸如:权限的GRANT/DENY/REVOEK,
对象的RENAME, 更新总计音信等等,可经过DMV查看更加多扶持的事件类型如下:

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

注意:

  1. TRUNCATE不在DDL触发器的轩然大波类型中,SQL Server师长Truncate
    归为DML操作语句,固然它也并不触发DML触发器,就像是张开开关的大量导入操作
    (Bulk Import Operations) 肖似;

2.
DDL触发器中抓获的音信都由EVENTDATA()函数再次来到,再次回到类型为XML格式,需求用XQuery来读取;

 

代码示例1:记录全体table上的一些DDL操作

--记录所有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

 

代码示例2:禁绝特定剧中人物的顾客对特定的表做DROP操作

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

 

三. LOGON 触发器

SQL Server
二〇〇七在SP第22中学私自引入了LOGON触发器,作为二个实例级的目的,它的体系视图,定义语句和DDL/DML触发器都以分别的。

select * from sys.server_triggers where name = 'login_history_trigger'
select * from sys.server_trigger_events
select OBJECT_ID('login_history_trigger') --无法获取

在SQL Server中,看名称就能够想到其意义,LOGON触发器,只扶助LOGON事件;

在ORACLE中,实例级触发器可支撑更加多事件 (SE雷克萨斯LCVEREENVISIONRO猎豹CS6, LOGON, LOGOFF,
STARTUP, or SHUTDOWN)。

 

代码示例1: 笔录全数login登陆历史 (其实也得以透过改进login
auditing选项,来记录成功和波折的登陆在errorlog里)

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

 

代码示例2: 界定特定客商在特准时间节制登陆、节制连接数

--限制下班时间不能登录
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

 

小心:假设LOGON触发器把全数人都锁在外边了如何是好?

Logon failed for login ‘TestUser’ due to trigger execution.

图片 3

那会儿,只好通过DAC登录SQL
Server去禁止使用LOGON触发器/改善逻辑以允许登陆,DAC登陆方式有长途和本地三种,远程登陆要求通过sp_configure
开启remote admin connections
,若无先行开启,那就只能接收本地登陆格局:

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

图片 4

 

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

图片 5

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

 

参考:

CREATE TRIGGER (Transact-SQL)

Create Nested Triggers

Transact-SQL statements

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

Database PL/SQL Language Reference, Using Triggers

标签:

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章

网站地图xml地图