触发器和存储过程

基于sql server

一 触发器

触发器是一段由对数据的更改操作引发的自动执行的代码,这些更改操作包括update、insert或delete。触发器通常用于保证业务规则和数据完整性,其主要优点是用户可以用编程的方法实现复杂的处理逻辑和商业规则,增强了数据完整性约束的功能。

语法格式:

CREATE TRIGGER 触发器名称
ON { 表名 | 视图名 }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT] [,] [ DELETE ] [ , ] [ UPDATE ] } 
AS
   SQL 语句
  • AFTER 后触发型触发器。可以在同一个操作上建立多个触发器。
  • FOR作用同AFTER
  • INSTEAD OF 前触发型触发器。在同一种操作上只能建立一个触发器
  • 在触发器中可以使用两个特殊的临时表 INSERTED 表 和 DELETED 表,这两个表的结构和建立触发器的表的结构完全相同,这两个临时表只能用在触发器代码中。
    INSERTED表保存了INSERT操作中新插入的数据和UPDATE操作中更新后的数据
    DELETED表保存了DELETE操作删除的数据和UPDATE操作更新前的数据

后触发型触发器
限制每个学生总的选课门数不能超过10门。

 CREATE TRIGGER_Total_AFT
 on SC AFTER INSERT
 AS
  IF (SELECT COUNT(*) FROM SC WHERE Sno IN (SELECT Sno FROM INSERTED ) ) >10
  ROLLBACK

前触发型触发器
限制每个学生总的选课门数不能超过10门。

CREATE Trigger tri_Total_INS
ON SC INSTEAD OF INSERT
AS
   IF ( SELECT COUNT(*) FROM SC  
      WHERE Sno IN ( SELECT Sno FROM INSERTED )) < 10
   INSERT INTO SC SELECT * FROM INSERTED

删除触发器
DROP TRIGGER 触发器名称

二 存储过程

语法格式:

CREATE PROC[EDURE] 存储过程名
[ { @参数名 数据类型} [ = default ] [OUTPUT] ] [, ……n]
AS
 SQL 语句 [ ……n]
  • default: 表明参数的默认值
  • OUTPUT: 表明参数是输出参数
  • 执行存储过程的sql语句 是 EXECUTE
    [ EXEC [UTE]] 存储过程名 [实参 [, OUTPUT] [,……n] ]

有多个参数并有默认值的存储过程:查询某个学生某门课程的考试成绩,若没有指定课程,则默认课程为 “VB”

CREATE PROCEDURE student_grade
    @sname char(10), @cname char(20) = 'VB'
AS
        SELECT Sname, Cname, Grade
         FROM Student s INNER JOIN SC
         ON s.Sno = SC.Sno INNER JOIN COURCE c
         ON c.Cno = SC.Cno
         WHERE Sname = @sname AND Cname = @cname

按参数位置:EXEC student_grade ‘Lily’ ‘高等数学’
按参数名传递值: EXEC student_grade @sname = ‘Lily’, @cname = ’ 高等数学’

带输入参数和输出参数的存储过程:统计指定课程(课程名)的平均成绩,并将统计结果作为输出参数返回。

CREATE PROC AvgGrade
    @cn char(20),
    @avg_grade int output
AS
    SELECT @avg_grade = AVG(Grade) FROM SC
     JOIN Cource C on C.Cno = SC.Cno
     Where Cname = cn

declare @Avg_Grade int
exec AvgGrade ‘VB’, @Avg_Grade output
print @Avg_Grade

删除指定课程(课程名)考试成绩不及格学生的此门课程的修课记录

CREATE PROC Del_SC
 @cn varchar(20)
AS
    DELETE FROM SC WHERE GRADE < 60
    AND Cno IN (
    SELECT Cno FROM Course WHERE Cname = @cn)

EXEC Del_SC ‘VB’

