SQL触发器语法(1)

SQL触发器 一、触发器的基本概念 1、触发器的定义 触发器是一种特殊的存储过程,它和 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 密切相连,可以看作是表格定义的一部分。当用户对指定表操作时,触发器会自动执行。触发器在update、insert、delete等操作执行结束后才执行。 2、触发器的功能 (1)级联更新数据库中相关表的数据; (2)实现多表之间数据的一致性; (3)执行比检查约束更复杂的约束操作; (4)调用存储过程; (5)在一张表的update、insert、delete操作上可设置多个触发器。3、使用触发器应注意的问题 (1)当使用约束、规则、默认值等 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 能够实现数据的完整性,就不用触发器实现;T1,T2 INSERT (2)只有表的拥有者才可以在表上创建或删除触发器,这种权限不准转授; (3)使用update语句可以一次对多行数据进行修改,而触发器只被触发一次; (4)触发器只能在当前数据库中创建,触发器的命名必须要遵守标识符的命名规则。 4、触发器的优点 (1)触发器是自动的; (2)触发器可以通过数据库中的相关表进行层叠更改; (3)触发器可以强制限制,这些限制比用check 所定义的约束更复杂 5、触发器的工作原理 inserted表和deleted表的特点如下: (1)这两张表是逻辑表,由数据库管理,用户不能对他们进行修改; (2)这两张表存储在内存中,而不是存储在数据库中; (3)这两张表的结构与被该触发器作用的表结构相同; (4)当触发器完成操作后,这两张表会自动删除; (5)两张表中保存的数据是因用户操作而被影响到的原数据或新数据; (6)这两张表是只读表。 二、触发器的建立 SQL触发器语法 CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ]{{ { FOR | AFTER | INSTEAD OF } { [ INS ERT ] [ DELETE ] [ UPDATE ] } AS SQL语句 参数说明: trigger_name 是触发器的名称。触发器名称必须符合标识符规则,并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。 Table | view 是在其上执行触发器的表或视图,有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。 WITH ENCRYPTION 加密syscomments 表中包含CREATE TRIGGER 语句文本的条目。使用WI TH ENCRYPTION 可防止将触发器作为SQL Server 复制的一部分发布。 AFTER 指定触发器只有在触发SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后,才能执行此触发器。 如果仅指定FOR 关键字,则AFTER 是默认设置。 不能在视图上定义AFTER 触发器。 INSTEAD OF 指定执行触发器而不是执行触发SQL 语句,从而替代触发语句的操作。 在表或视图上,每个INSERT、UPDATE 或DELETE 语句最多可以定义一个INSTEAD OF 触发器。 INSTEAD OF 触发器不能在WITH CHECK OPTION 的可更新视图上定义。如果向指定了WITH CHECK OPTION 选项的可更新视图添加INSTEAD OF 触发器,SQL Server 将产生一个错误。用户必须用ALTER VIEW 删除该选项后才能定义INSTEAD OF 触发器。 { [DELETE] [,] [INSERT] [,] [UPDATE] } 是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个,需用逗号分隔这些选项。 对于INSTEAD OF 触发器,不允许在具有ON DELETE 级联操作引用关系的表上使用DELETE 选项。同样,也不允许在具有ON UPDATE 级联操作引用关系的表上使用UPDATE 选项。 * deleted 和inserted 是逻辑(概念)表。这些表在结构上类似于定义触发器的表(也就是在其中尝试用户操作的表);这些表用于保存用户操作可能更改的行的旧值或新值。例如,若要检索deleted 表中的所有值,请使用: SELECT * FROM deleted 禁用触发器:disable trigger 触发器名ON 表名 恢复启用:ENABLE TRIGGER 触发器名ON 表名 删除触发器:DROP TRIGGER 触发器名 例1:创建一个触发器具有显示信息功能。为成绩表创建一个触发器,在删除成绩表中的记录时,显示表中有多少条记录被删除。 use hy go create trigger tri1 on sc for delete as begin declare @count varchar(30) select @count=str(@@rowcount)+'个记录被删除' print @count end go 触发语句 delete from sc where grade=0 例2:在SC表上创建一个触发器,该触发器被INSERT操作触发,当用户向SC表插入一条新记录时,判断该记录的学号在S表中是否存在,如果存在插入成功, 否则插入失败。 create trigger t5 on sc after insert as begin if not exists(select*from s where sno= (select sno from inserted)) begin rollback tran print'此记录无效' END end go insert into sc values('200112','04',45,null) 例3、为学生表创建一个触发器,实现级联插入。当向学生表S中插入一条新记录时,系统会将该生的学号自动插入到成绩表SC中,并在成绩表中填写选修的课程编号是’02’。 create trigger t2 on s for insert as begin declare @number varchar(10) select @number=sno from inserted insert into sc(sno,cno,GRADE)values(@number,'02',90) end 触发 insert into s(sno,sname,ssex) values('010122','ggg','男') 例4、在S表上创建一个触发器,被UPDATE操作触发,当用户在S表中修改一条学生记录的学号时,同时自动更新SC表中相应的学号 create trigger t8 on s for update as begin update sc set sno=(select sno from inserted) where sno=(select sno from deleted) end 触发语句 update s set sno='010133'where sno='010122' 注明:如果此时三表之间已经建立了外码约束,则UPDATE语句不能正常执行,提示违反外码约束,前面已经讲过,触发器可以设置级联更新,前提条件是表之间没有建立外码约束。 二、触发器的修改、删除和显示 1.修改触发器 利用T-SQL修改触发器的语法格式: alter trigger 触发器名称 on 表名 for update[,insert ,delete] as begin sql语句 end return 语法注释: 修改触发器只需在创建触发器的语法格式里,将create 改为alter 2.删除触发器 利用T-SQL删除触发器的语法格式: drop trigger 触发器名[ ,...n ] 语法注释: 触发器名要删除的触发器名称 [ ,...n ] 表示可以删除多个触发器 3.显示触发器信息 用SQL Server 提供的系统存储过程显示所创建的触发器的有关信息。格式及功能 如下: sp_help 触发器名显示触发器的一般信息 sp_helptext 触发器名显示触发器的文本信息 sp_depends 表名显示表中各种依赖关系 sp_depends 触发器名显示触发器所引用的对象。