SQL SERAV4VE中华V TCRUISERIGGE大切诺基 触发器

1.触发器简单介绍

触发器是一种独特的囤积进度,它的试行不是由程序调用,亦非手动实行,而是由事件来触发。触发器是当对某多个表张开操作。比如:update、insert、delete那个操作的时候,系统会自动调用实施该表上相应的触发器。

触发器是一种特殊类别的蕴藏进度。触发器可含蓄复杂的T-SQL语句。触发器不可能透过名称被一贯调用,也分裂意设置参数。它是手无寸铁在接触事件上的。

2.触发器类型

1、DML( 数据垄断语言 Data Manipulation Language)触发器:是指触发器在数据库中生出 DML 事件时将启用。DML事件是指在表或视图中对数据开展的 insert、update、delete 操作的话语。

2、DDL(数据定义语言 Data Definition Language)触发器:是指当服务器或数据库中生出 DDL 事件时将启用。DDL事件是指在表或索引中的 create、alter、drop 操作语句。

3、登录触发器:是指当客商登入 SQL SEKugaVE索罗德实例建立会话时接触。倘诺身份验证失败,登入触发器不会接触。

个中 DML 触发器相比较常用,依照 DML 触发器触发的章程各异又分为以下两种情景:

after 触发器(之后触发):在那之中 after 触发器须要唯有实行insert、update、delete 某一操作之后触发器才会被触发,且不得不定义在表上。

instead of 触发器 (从前接触):instead of 触发器并不实行其定义的操作(insert、update、delete)而仅是推行触发器自己。能够在表或视图上定义 instead of 触发器。

 

3.触发器语法结构

AFTEENCORE 触发器语法:

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
AS { sql_statement  [ ; ] [ ,...n ] }  

<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ EXECUTE AS Clause ]

INSTEAD OF 触发器语法:

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  

<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  

<method_specifier> ::=  
    assembly_name.class_name.method_name

DDL 触发器语法:

CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  

<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

登入触发器语法:

CREATE [ OR ALTER ] TRIGGER trigger_name   
ON ALL SERVER   
[ WITH <logon_trigger_option> [ ,...n ] ]  
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  

<logon_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

 参数介绍:

  • CREATE OR ALTERAV4:成立或许有法规的改变触发器(即要修改的触发器必需已经存在)
  • schema_name:ML触发器所属的情势的称谓(即全部者,比方:dbo)。
  • trigger_name:触发器的名号
  • table | view:推行 DML 触发器的表或视图,有的时候称为触发器表或触发器视图。钦点表格或视图的一丝一毫限定名称是可选的。视图只可以由 INSTEAD OF 触发器援引
  • DATABASE:将 DDL 触发器的范围使用于当下数据库。假若指定,触发器会在方今数据库中产生event_type 或 event_group 时触发。
  • ALL SE科雷傲VEGL450:将 DDL 或登陆触发器的功效域应用于近些日子服务器。借使钦点,触发器会在时下服务器的别的地方产生event_type 或 event_group 时触发
  • WITH ENCLANDYPTION:加密 CREATE TXC60IGGE哈弗 语句的文本。使用 WITH ENC昂CoraYPTION 能够堤防触发器作为 SQL Server 复制的一某些进行业发布布。不可能为 CL奥德赛 触发器内定 WITH ENC路虎极光YPTION。
  • EXECUTE AS:钦定实施触发器的新余上下文。以便能够支配 SQL Server 实例用于注明触发器援用的另外数据库对象的权柄的客商帐户。
  • NATIVE_COMPILATION:表示触发器是本土编写翻译的。
  • SCHEMABINDING:钦定触发器引用的表不能够被剔除或更动。
  • FO奥迪Q5 | AFTERAV4:AFTE揽胜 内定仅在触发 SQL 语句中钦点的持有操作成功实行时触发 DML 触发器。全数援用级联操作和平契约束检查在这里触发器触发在此以前也非得成功。当 FO昂Cora 是钦命的独一首要字时,AFTEHighlander 是暗中同意值。视图不或者定义AFTEOdyssey触发器。
  • INSTEAD OF:钦点推行 DML 触发器并不是触发 SQL 语句,因而覆盖触发语句的操作。不只怕为 DDL 或登入触发器钦命 INSTEAD OF。对于 INSTEAD OF 触发器,在具有钦定级联合浮动作 ON DELETE 的援引关系的表上不允许行使 DELETE 选项。类似地,在富有钦赐级联合浮动作 ON UPDATE 的援引关系的表上,不一致敬 UPDATE 选项。
  • {[DELETE] [,] [INSERT] [,] [UPDATE]} :钦点在针对此表或视图进行尝试时激活 DML 触发器的数码修改语句。必需起码钦命多个选项。在触发器定义中允许以其余顺序对这么些接纳举办放肆组合。
  • event_type:是实践后引致 DDL 触发器触发的 Transact-SQL 语言事件的名号。
  • event_group:是 Transact-SQL 语言事件的预约义分组的称谓。属于别的Transact-SQL 语言事件实行后的 DDL 触发器触发 event_group。
  • sql_statement:是接触条件和动作。触发条件内定附加条件,以明确尝试的 DML,DDL 或登入事件是否形成实施触发器操作。
  • method_specifier:对于 CL福睿斯触发器,钦定要与触发器绑定的主次集的艺术。该办法不得不引用任何参数并返回void。class_name 必须是实惠的 SQL Server 标志符,並且必需作为有着程序集可以知道性的主次聚集的类存在。

