和master一样记录信息,所以无法获得对数据库的

2019-09-16 作者:数据库   |   浏览(100)

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

一、什么是数据库镜像

    基本软件的高可用性解决方案

  快速的故障转移恢复(3秒转移),低硬件成本

  基于数据库级别的实现

系统数据库

还原sql server 2012数据库时,经常会出现:

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

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

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

二、数据库镜像中的服务器角色

        主体服务器

    承载主体数据库

    接受用户连接和事务处理请求

        镜像服务器

    承载镜像数据库

    作为主体数据库的热备份(主体数据库的变化及时传到镜像数据库中)

    仅在故障转移后接受用户连接,事务处理请求。

        见证服务器(监视)

    监视服务器状态和连接性,实现自动自动故障转移

    1、master:用于记录所有SQL server系统级别的信息,比如说登录的信息,系统设置信息。如果他没了。你的SQL server就挂了。

“因为数据库正在使用,所以无法获得对数据库的独占访问权”,

 

三、数据库镜像会话

  会话初始化

    镜像请求事务日志记录,与主体服务器实现同步

  会话过程

    主体服务器将日志记录传输给镜像服务器

    各个角色之间相互监视会话状态

       会话终结

    发生故障转移

小鱼儿玄机30码姐妹,    管理员终止数据库镜像

    2、model:模型的意思,就像模板一样。创建数据库都是继承model。如果你在该数据库下创建一张表,以后每创建新数据库,就已经有了model的表。

我关闭了sql查询窗口,关了连接的客户端,关闭了浏览器上相关的访问网页,甚至关闭了IIS,然而,并没有什么用

一. DML触发器

四 数据库镜像三种模式

操作模式 事务安全 传输机制 是否需要仲裁 见证服务器 故障转移类型
高可用 Full 同步 Y Y 自动或手动
高级别保护 Full 同步 Y N 仅手动
高性能 OFF 异步 N N/A 仅强制

  高可用:要求高服务可用性, 要求实现自动故障转移,确保数据的完整。

  高级别保护模式: 数据完整性要求,不要求自动故障转移,对服务的可用性要求较低。

       高性能保护模式:主体服务器和镜像服务器距离很远,通讯链路有明显的延迟,对性能的事要求高于数据的完整性。

    3、msdb:和master一样记录信息,不同的是它记录任务调动,事件处理,数据备份及恢复,报警和异常的信息。

 

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

 五   配置演示

        环境: 数据库版本 sqlserver 2012  系统版本windows server 2008 R2 域管理

        mirroring测试场景

              主体服务器--FETCHINGDATA49MSSQLSERVERTWO   tcp 5022 端口

              镜像服务器--172.168.18.132MSSQLSERVER2012  tcp 5022 端口   

              见证服务器--FETCHINGDATA49 

            镜像操作模式: 高可用. 事务安全:Full,传输机制:同步,是否仲裁:Y,见证服务器:Y,故障转移:自动。

--步骤(1) 【主体服务器】设置为完整恢复模式,  做一次完整备份和日志备份
ALTER DATABASE Mirroring_Test SET  RECOVERY FULL 
backup database Mirroring_Test to disk='C:dataMirroring_Test.bak' with init
backup log  Mirroring_Test to disk='C:dataMirroring_Test.bak'  

  

--步骤(2) 【镜像服务器】还原到镜像库上(将备份文件复制到镜像服务器目录还原)
--运行下面语句,使之创建镜像数据库,处于正在还原状态并且是覆盖。
 restore database Mirroring_Test from disk='D:dataMirroring_Test.bak' 
 with file=1,
 move N'Mirroring_Test' To N'D:dataMirroring_Test.mdf',
  move N'Mirroring_Test_log' To N'D:dataMirroring_Test_log.ldf',
  norecovery, replace

 restore log Mirroring_Test  from disk='D:dataMirroring_Test.bak' with file=2,norecovery

    小鱼儿玄机30码姐妹 1

      在各服务器创建各端口

--步骤(3) 创建端点  【主体服务器】创建端点用于伙伴通讯,激活端点
create endpoint Mirroring_Test
as TCP (listener_port=5022)
for database_mirroring(role=partner,Encryption=supported)

-- 【镜像服务器】创建端点用于伙伴通讯,激活端点
create endpoint Mirroring_Test
as TCP (listener_port=5022)
for database_mirroring(role=partner,Encryption=supported)

--【见证服务器】创建端点用于见证通讯,激活端点
create endpoint Mirroring_Test
as TCP (listener_port=5023)
for database_mirroring(role=witness,Encryption=supported)

   小鱼儿玄机30码姐妹 2

     步骤4 创建connect(连接)权限。为三个数据库实例设置相同的账户名称和口令

           小鱼儿玄机30码姐妹 3

       小鱼儿玄机30码姐妹 4

        小鱼儿玄机30码姐妹 5

         小鱼儿玄机30码姐妹 6

         小鱼儿玄机30码姐妹 7

           步骤(5) 【主体服务器】配置镜像向导

           小鱼儿玄机30码姐妹 8

           小鱼儿玄机30码姐妹 9

             小鱼儿玄机30码姐妹 10

     

测试


 

--测试手动故障转移(在主体服务器上执行)

ALTER DATABASE Mirroring_Test SET PARTNER failover

--测试自动故障转移(如在主体数据库服务器切断网线,或停止实例)

--测试数据同步在主体服务器上改动数据,在镜像数据库上创建快照查询

CREATE DATABASE snap_Mirroring_Test

ON (NAME=Mirroring_Test,FILENAME='D:Snap_Mirroring_Test.snap')

   AS SNAPSHOT OF  Mirroring_Test

  

--删除快照

DROP DATABASE snap_Mirroring_Test

        

    4、tempdb:临时数据库,它为所有临时表,临时存储过程提供存储空间

百度到了一个解决方法,最终解决了问题

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

注意:以上四个系统数据库,别去删,删了有大大小小的问题。

还原前执行

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

数据类型:

ALTER DATABASE [datebase] SET OFFLINE WITH ROLLBACK IMMEDIATE

 

    1、数值型:

还原后再:

2. BEFORE/AFTER/INSTEAD OF

          整型:int  smallint  bigint  tinyint

ALTER  database  [ datebase]  set   online  

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

          浮点型:float  real

 

SQL Server DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

          货币型:money

OFFLINE和ONLINE是事务的处理机制。

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

    2、布尔型(bit):

前者是断开其他用户与数据库的连接,后者是开启其他用户和数据库的连接,

ORACLE DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

         True

 

         False

3. 触发条件

    3、二进制型

(1) 不能触发的情况

         binary

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

         varbinary

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

         image

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

    4、文本数据类型

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

        char    固定长度

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

        vachar  可变长度

exec sp_configure 'nested triggers'

        nvarchar

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

        nchar  

exec sp_configure 'nested triggers',0
RECONFIGURE

        text    长文本信息

但这个参数有两个另外:

        ntext   可变长度的长文本

  • 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

        datetime 日期和时间

 

新建数据库:

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

    1、数据库文件  :主数据文件和副数据文件

  • 直接递归:就是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次嵌套的上限;

    2、日志文件:一个或多个

总结下来:

    3、create database 名称

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

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

重命名数据库:exec sp_renamedb new,old

 

删除数据库:drop database 名称1,名称2

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

    create database test(数据库文件名)

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

    on

 

    (

本文由小鱼儿玄机30码发布于数据库,转载请注明出处:和master一样记录信息,所以无法获得对数据库的

关键词: 小鱼儿玄机30码