sqlserver创建存储过程和触发器.ppt

当创建一个触发器时必须指定如下选项： （1）名称； （2）在其上定义触发器的表； （3）触发器将何时激发； （4）激活触发器的数据修改语句，有效选项为 INSERT、UPDATE 或 DELETE，多个数据修改语句可激活同一个触发器； （5）执行触发操作的编程语句。 DML 触发器使用 deleted 和 inserted 逻辑表。它们在结构上和触发器所在的表的结构相同，SQL Server 会自动创建和管理这些表。 可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件。 Deleted表用于存储delete，update语句所影响的行的副本。在执行delete或 update 语句时，行从触发器表中删除，并传输到deleted表中。 Inserted 表用于存储 Insert 或 update 语句所影响的行的副本，在一个插入或更新事务处理中，新建的行被同时添加到 Inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。 使用SQL Server管理平台创建触发器的过程如下： 在SQL Server管理平台中，展开指定的服务器和数据库项，然后展开表，选择并展开要在其上创建触发器的表，如下图所示，右击触发器选项。 从弹出的快捷菜单中选择『新建触发器』选项，则会出现触发器创建窗口，如上图所示。 最后，单击『执行』按钮，即可成功创建触发器。 使用 CREATE TRIGGER 命令创建 DML 触发器的语法形式如下： CREATE TRIGGER [schema_name.] trigger_name ON { table|view } [WITH [ENCRYPTION]] {FOR|AFTER|INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]} AS {sql_statement [;] [...n]} 使用INSERT触发器： 场景： 你为销售部门创建一个数据库，用来存储所有的订货信息。销售经理要实时了解库存情况。因此用户提交订单时，库存数量将会自动减去订单中的数量。你觉得用什么方法比较方便呢？ INSERT触发器通常被用来更新时间标记字段，或者验证被触发器监控的字段中数据满足要求的标准，以确保数据的完整性。 举例：建立一个触发器（sc_ins） ，当向sc表中添加数据时，如果添加的数据与s表中的数据不匹配（没有对应的学号），则将此数据删除。（可设变量@bh ） 程序清单如下： CREATE TRIGGER sc_ins ON sc AFTER INSERT AS BEGIN DECLARE @bh char(5) Select @bh=Inserted.sno from Inserted If not exists(select sno from s where s.sno=@bh) Delete sc where sno=@bh END 当在一个有 UPDATE 触发器的表中修改记录时，表中原来的记录被移动到删除表中，修改过的记录插入到了插入表中，触发器可以参考删除表和插入表以及被修改的表，以确定如何完成数据库操作。 举例：创建一个修改触发器（tri_s_upd） ，该触发器防止用户修改表 s 的入学成绩。 程序清单如下： 使用UPDATE触发器： 场景： 你为销售部门创建一个数据库，包含产品的信息。为了防止新的销售人员销售库存为零的产品。销售部门希望你通过技术预防出现此类问题。 你决定使用触发器来加以预防。一旦销售人员的操作使得库存列中的数值为负值时，将执行RAISERROR（）命令进行报警，并将事件写入操作系统的事件日志中。 CREATE TRIGGER tri_s_upd ON s AFTER update AS IF UPDATE（score） BEGIN RAISERROR（‘不能修改入学成绩’,16,10） ROLLBACK TRANSACTION END GO 可使用IF UPDATE 语句来定义用来监视特定列的数据更新的触发器，这将允许触发器轻松分离出针对特定列的活动。 当触发器检测到特定列发生更新时，它会采取相应的操作，如：引发声明该列不可更新的错误信息，或者基于新更新的列值一系列的语句。 DELETE触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除，第二种情况是执行可删除主记