触发器能够强制实践一定的工作准绳,以保持数据完整性、检查数占领效性、达成数据库管理职责和局地外加功能。

DML触发器例子

  1. 始建AFTE奥德赛 INSERT 触发器:当下单后更新tbOrderTotalPrice 的总结消息

预备一张订单表(tbOrder),包罗订单ID,订单交易总额,订单创立时间,准备订单交易支行时间总额(tbOrderTotal普赖斯 ),用来计算各样月的交易合计金额

图片 1图片 2

USE [TEST]
GO

/****** Object:  Trigger [dbo].[tSumTotalOrderPrice]    Script Date: 2018/5/15 11:10:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tSumTotalOrderPrice] on [dbo].[tbOrder]
AFTER INSERT
AS
BEGIN
 DECLARE @DT INT
 SET @DT= CONVERT(varchar(6),GETDATE(),112)

 DECLARE @SUM DECIMAL(18, 2)
 SET @SUM=(SELECT SUM(OPrice) FROM tbOrder WHERE @DT = CONVERT(varchar(6),CreateDT,112))

 IF(EXISTS(SELECT * FROM tbOrderTotalPrice WHERE YearMoth=@DT))
 BEGIN
  UPDATE tbOrderTotalPrice SET TotalPrice=@SUM WHERE YearMoth=@DT
 END
 ELSE
 BEGIN
  INSERT INTO tbOrderTotalPrice VALUES (@DT,@SUM)
 END
END

GO

执行INSERT 行为:INSERT INTO [dbo].[tbOrder]([OPrice],[CreateDT])VALUES(9,GETDATE())

图片 3

图片 4

 2.成立 instead of  insert 触发器,当增添顾客战表记录时候,验证学号和科目存在技巧张开增加操作

预备学生表(tbStudent),学科表(tbSubject),成绩表(tbStuSubScore)

图片 5 图片 6 

图片 7

在学员表中增多测量检验学生,在课程专门的学业拉长测量检验科目

图片 8 图片 9

新扩展instead of insert 触发器,在实践INSERT 语句前验证学号和学科存在本事开展加多操作

USE [TEST]
GO

/****** Object:  Trigger [dbo].[tCheckStuSubScore]    Script Date: 2018/5/17 14:20:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  yangyi
-- Create date: 2018/5/17
-- Description: 新增学科分数
-- =============================================
CREATE TRIGGER [dbo].[tCheckStuSubScore] ON [dbo].[tbStuSubScore]
   INSTEAD OF INSERT
AS 
BEGIN
 --验证学号是否存在
 IF(NOT EXISTS(SELECT * FROM tbStudent WHERE StuID=(SELECT StuID FROM inserted)))
 BEGIN
  PRINT('验证学号不否存在')
  ROLLBACK TRANSACTION
 END
 ELSE
 BEGIN
  --验证学科是否存在
  IF(NOT EXISTS(SELECT * FROM tbSubject WHERE SubID=(SELECT SubID FROM inserted)))
  BEGIN
   PRINT('验证学科不否存在')
   ROLLBACK TRANSACTION
  END
  ELSE
  BEGIN
   --执行INSERT
   INSERT INTO tbStuSubScore SELECT * FROM inserted
  END
 END
END

GO

注意:

在触发器语句中用五个自笔者作古的表一个是deleted表和inserted。它们是透过触发器操作自动创立驻留在内部存款和储蓄器中的临时表。
Deleted表用于存款和储蓄 DELETE和 UPDATE语句所影响的行的别本。在实行DELETE或 UPDATE语句时,行从触发器表中删除,并传导到 deleted表中。Deleted表和触发器表平常未有一样的行。
Inserted 表用于存款和储蓄 INSERT 和 UPDATE 语句所影响的行的别本。在七个插入或更新事务管理中,新兴业银行被相同的时候增多到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本

1.安顿操作(Insert) Inserted表有多少,Deleted表无多少
2.剔除操作(Delete) Inserted表无数据,Deleted表有多少
3.翻新操作(Update) Inserted表有多少(新数据),Deleted表有数据(旧数据)

测试:

1>.实施INSERT 加多一条学科不存不日常在的学员战绩记录,实行破产

INSERT INTO [dbo].[tbStuSubScore]([StuID],[Score],[SubID],[CreateDT])VALUES('001',100,0,GETDATE())  

2.>实践INSERT 增加一条学号不存不健康在的上学的小孩子战绩记录,实施停业

INSERT INTO [dbo].[tbStuSubScore]([StuID],[Score],[SubID],[CreateDT])VALUES('004',100,1,GETDATE())

图片 10 图片 11

3.>实施一条常规的学员成绩记录,实行成功

INSERT INTO [dbo].[tbStuSubScore]([StuID],[Score],[SubID],[CreateDT])VALUES('001',100,1,GETDATE())

图片 12

 

触发器的分类: DML、 DDL、 登入触发器

 

 

创设触发器供给钦命的选项:

 

 1.触发器的名目。

 2.在其上定义触发器的表。

 3.触发器将几时激发。

 4.激活触发器的多寡修改语句。

 5.推行触发操作的编制程序语句。

 

 

CREATE T大切诺基IGGE陆风X8语句基本语法格式如下:

 

CREATE TTiguanIGGER     触发器名称

ON {表名 | 视图名}

[with encryption]

{

   { {FOR | AFTER | INSTEAD OF}

   {[DELETE] [,][INSERT] [,] [UPDATE]}

   AS

   sql_statement […n ]

 

其中:

AFTER

点名触发器唯有在触发 SQL 语句中钦命的持有操作都已成功实践后才激起。全数的引用级联操作和束缚检查也无法不成功做到后,手艺实行此触发器。若是仅钦定FOENVISION 关键字,则 AFTE凯雷德 是默许设置。

INSTEAD OF

点名施行触发器实际不是实施触发 SQL 语句,进而代替触发语句的操作。

 

例如:

 1 /*在student表上创建触发器,
 2 在用户插入、修改和删除记录时,都会自动显示表中的内容:*/
 3 
 4 use test
 5 go
 6 
 7 create trigger trig_1 on student
 8 after insert,delete,update
 9 as 
