NOT LIKE

小说:跑得快扑克游戏5张牌作者:侯扁龙更新时间:2019-02-18字数:81419

两人不断在这个盆地里面闪来闪去,来如风去如风,突然出现在任何地方都是没有任何的前兆,外人看去还以为他们两人是在闪来闪去,实际上两人都是在争分夺秒的进行飞雷神之术的较量,两人的身上都被对方留下了木式,因此不管双方去哪里,对方都能立刻察觉到。

167棋牌游戏手机下载

“……”艾薇儿泪流满面,她没有站起来,也没有说话,只是静静地坐着,看着雪飞鸿和林媚儿。
就在此刻,几个鬼子参谋跑来报告:“报告!汤架山辎重大队传来消息,他们阵地遭遇多股不明武装的袭击,形势危急,急需增援!”

通风说他转世十余次,这十余次他是被何人所杀?杀造化神猿者必得果报,如来是否会利用这一手段铲除异己呢?而他为何杀了阴阳神猿这么多次,难道……

NOT LIKE


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

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

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

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

 

. DML触发器

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

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

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

 

2. BEFORE/AFTER/INSTEAD OF

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

SQL Server DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

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

ORACLE DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

 

3. 触发条件

(1) 不能触发的情况

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

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

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

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

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

exec sp_configure "nested triggers"

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

exec sp_configure "nested triggers",0
RECONFIGURE

但这个参数有两个另外:

  • 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

 

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

  • 直接递归:就是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
  • 可以看出数据库选项RECURSIVE_TRIGGERS,仅对直接递归有效,对间接递归无效;可以通过Nest Triggers的开关来控制是否允许嵌套,从而控制是否允许间接递归;
  • 不论直接递归,还是间接递归,递归次数都有32次嵌套的上限;

总结下来:

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

2. INSTEAD OF触发器,不受Nest Triggers选项影响,均可以嵌套,上限32层,间接递归也是可以的,直接递归无论是否开启数据库选项RECUSIVE_TRIGGERS,都无效;把上面两个脚本示例中的AFTER改为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 2005开始支持DDL触发器,它不只限于对CREATE/ALTER/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 2005在SP2中悄悄引入了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中,实例级触发器可支持更多事件 (SERVERERROR, 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.

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

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

 

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

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

 

参考:

CREATE TRIGGER (Transact-SQL)

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017

Create Nested Triggers

https://docs.microsoft.com/en-us/sql/relational-databases/triggers/create-nested-triggers?view=sql-server-2017

Transact-SQL statements

https://docs.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-2017

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

https://community.oracle.com/thread/1082134

Database PL/SQL Language Reference, Using Triggers

https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS020

当前文章:http://0477auto.com/ask/question_71283.html

发布时间:2019-02-18 03:01:03

宝博游戏大厅斗地主 percent橙光游戏 积目app正式版下载v1 众发娱乐棋牌安卓版下载 欢乐炸金花全民破解版 祥运棋牌赤峰麻将 熊猫麻将俱乐部如何建立 四川血战麻将下载安装

编辑:徒宗道

我要说两句: (0人参与)

发布