未加星标

T-SQL语句(六)――触发器操作 SQL SQL语句 触发器操作

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏
T-SQL 语句(六)—— 触发器操作
一、触发器简介
1、触发器的功能:
  a、实现比约束更为复杂的数据约束
  b、可以检查 SQL 所做的操作是否被允许
  c、修改其他数据库里表的数据
  d、可以一次调用多个存储过程
  e、发送SQL Mail
  f、返回自定义错误信息
  g、修改原来要操作的 SQL 语句
  h、防止数据表结构更改或表被删除
 2、触发器的种类主要分为DML 触发器和DDL 触发器两种,其中DML触发器又分为After 和 Instead Of 触发器
二、创建 DML 触发器
-- 一个表针对每个操作可以定义多个After 触发器,但是Instead Of触发器对每个操作只能建立一个
CREATE TRIGGER trigger_insert ON news AFTER INSERT AS PRINT '插入了一条记录'
GO
CREATE TRIGGER trigger_update ON news AFTER UPDATE AS PRINT '修改了一条记录'
GO
CREATE TRIGGER trigger_delete ON news AFTER DELETE AS PRINT '删除了一条记录'
GO
CREATE TRIGGER trigger_change
ON [order]
AFTER INSERT,UPDATE,DELETE
AS
EXEC master..xp_sendmail 'TOM','订单有更改'
GO
CREATE TRIGGER trigger_order_insert
ON [order]
AFTER INSERT,UPDATE
AS
IF (SELECT discount from INSERTED) > 0.6
BEGIN
PRINT '折扣不能大于 0.6'
ROLLBACK TRANSACTION
END
GO
三、触发器排序

使用 sp_settriggerorder 存储过程进行触发器排序,其参数均为字符串,参数一是触发器名称,参数二是激活触发器的顺序参数,可以为 FIRST,LAST和NONE,参数三是激活触发器的动作,可以是 INSERT,UPDATE 和 DELETE

CREATE TRIGGER trigger_update_age_employee
ON employee
AFTER UPDATE
AS PRINT '更新员工年龄'
GO
CREATE TRIGGER trigger_update_name_employee
ON employee
AFTER UPDATE
AS PRINT '更新员工姓名'
GO
UPDATE employee SET age = 10 WHERE id=1 -- 进行观察
EXEC sp_settriggerorder
'trigger_update_age_employee','LAST',UPDATE
GO
UPDATE employee SET age = 10 WHERE id=11 -- 触发器执行的顺序改变了
四、Instead Of 触发器
  After 触发器只能在数据表中使用,而 Instead Of 触发器可以在数据表和视图中使用。以下情况建议使用 Instead Of 触发器:
  1、数据库的数据禁止修改,可以使用 Instead Of 触发器来跳过 Update 修改记录的 SQL 语句
  2、有可能需要回滚修改的 SQL 语句,Instead Of 触发器的回滚效率比 After 高。
  3、在视图中使用触发器
  4、用自己的方式修改数据,使用 Instead Of 触发器来控制数据的修改方式和流程。
CREATE TRIGGER trigger_insert_employee
 ON employee
 INSTEAD OF INSERT
AS PRINT 'employee 表插入数据'
GO  
EXEC sp_help 'trigger_insert_employee' -- 查看触发器信息
EXEC sp_helptext 'trigger_insert_employee' -- 查看触发器SQL文本
五、修改 DML 触发器

  修改触发器的语法与修改存储过程语法类似,只是将 CREATE 改变成 ALTER。触发器重命名使用 sp_name 存储过程

六、删除 DML 触发器
DROP TRIGGER trigger_insert_employee
七、启用和禁用 DML 触发器
ALTER TABLE employee
DISABLE TRIGGER ALL -- 禁止 employee 的所有触发器
ALTER TABLE employee
ENABLE TRIGGER trigger_insert_employee -- 启用某个触发器
八、创建 DDL 触发器

  DDL 触发器是SQL Server 2005 以后新增的触发器类型,它在响应 DDL 语句时触发,一般用于在数据库中执行管理任务。

CREATE TRIGGER trigger_t1
ON ALL SERVER -- DDL 触发器作用到当前服务器上所有数据库,ON 可以指定某个数据库对象
FOR DROP_TABLE,ALTER_TABLE --使用FOR或AFTER是一个意思,但DDL触发器不支持INSTEAD OF触发器。DROP_TABLE是激活 DDL 触发器的事件
AS PRINT '删除表或修改表结构!'
-- 创建数据库 DDL 语句操作记录表
CREATE TABLE table_ddl_log(
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
[event] NVARCHAR(100),-- 事件
[server] NVARCHAR(100), -- 服务器名
[database] NVARCHAR(100), -- 数据库名
[schema] NVARCHAR(100),-- 架构名
[sql] NVARCHAR(MAX), -- SQL语句
[operator] NVARCHAR(100),
[date] datetime2 DEFAULT getdate()
)
GO
-- 创建 DDL 语句记录触发器
ALTER TRIGGER trigger_ddl
ON DATABASE -- 指定当前数据库
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @log XML
SET @log = EVENTDATA()
INSERT ddl_log VALUES(
@log.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),
@log.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(100)'),
@log.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)'),
@log.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)'),
@log.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(4000)'),
CONVERT(nvarchar(100),CURRENT_USER),
GETDATE()
);
GO
九、触发器的使用
1、在触发器中使用 @@ROWCOUNT 获取此次数据操作受影响行数;
2、在触发器中使用 @@IDENTITY 获取插入记录的编号;
3、在UPDATE和INSERT触发器里可以使用 Update(字段名) 来判断某个字段是否被更改。
CREATE TRIGGER trigger_update_name_employee
ON employee
INSTEAD OF UPDATE
AS
SET NOCOUNT ON -- 不记录行数提高性能,这是个运行时设置语句,不是编译时语句
IF UPDATE(name)
BEGINPRINT '名字不能修改'RAISERROR('名字一旦确定不能修改',16,5)
END
GO
UPDATE employee SET name = '张三' WHERE id = 1

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQL数据IGG服务器XMLTI数据库删除CU存储过程
分页:12
转载请注明
本文标题:T-SQL语句(六)――触发器操作 SQL SQL语句 触发器操作
本站链接:http://www.codesec.net/view/532699.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(30)