10 begin
11     set nocount on
12     select * from student
13 end
14 
15 insert student(sno) values(5)
16 
17 delete student where sno=5
18 
19 
20 exec sp_helptext trig_1    --查看触发器内容 
21 exec sp_helptrigger student   --查看表上的触发器的属性
22 select * from sysobjects where xtype='TR'  --查看数据库中已有的触发器
23 
24 
25 drop trigger trig_1

 

 

inserted表和deleted表

 

触发器实行的时候,发生多个偶然表:inserted表和deleted表。它们的构造和所在的表的构造同样,可应用那多个表测量试验某个数据修改的效应和设置触发器操作的基准,但不可能对表中的多寡进行改动。

deleted表用于存款和储蓄DELETE和UPDATE语句所影响的行的副本。在施行delete或update语句时,行从触发器表中删除,并传导到deleted表中。

inserted表用于存款和储蓄INSERT和UPDATE语句所影响的行的别本。在插入和更新时,新兴业银行被同不常间加上到inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。

 

在对全数触发器的表(触发器表)举办操作时,有:

试行INSERT操作,插入到触发器表中的新行被插入到inserted表中。

实行DELETE操作,从触发器表中删除的行被插入到deleted表中。

实施UPDATE操作,先从触发器表中删除旧行,然后再插入新行。删除的旧行插入到deleted表中;改动后的新行被插入到inserted 表中。

 

 

 

