SQLServer INSTEAD OF 触发器的工作原理、创建语法、使用示例、优缺点
INSTEAD OF触发器工作原理
INSTEAD OF表示并不执行其所定义的操作INSERT,UPDATE ,DELETE,而仅是执行触发器本身,即当对表进行INSERT、UPDATE 或 DELETE 操作时,系统不是直接对表执行这些操作,而是把操作内容交给触发器,让触发器检查所进行的操作是否正确,如正确才进行相应的操作。因此,INSTEAD OF 触发器的动作要早于表的约束处理。
既可以在表上定义INSTEAD OF触发器,也可以在视图上定义。
传递给为表定义的 INSTEAD OF 触发器的插入的和删除的表与传递给 AFTER 触发器的插入的和删除的表遵守相同的规则。 插入的和删除的表的格式与在其上定义 INSTEAD OF 触发器的表的格式相同。 插入的和删除的表中的每一列都直接映射到基表中的列。
以下是关于引用带 INSTEAD OF 触发器的表的 INSERT 或 UPDATE 语句何时必须提供列值的规则,当引用的表不带 INSTEAD OF 触发器时也一样:
-
不能为计算列或具有 timestamp
数据类型的列指定值。
-
不能为具有 IDENTITY 属性的列指定值,除非该表的 IDENTITY_INSERT 为 ON。 当 IDENTITY_INSERT 为 ON 时,INSERT 语句必须提供一个值。
-
INSERT 语句必须为所有无 DEFAULT 约束的 NOT NULL 列提供值。
-
对于除计算,identity 之外的任何列或timestamp
列值都是可选的任何允许空值的列或列具有 DEFAULT 定义的 NOT NULL。
当 INSERT、UPDATE 或 DELETE 语句引用具有 INSTEAD OF 触发器的视图时, 数据库引擎 将调用该触发器,而不是对任何表采取任何直接操作。 即使插入的和删除的表中为该视图生成的信息格式不同于基表中的数据格式,触发器也必须使用插入的和删除的表中的信息来生成实现基表中请求的操作所需的任何语句。
INSTEAD OF触发器创建
语法:
--声明数据库应用。
use 数据库名;
go
--判断是否已存在触发器,如果已存在则删除。
if exists(select * from sysobjects where name=触发器名)
drop trigger 触发器名;
go
--执行创建触发器
create
--触发器关键字
trigger
--DML 触发器所属架构的名称。 DML 触发器的作用域是为其创建该触发器的表或视图的架构。 不能为 DDL 或登录触发器指定
--[dbo.]
--触发器名称
[架构名.]{ 表名 | 视图名}
on
--对其执行 DML 触发器的表或视图,有时称为触发器表或触发器视图。 可以根据需要指定表或视图的完全限定名称。 视图只能被 INSTEAD OF 触发器引用。 不能对局部或全局临时表定义 DML 触发器。
[架构名.]触发器名
with
--对CREATE TRIGGER 语句的文本进行模糊处理。使用WITH ENCRYPTION可以防止将触发器作为SQL Server复制的一部分进行发布。不能为 CLR 触发器指定 WITH ENCRYPTION。(指定此选项将为触发器加密)
[encryption][,]
--指示触发器已本机编译。 (只能应用于table)
--内存优化表上的触发器需要使用此选项。
--[native_compilation][,]
--确保不能删除或更改触发器引用的表。(只能应用于table)
--内存优化表上的触发器需要使用此选项,但此选项不支持传统表上的触发器。
[schemabinding][,]
--EXECUTE AS (后面可以跟函数,存储过程等)
--指定用于执行该触发器的安全上下文。 允许您控制 SQL Server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。
--内存优化表上的触发器需要使用此选项。
--[execute as clause]
--指定执行 DML 触发器而不是触发 SQL 语句,因此,其优先级高于触发语句的操作。 不能为 DDL 或登录触发器指定 INSTEAD OF。
--对于表或视图,每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。 但是,可以为具有自己的 INSTEAD OF 触发器的多个视图定义视图。
--INSTEAD OF 触发器不可以用于使用 WITH CHECK OPTION 的可更新视图。 如果将 INSTEAD OF 触发器添加到指定了 WITH CHECK OPTION 的可更新视图中,则 SQL Server 将引发错误。 用户须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。
instead of
--指定数据修改语句,这些语句可在 DML 触发器对此表或视图进行尝试时激活该触发器。 必须至少指定一个选项。 在触发器定义中允许使用上述选项的任意顺序组合。
--对于 INSTEAD OF 触发器,不允许对具有指定级联操作 ON DELETE 的引用关系的表使用 DELETE 选项。 同样,也不允许对具有指定级联操作 ON UPDATE 的引用关系的表使用 UPDATE 选项。
{ [insert] [,] [update] [,] [delete] }
--指定应该再添加一个现有类型的触发器。 WITH APPEND 不能与 INSTEAD OF 触发器一起使用。如果显式声明了 AFTER 触发器,则也不能使用该子句。
--仅当为了向后兼容而指定了 FOR 时(但没有 INSTEAD OF 或 AFTER)时,才能使用 WITH APPEND。 如果指定了 EXTERNAL NAME(即触发器为 CLR 触发器),则不能指定 WITH APPEND。
--with append
--指示当复制代理修改涉及到触发器的表时,不应执行触发器。
--not for replication
as
begin
sql_statement
end
go
示例:
传递给为表定义的 INSTEAD OF 触发器的插入的和删除的表与传递给 AFTER 触发器的插入的和删除的表遵守相同的规则。 插入的和删除的表的格式与在其上定义 INSTEAD OF 触发器的表的格式相同。 插入的和删除的表中的每一列都直接映射到基表中的列。
以下是关于引用带 INSTEAD OF 触发器的表的 INSERT 或 UPDATE 语句何时必须提供列值的规则,当引用的表不带 INSTEAD OF 触发器时也一样:
-
不能为计算列或具有
timestamp
数据类型的列指定值。 -
不能为具有 IDENTITY 属性的列指定值,除非该表的 IDENTITY_INSERT 为 ON。 当 IDENTITY_INSERT 为 ON 时,INSERT 语句必须提供一个值。
-
INSERT 语句必须为所有无 DEFAULT 约束的 NOT NULL 列提供值。
-
对于除计算,identity 之外的任何列或
timestamp
列值都是可选的任何允许空值的列或列具有 DEFAULT 定义的 NOT NULL。当 INSERT、UPDATE 或 DELETE 语句引用具有 INSTEAD OF 触发器的视图时, 数据库引擎 将调用该触发器,而不是对任何表采取任何直接操作。 即使插入的和删除的表中为该视图生成的信息格式不同于基表中的数据格式,触发器也必须使用插入的和删除的表中的信息来生成实现基表中请求的操作所需的任何语句。
INSTEAD OF触发器创建
语法:
--声明数据库应用。
use 数据库名;
go
--判断是否已存在触发器,如果已存在则删除。
if exists(select * from sysobjects where name=触发器名)
drop trigger 触发器名;
go
--执行创建触发器
create
--触发器关键字
trigger
--DML 触发器所属架构的名称。 DML 触发器的作用域是为其创建该触发器的表或视图的架构。 不能为 DDL 或登录触发器指定
--[dbo.]
--触发器名称
[架构名.]{ 表名 | 视图名}
on
--对其执行 DML 触发器的表或视图,有时称为触发器表或触发器视图。 可以根据需要指定表或视图的完全限定名称。 视图只能被 INSTEAD OF 触发器引用。 不能对局部或全局临时表定义 DML 触发器。
[架构名.]触发器名
with
--对CREATE TRIGGER 语句的文本进行模糊处理。使用WITH ENCRYPTION可以防止将触发器作为SQL Server复制的一部分进行发布。不能为 CLR 触发器指定 WITH ENCRYPTION。(指定此选项将为触发器加密)
[encryption][,]
--指示触发器已本机编译。 (只能应用于table)
--内存优化表上的触发器需要使用此选项。
--[native_compilation][,]
--确保不能删除或更改触发器引用的表。(只能应用于table)
--内存优化表上的触发器需要使用此选项,但此选项不支持传统表上的触发器。
[schemabinding][,]
--EXECUTE AS (后面可以跟函数,存储过程等)
--指定用于执行该触发器的安全上下文。 允许您控制 SQL Server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。
--内存优化表上的触发器需要使用此选项。
--[execute as clause]
--指定执行 DML 触发器而不是触发 SQL 语句,因此,其优先级高于触发语句的操作。 不能为 DDL 或登录触发器指定 INSTEAD OF。
--对于表或视图,每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。 但是,可以为具有自己的 INSTEAD OF 触发器的多个视图定义视图。
--INSTEAD OF 触发器不可以用于使用 WITH CHECK OPTION 的可更新视图。 如果将 INSTEAD OF 触发器添加到指定了 WITH CHECK OPTION 的可更新视图中,则 SQL Server 将引发错误。 用户须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。
instead of
--指定数据修改语句,这些语句可在 DML 触发器对此表或视图进行尝试时激活该触发器。 必须至少指定一个选项。 在触发器定义中允许使用上述选项的任意顺序组合。
--对于 INSTEAD OF 触发器,不允许对具有指定级联操作 ON DELETE 的引用关系的表使用 DELETE 选项。 同样,也不允许对具有指定级联操作 ON UPDATE 的引用关系的表使用 UPDATE 选项。
{ [insert] [,] [update] [,] [delete] }
--指定应该再添加一个现有类型的触发器。 WITH APPEND 不能与 INSTEAD OF 触发器一起使用。如果显式声明了 AFTER 触发器,则也不能使用该子句。
--仅当为了向后兼容而指定了 FOR 时(但没有 INSTEAD OF 或 AFTER)时,才能使用 WITH APPEND。 如果指定了 EXTERNAL NAME(即触发器为 CLR 触发器),则不能指定 WITH APPEND。
--with append
--指示当复制代理修改涉及到触发器的表时,不应执行触发器。
--not for replication
as
begin
sql_statement
end
go
--声明数据库应用。
use testss;
go
--判断是否已存在触发器,如果已存在则删除。
if exists(select * from sysobjects where name='insteadoftri')
drop trigger insteadoftri;
go
--执行创建触发器
create
--触发器关键字
trigger
--DML 触发器所属架构的名称。 DML 触发器的作用域是为其创建该触发器的表或视图的架构。 不能为 DDL 或登录触发器指定
--[dbo.]
--触发器名称
dbo.insteadoftri
on
--对其执行 DML 触发器的表或视图,有时称为触发器表或触发器视图。 可以根据需要指定表或视图的完全限定名称。 视图只能被 INSTEAD OF 触发器引用。 不能对局部或全局临时表定义 DML 触发器。
dbo.test1
with
--对CREATE TRIGGER 语句的文本进行模糊处理。使用WITH ENCRYPTION可以防止将触发器作为SQL Server复制的一部分进行发布。不能为 CLR 触发器指定 WITH ENCRYPTION。(指定此选项将为触发器加密)
encryption,
--指示触发器已本机编译。 (只能应用于table)
--内存优化表上的触发器需要使用此选项。
--[native_compilation][,]
--确保不能删除或更改触发器引用的表。(只能应用于table)
--内存优化表上的触发器需要使用此选项,但此选项不支持传统表上的触发器。
schemabinding
--EXECUTE AS (后面可以跟函数,存储过程等)
--指定用于执行该触发器的安全上下文。 允许您控制 SQL Server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。
--内存优化表上的触发器需要使用此选项。
--[execute as clause]
--指定执行 DML 触发器而不是触发 SQL 语句,因此,其优先级高于触发语句的操作。 不能为 DDL 或登录触发器指定 INSTEAD OF。
--对于表或视图,每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。 但是,可以为具有自己的 INSTEAD OF 触发器的多个视图定义视图。
--INSTEAD OF 触发器不可以用于使用 WITH CHECK OPTION 的可更新视图。 如果将 INSTEAD OF 触发器添加到指定了 WITH CHECK OPTION 的可更新视图中,则 SQL Server 将引发错误。 用户须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。
instead of
--指定数据修改语句,这些语句可在 DML 触发器对此表或视图进行尝试时激活该触发器。 必须至少指定一个选项。 在触发器定义中允许使用上述选项的任意顺序组合。
--对于 INSTEAD OF 触发器,不允许对具有指定级联操作 ON DELETE 的引用关系的表使用 DELETE 选项。 同样,也不允许对具有指定级联操作 ON UPDATE 的引用关系的表使用 UPDATE 选项。
insert,update,delete
--指定应该再添加一个现有类型的触发器。 WITH APPEND 不能与 INSTEAD OF 触发器一起使用。如果显式声明了 AFTER 触发器,则也不能使用该子句。
--仅当为了向后兼容而指定了 FOR 时(但没有 INSTEAD OF 或 AFTER)时,才能使用 WITH APPEND。 如果指定了 EXTERNAL NAME(即触发器为 CLR 触发器),则不能指定 WITH APPEND。
--with append
--指示当复制代理修改涉及到触发器的表时,不应执行触发器。
not for replication
as
begin
if (select count(1) from inserted) = 1 and (select count(1) from deleted) = 0
begin
print('insert');
insert into dbo.test1(name,sex,age,classid,height,xml1,xml2)
select top 1 name,sex,age,classid,height,xml1,xml2 from inserted order by id desc;
end
else if (select count(1) from deleted) = 1 and (select count(1) from inserted) = 0
begin
print('delete');
delete from dbo.test1 where id=(select top 1 id from deleted order by id desc);
end
else
begin
print('update');
update dbo.test1 set name=inserted.name,sex=inserted.sex,age=inserted.age,classid=inserted.classid,height=inserted.height,
xml1=inserted.xml1,xml2=inserted.xml2
from inserted
where dbo.test1.id=(select top 1 id from deleted order by id desc);
end
end
go
示例结果:
INSTEAD OF触发器优缺点
优点:
1、可以实现在对数据操作之前更严格的检查和校验。
2、可定义在表上,也可以定义在视图上,同一操作只能定义一个。
3、指定执行 DML 触发器而不是触发 SQL 语句,因此,其优先级高于触发语句的操作。
缺点:
1、可移植性差。
2、占用服务器资源,给服务器造成压力。
3、执行速度主要取决于数据库服务器的性能与触发器代码的复杂程度。
4、触发器会使编程时源码的结构*打乱,为将程序修改、源码阅读带来困难。
推荐阅读
-
go语言Socket编程-Socket编程 什么是Socket Socket,英文含义是插座、插孔,一般称之为套接字,用于描述IP地址和端口。可以实现不同程序间的数据通信。 Socket起源于Unix,而Unix基本哲学之一就是“一切皆文件”,都可以用“打开open –> 读写write/read –> 关闭close”模式来操作。Socket就是该模式的一个实现,网络的Socket数据传输是一种特殊的I/O,Socket也是一种文件描述符。Socket也具有一个类似于打开文件的函数调用:Socket,该函数返回一个整型的Socket描述符,随后的连接建立、数据传输等操作都是通过该Socket实现的。 套接字的内核实现较为复杂,不宜在学习初期深入学习,了解到如下结构足矣。 套接字通讯原理示意 在TCP/IP协议中,“IP地址+TCP或UDP端口号”唯一标识网络通讯中的一个进程。“IP地址+端口号”就对应一个socket。欲建立连接的两个进程各自有一个socket来标识,那么这两个socket组成的socket pair就唯一标识一个连接。因此可以用Socket来描述网络连接的一对一关系。 常用的Socket类型有两种:流式Socket(SOCK_STREAM)和数据报式Socket(SOCK_DGRAM)。流式是一种面向连接的Socket,针对于面向连接的TCP服务应用;数据报式Socket是一种无连接的Socket,对应于无连接的UDP服务应用。 网络应用程序设计模式 C/S模式 传统的网络应用设计模式,客户机(client)/服务器(server)模式。需要在通讯两端各自部署客户机和服务器来完成数据通信。 B/S模式 浏览器(Browser)/服务器(Server)模式。只需在一端部署服务器,而另外一端使用每台PC都默认配置的浏览器即可完成数据的传输。 优缺点 对于C/S模式来说,其优点明显。客户端位于目标主机上可以保证性能,将数据缓存至客户端本地,从而提高数据传输效率。且,一般来说客户端和服务器程序由一个开发团队创作,所以他们之间所采用的协议相对灵活。可以在标准协议的基础上根据需求裁剪及定制。例如,腾讯所采用的通信协议,即为ftp协议的修改剪裁版。 因此,传统的网络应用程序及较大型的网络应用程序都首选C/S模式进行开发。如,知名的网络游戏魔兽世界。3D画面,数据量庞大,使用C/S模式可以提前在本地进行大量数据的缓存处理,从而提高观感。 C/S模式的缺点也较突出。由于客户端和服务器都需要有一个开发团队来完成开发。工作量将成倍提升,开发周期较长。另外,从用户角度出发,需要将客户端安插至用户主机上,对用户主机的安全性构成威胁。这也是很多用户不愿使用C/S模式应用程序的重要原因。 B/S模式相比C/S模式而言,由于它没有独立的客户端,使用标准浏览器作为客户端,其工作开发量较小。只需开发服务器端即可。另外由于其采用浏览器显示数据,因此移植性非常好,不受平台限制。如早期的偷菜游戏,在各个平台上都可以完美运行。 B/S模式的缺点也较明显。由于使用第三方浏览器,因此网络应用支持受限。另外,没有客户端放到对方主机上,缓存数据不尽如人意,从而传输数据量受到限制。应用的观感大打折扣。第三,必须与浏览器一样,采用标准http协议进行通信,协议选择不灵活。 因此在开发过程中,模式的选择由上述各自的特点决定。根据实际需求选择应用程序设计模式。 简单的C/S模型通信 Server端:Listen函数 func Listen(network, address string) (Listener, error) network:选用的协议:TCP、UDP, 如:“tcp”或 “udp” address:IP地址+端口号, 如:“127.0.0.1:8000”或 “:8000” Listener 接口: type Listener interface { Accept (Conn, error) Close error Addr Addr } Conn 接口: type Conn interface { Read(b byte) (n int, err error) Write(b byte) (n int, err error) Close error LocalAddr Addr RemoteAddr Addr SetDeadline(t time.Time) error SetReadDeadline(t time.Time) error SetWriteDeadline(t time.Time) error } 参看 [<u>https://studygolang.com/pkgdoc</u>](https://studygolang.com/pkgdoc) 中文帮助文档中的demo: 示例代码:TCP服务器.go package main import ( "net" "fmt" ) func main { // 创建监听 listener, err:= net.Listen("tcp", ":8000") if err != nil { fmt.Println("listen err:", err) return } defer listener.Close // 主协程结束时,关闭listener fmt.Println("服务器等待客户端建立连接...") // 等待客户端连接请求 conn, err := listener.Accept if err != nil { fmt.Println("accept err:", err) return } defer conn.Close // 使用结束,断开与客户端链接 fmt.Println("客户端与服务器连接建立成功...") // 接收客户端数据 buf := make(byte, 1024) // 创建1024大小的缓冲区,用于read n, err := conn.Read(buf) if err != nil { fmt.Println("read err:", err) return } fmt.Println("服务器读到:", string(buf[:n])) // 读多少,打印多少。 }
-
SQLServer INSTEAD OF 触发器的工作原理、创建语法、使用示例、优缺点