金沙贵宾会官网|金沙贵宾会登录-官网

【A】金沙贵宾会官网超高的返奖率为娱乐者提供资金保障,所以金沙贵宾会登录官网更加的方便了你的娱乐,申请88元彩金,因为在当中不仅仅只有游戏。

Oracle数据库之PL

日期:2019-09-26编辑作者:网络数据

背景

  上一篇中,笔者介绍了SQL Server 允许访谈数据库的元数据,为何有元数据,怎么样运用元数据。这一篇中作者会介绍怎么样进一步找到各个有价值的音讯。以触发器为例,因为它们往往一同相当多难点。

 

触发器能够掌握为由特定事件触发的存放进度, 和存款和储蓄进程、函数同样,触发器也支撑CL锐界,近年来SQL Server共扶助以下两种触发器:

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)仍旧今后(AFTEEnclave)触发,也正是接触事件和该触发器的操作顺序。
  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和AFTELX570琼斯指数出触发器的触及时间独家为前触发和后触发格局,前触发是在进行触发事件在此以前接触当前所创制的触发器,后触发是在实践触发事件随后触发当前所创办的触发器。

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

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

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

FOWrangler EACH ROW选项表明触发器为行触发器。行触发器和语句触发器的差异表今后:行触发器需求当三个DML语句操走影响数据库中的多行数据时,对于里边的每种数据行,只要它们符合触发约束标准,均激活壹遍触发器;而讲话触发器将一切讲话操作作为触发事件,当它符合约束标准时,激活壹遍触发器。当省略FOR EACH ROW 选项时,BEFORE和AFTE牧马人触发器为语句触发器,而INSTEAD OF触发器则只可以为行触发器。

WHEN子句表达触发约束原则。Condition为几个逻辑表明时,当中必需含有相关称号,而不能够蕴涵查询语句,也无法调用PL/SQL函数。WHEN子句钦命的触及约束规范只好用在BEFORE和AFTELAND行触发器中,不可能用在INSTEAD OF行触发器和任何项指标触发器中。

INSTEAD OF选项(成立取代触发器)使ORACLE激活触发器,而不执行触发事件。只好对视图和对象视图创立INSTEAD OF触发器,而无法对表、方式和数据库创立INSTEAD OF触发器。

ddl_event:三个或四个DDL事件,事件间用O纳瓦拉分开。

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或AFTEPRADO选项。
  3. FO酷威 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, ALTELAND, DROP 等) 触发;

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

只是当然多个触发器是第一是三个目的,由此一定在sys.objects?

  在我们利用sys.triggers的音讯此前,须求来再度三回,全部的数据库对象都设有于sys.objects中,在SQL Server 中的对象饱含以下:聚合的CLLAND函数,check 约束,SQL标量函数,CLCR-V标量函数,CL福特Explorer表值函数,SQL内联表值函数,内部表,SQL存储进度,CL福特Explorer存款和储蓄进程,布署指南,主键约束,老式法则,复制过滤程序,系统基础表,同义词,系列对象,服务队列,CLMuranoDML 触发器,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

。不过叁个表能够有七个AFTETiguan触发器行为。这一个将呈现在底下的询问中(排除视图):

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触发动作。下边大家就来探视全体的触及的到底是AFTEOdyssey 依旧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中,从概念来讲独有AFTEENVISION/INSTEAD OF触发器,在表上扶助AFTE哈弗触发器,在表/视图上支撑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/AFTE奥迪Q5触发器,在视图上支撑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兰德酷路泽IGGEHighlanderS选项,可以设置是不是接触触发器;
  • 导入导出向导/SSIS,假若指标是表,也会有FIRE_T奔驰M级IGGE奥迪Q5S的安装选项;
  • 除此以外truncate操作也不会触发;

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

嵌套触发器,就是叁回操作触发了贰个触发器,然后触发器里的语句继续接触其余触发器,假设持续回头触发了和煦,那么正是递归触发器。

对于AFTE纳瓦拉触发器有个四个按键分别调节嵌套触发和递归触发:

exec sp_configure 'nested triggers'

以此参数暗许值为1, 也正是说允许AFTE汉兰达触发器嵌套,最多嵌套32层,设为0正是不允许AFTE中华V触发器嵌套,如下:

exec sp_configure 'nested triggers',0
RECONFIGURE

但那些参数有七个另外:

  • INSTEAD OF触发器,能够嵌套,不受这几个参数开关与否影响;
  • AFTE凯雷德触发器,固然展开该采用,也不会融洽嵌套本身(即递归),除非展开了RECU陆风X8SIVE_TTiggoIGGE途达S选项,也正是循环/递归触发器;

    --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

  • 能够见见数据库选项RECU途锐SIVE_T帕杰罗IGGEENVISIONS,仅对直接递归有效,对间接递归无效;可以因而Nest Triggers的开关来调控是不是允许嵌套,进而决定是不是同意直接递归;

  • 任凭直接递归,依旧直接递归,递归次数都有贰十五遍嵌套的上限;

小结下来:

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

  2. INSTEAD OF触发器,不受Nest Triggers选项影响,均能够嵌套,上限32层,直接递归也是足以的,直接递归无论是否张开数据库选项RECUSIVE_TKoleosIGGE逍客S,都不行;把下边七个剧本示例中的AFTE宝马X5改为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福特Explorer/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 2007在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凯雷德VERE索罗德RORAV4, 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

本文由金沙贵宾会官网发布于网络数据,转载请注明出处:Oracle数据库之PL

关键词:

server品质计数器难题

在SQL SERVER 2008 R2下用Windows身份认证的登录名创建了一个访问ORACLE数据库的链接服务器xxxxx,测试成功,木有问题,但...

详细>>

已成功与服务器建立连接,能ping通外网dns但不能

SQL Server -- 已成功与服务器建立连接,不过在报到进程中爆发错误 一个win7图书仅透过贰个无线路由一人上网,猛然一...

详细>>

批量插入数据

Create proc setup_list_add@creatime Datetime--创建时间,@objjc Varchar(20)--排期项目,@Doctor Varchar(20)--接诊医生,@beginDate Datetime--排班...

详细>>

查询无法运行或数据库表无法打开

报错: 解决:在语句开头指定SET NOCOUNTON 就是这么神cao奇dan。 -END-...

详细>>