<think>好的,我现在需要帮用户弄清楚MySQL触发器存储过程的用法以及它们的区别。首先,我得回忆一下自己对这两个概念的理解,并确认是否正确。 触发器,我记得是在特定数据库操作(比如INSERT、UPDATE、DELETE)发生时自动执行的存储程序。用户提到的引用[1]中也提到,触发器分为三种类型:INSERT、UPDATEDELETE触发器触发器的执行是自动的,不需要手动调用,所以可能用于数据验证、审计日志或是维护数据一致性等场景。比如,当插入一条新记录时,触发器可以自动更新某个统计表。 存储过程则不同,引用[3]提到,存储过程需要显式地通过CALL或EXECUTE来调用,可以包含复杂的逻辑,比如控制结构(if、while等),并且能返回结果。存储过程更像是一个预定义的函数,把一系列SQL语句封装起来,方便重用。例如,处理订单时可能需要多个步骤,用存储过程可以把这些步骤集中管理,提高效率并减少网络传输,因为应用程序只需调用一次存储过程而不是发送多个SQL语句。 接下来是区别部分。触发器存储过程的主要区别在于触发方式用途。触发器是事件驱动的,自动触发,而存储过程需要显式调用。触发器的应用场景更多是数据变动相关的自动处理,而存储过程用于封装复杂的业务逻辑。此外,触发器不能直接返回结果,而存储过程可以返回查询结果或输出参数。引用[3]还指出,存储过程可以包含控制结构,这可能比触发器更灵活。 还要考虑它们的创建管理方式。触发器的创建语法是CREATE TRIGGER,需要指定触发事件时机(BEFORE/AFTER)。而存储过程用CREATE PROCEDURE,可以定义参数返回类型。触发器可能对性能有影响,尤其是在处理大量数据时,因为每个相关操作都会触发执行。而存储过程的性能优势在于减少多次数据库交互,但设计不当也可能成为瓶颈。 用户可能还关心实际应用中的择。比如,何时用触发器,何时用存储过程。例如,审计日志适合用触发器,因为需要在数据变动时自动记录;而复杂的业务逻辑如月度报表生成可能更适合存储过程,按需调用。 需要验证自己的理解是否正确,比如触发器的执行权限、存储过程能否处理事务等。根据引用[2],存储过程中的变量作用域在过程内部,类似Java的方法局部变量,而触发器中变量的使用可能受限,因为触发器通常特定的表事件绑定。 最后,用户可能希望知道使用它们的最佳实践潜在问题,比如触发器的递归触发风险,存储过程的调试维护难度。这些都是需要提及的。</think>### MySQL触发器存储过程的用法及区别解析 #### 一、触发器(Trigger)的用法 1. **定义与触发条件** 触发器是一种与表事件绑定的特殊存储程序,在特定操作($INSERT$、$UPDATE$、$DELETE$)执行时**自动触发**。例如: ```sql CREATE TRIGGER audit_log AFTER INSERT ON orders FOR EACH ROW BEGIN INSERT INTO log_table VALUES (NEW.order_id, NOW()); END; ``` 此触发器会在`orders`表插入数据后自动记录日志[^1]。 2. **类型与时机** - 支持`BEFORE`(操作前触发)`AFTER`(操作后触发)两种时机 - 分为三种类型:`INSERT`触发器、`UPDATE`触发器、`DELETE`触发器 3. **典型应用场景** - 数据审计(如记录修改痕迹) - 级联更新(如更新库存时自动调整统计表) - 约束补充(如实现复杂业务规则校验) --- #### 二、存储过程(Stored Procedure)的用法 1. **定义与调用方式** 存储过程是一组预编译的SQL语句集合,需通过`CALL`命令显式调用: ```sql CREATE PROCEDURE CalculateSales(IN year INT) BEGIN SELECT SUM(amount) FROM sales WHERE YEAR(order_date) = year; END; -- 调用示例 CALL CalculateSales(2023); ``` 支持输入/输出参数,可返回查询结果[^3]。 2. **功能特性** - 支持变量声明(如`DECLARE total INT DEFAULT 0;`) - 支持流程控制(如`IF...THEN`、`WHILE`循环) - 可嵌套调用其他存储过程[^2] 3. **典型应用场景** - 复杂业务逻辑封装(如订单结算流程) - 批量数据处理(如定期统计报表生成) - 权限控制(通过存储过程限制直接表访问) --- #### 三、核心区别对比 | **特性** | **触发器** | **存储过程** | |------------------------|-----------------------------------|---------------------------------| | 触发方式 | 自动由表事件触发 | 需显式调用`CALL`执行 | | 返回值 | 不能直接返回结果 | 可通过`OUT`参数或结果集返回数据 | | 事务控制 | 默认在触发语句的事务中执行 | 可独立声明事务(`BEGIN TRANSACTION`)| | 应用场景 | 数据变更的联动操作 | 复杂业务逻辑封装 | | 性能影响 | 高频操作可能引发级联性能问题 | 预编译减少解析开销,但需注意设计合理性 | --- #### 四、择建议 1. **优先使用触发器** - 需要保证数据一致性的自动化操作(如审计日志) - 简单级联更新(如更新订单状态后同步库存) 2. **优先使用存储过程** - 多步骤复杂业务逻辑(如用户注册流程包含校验、插入、发邮件) - 需要复用或对外提供标准化接口的操作 3. **注意事项** - 避免在触发器中嵌套复杂逻辑,可能导致性能瓶颈 - 存储过程版本管理需谨慎,修改可能影响多个调用点 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值