使用DML触发器

 

1. INSERT和UPDATE触发器

     当向表中插入恐怕更新记录时,INSERT恐怕UPDATE触发器被激活。平时情状下,那二种触发器常用来检查插入恐怕涂改后的数量是或不是满意须要。 

INSERT触发器被触发时,新的笔录增至触发器的呼应表中,何况同不时间也助长到一个inserted表中。

修改三个记录等于插入了叁个新的笔录同临时间删除贰个旧的笔录。当在三个有UPDATE触发器的表中期维修改记录时,表中原来的笔录被移位到deleted表中,修改过的笔录插入到了插入表中,触发器能够参照deleted表和inserted表以至被涂改的表,以明确哪些成功数据库操作。

 

2. DELETE触发器

DELETE触发器经常用于上面包车型地铁图景:

幸免这一个实在要刨除,但是大概会孳生数据一致性难点的事态,平常是用以那一个用作其余表的表面键记录。

用以级联删除操作。

 

例如:

 1 /*例:下例说明inserted表和deleted表的作用*/
 2 
 3 if exists(select name from sysobjects where name='trig_2' and type='TR')
 4     drop trigger trig_2
 5 go
 6 
 7 create trigger trig_2 
 8 on student
 9 after update                         --update触发器
10 as 
11     print 'inserted表'
12     select * from inserted
13     print 'deleted表'
14     select * from deleted
15 go
16 set nocount on
17 update student set sname='关二' where sno=2
18 --drop trigger trig_2
19 
20 
21 create trigger trig_3 
22 on student
23 after insert                         --insert触发器
24 as 
25     print 'inserted表'
26     select * from inserted
27     print 'deleted表'
28     select * from deleted
29 go
30 insert student values(100,'刘一百','男',25)
31 drop trigger trig_3
32 
33 
34 create trigger trig_4 
35 on student
36 after delete                         --delete触发器
37 as 
38     print 'inserted表'
39     select * from inserted
40     print 'deleted表'
41     select * from deleted
42 go
43 delete student where sno=100
44 drop trigger trig_4

 

 

修改触发器

 

语法格式:

ALTER TRIGGER trigger_name

ON ( table | view )

{

  { ( FOR | AFTER | INSTEAD OF ) }

  { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }

AS

  sql_statement […n ]

  }   

 

DDL触发器使用

 

