触发器和存储过程

基于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’