例如:

 1 /*DDL触发器*/
 2 
 3 /*在test数据库上创建一个DDL触发器safe,
 4 用来防止数据库中的任一表被修改或删除。*/
 5 
 6 create trigger safetest
 7 on database                         --数据库DDL触发器
 8 after drop_table,alter_table
 9 as
10 begin
11     raiserror('不能修改表结构',16,2)
12     rollback
13 end
14 go
15  
16 --执行以下程序,观察结果
17 alter table student add nation char(10)
18 
19 disable trigger safetest on database
20 drop trigger safetest on database
21 ---------------------------------------------------------
22 
23 
24 /*在服务器上创建一个DDL触发器tablecreat,
25 用来防止在服务器上创建数据库*/
26 
27 create trigger trig_last
28 on all server
29 after create_database
30 as
31 begin
32     raiserror('不能创建新的数据库',16,2)
33     rollback
34 end
35 go
36 
37 --执行以下程序,观察结果
38 create database test_trig
39 
40 disable trigger trig_last on all server
41 drop trigger trig_last on all server

 

删去触发器

 

行使SQL Server Management Studio删除触发器

选取DROP TOdysseyIGGE大切诺基语句来删除触发器。其语法格式如下:

DROP TRIGGER { trigger } [ , …n ]

 

触发器禁用和启用

 

例如:

 1 /*触发器禁用和启用*/
 2 
 3 /*禁用sc表上的触发器trig_g。*/
 4 alter table sc disable trigger trig_g
 5 disable trigger trig_g on sc
 6 go
 7 
 8 /*启用sc表上的触发器trig_g。*/
 9 alter table sc enable trigger trig_g
10 enable trigger trig_g on sc
11 go
12 
13 --执行以下程序,观察结果
14 insert into sc values(1,100,1,-50)
15 select * from sc

 

 

触发器械体运用

 

例如:

 1 /*具体应用*/
 2 
 3 /*创建触发器trig3,
 4 当删除student表中的学生记录时,
 5 应该同时删除sc表中对应的记录*/
 6 
 7 create trigger trig_5
 8 on student
 9 for delete
10 as 
11  delete sc
12  where sc.sno in(select sno from deleted)
13 go
14 
15 select * from student
16 select * from sc
17 go
18 
19 /*exec sp_help score   --查看其中外键
20 
21 alter table score         --删除外键
22 drop CONSTRAINT FK_score_course
23 
24 alter table score
25 drop CONSTRAINT FK_score_student*/
26 
27 
28 delete student where sno=3
29 go
30 
31 select * from student
32 select * from sc 
33 -------------------------------------------------------------
34 
35 
36 create trigger trig_g 
37 on sc
38 after insert,update
39 as
40 begin
41     declare @g int
42     select @g=grade from inserted
43     if @g<0
44     begin
45          select '成绩必须>=0'
46          rollback
47     end
48 end
49 go
50 
51 --执行以下程序,观察结果
52 insert into sc values(1,10,1,50)
53 select * from sc
54 
55 
56 /*例: 建立一个修改触发器trigno,
57 该触发器防止用户修改表student的学号*/
58 
59 create trigger trigno
60 on student
61 after update
62 as
63 if update(sno)
64     begin
65         raiserror('不能修改学号',16,2)
66         rollback
67     end
68 go
69 
70 --执行以下程序,观察结果
71 update student set sno='2' where sno='1'
72 select * from student
73 ------------------------------------------------------
74 
75 
76 /*INSTEAD OF触发器*/
77 
78 /*例:在student表上创建一个INSTEAD OF触发器trig_6,
79 当用户插入数据时注意观察触发器的执行。*/
80 
81 create trigger trig_6
82 on student
83 instead of insert
84 as
85     select * from student
86 go
87 
88 --执行以下程序,观察结果
89 insert into student(sno,sname) values('300','白扯')

 

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:SQL SERAV4VE中华V TCRUISERIGGE大切诺基 触发器

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。