存储过程和触发器

目录

1、存储过程

1.1 存储过程的概述

1.2 存储过程的类型

1. 系统存储过程

2. 本地存储过程

3. 临时存储过程

4. 扩展存储过程

1.3 T-SQL创建存储过程

1.4 T-SQL执行存储过程

1.5 T-SQL查看存储过程

1.6 T-SQL修改存储过程

1.7 T-SQL删除存储过程

2、触发器

2.1 触发器的概述

2.2 触发器的类型

1．DML触发器

2．DDL 触发器

3．登录触发器

2.3 T-SQL创建触发器

1. 创建触发器的注意事项

2. DML触发器

3. DDL触发器

2.4 T-SQL查看触发器

2.5 T-SQL修改触发器

2.6 T-SQL禁用和启用触发器

2.7 T-SQL删除触发器

---

 T-SQL的语法约定

数据库的概念和操作-CSDN博客

1、存储过程

1.1 存储过程的概述

存储过程是一组在数据库系统中为了完成特定功能的T-SQL语句的集合，经编译后独立存储在数据库中。当需要其功能时，只需要通过存储过程名并给出参数（如果存储过程有参数的情况）调用即可，且存储过程只在首次执行时进行编译，而不需要每次执行时重新编译，所以比单个T-SQL语句块的运行速度快。

存储过程是SQL Server中一个非常有用的工具。SQL Server支持存储过程和系统过程。存储过程是独立存在于表之外的数据对象。可以由客户调用，也可以从另一个过程或触发器调用，参数可以被传递和返回，出错代码也可以被检验到。

1.2 存储过程的类型

1. 系统存储过程

系统存储过程是安装SQL Server 2014时自动创建的。系统存储过程能执行许多管理性和信息性活动（如获取数据库信息或者数据库对象的信息等）。系统过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，可以在其他数据库中被调用。

2. 本地存储过程

本地存储过程也称为用户定义存储过程，是由用户自行创建并存储在用户数据库中的存储过程，一般所说的存储过程指的就是本地存储过程。

3. 临时存储过程

临时存储过程可分为以下两种：

(1)本地临时存储过程

如果在创建存储过程时，其名称以“#”号开头，则该存储过程将成为一个存储在tempdb数据库中的本地临时存储过程。

(2)全局临时存储过程

如果在创建存储过程时，其名称以 “##”号开头，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程。

不论创建的是本地临时存储过程还是全局临时存储过程，只要SQL Server 2014停止运行，它们将不复存在。

4. 扩展存储过程

扩展存储过程是用户可以使用外部程序语言（例如C语言）编写的存储过程。显而易见，扩展存储过程可以弥补SQL Server 2014的不足，并按需要自行扩展其功能。

扩展存储过程在使用和执行上与一般的存储过程完全相同，为了区别，扩展存储过程的名称通常以XP_开头。扩展存储过程是以动态链接库(DLL)的形式存在，能让SQL Server 2014动态地装载和执行。扩展存储过程一定要存储在系统数据库master中。

1.3 T-SQL创建存储过程

CREATE {PROC|PROCEDURE} [schema_name.]procedure_name
[ @parameter data_type [=default] [OUT|OUTPUT|[READONLY]] [,...n] 
AS {[BEGIN] sql_statement [...n] [END]}

PROC|PROCEDURE：PROCEDURE可以简写为PROC

OUT|OUTPUT|READONLY：

OUT|OUTPUT：输出型参数，类似于引用。

READONLY：不能在过程的主体中修改参数。参数类型为表值类型，必须指定READONLY。

1.4 T-SQL执行存储过程

[EXEC|EXECUTE] procedure_name [[@parameter=]{value|@variable[OUTPUT]}] [,...n]

EXEC|EXECUTE：EXECUTE可以简写为EXEC，如果存储过程是批处理中的第一条语句，那么可以省略EXECUTE关键字。

@parameter=：关键字参数，明确指定哪个实参传递给那个形参。

@variable OUTPUT：输出型参数，形参和实参的位置都要写OUTPUT。

1.5 T-SQL查看存储过程

SP_HELPTEXT 'name' -- 命令语句
SP_HELP ['name'] -- 名称、架构名、类型、创建时间、参数
-- name省略时，显示所有数据库对象

1.6 T-SQL修改存储过程

与T-SQL创建存储过程相同，只是把CREATE 换成 ALTER

1.7 T-SQL删除存储过程

DROP {PROC|PROCEDURE} {[schema_name.]procedure_name} [,...n]

2、触发器

2.1 触发器的概述

触发器是一种特殊的存储过程，它会在特定的事件或条件下自动执行。

优点：

1. 强制实现比CHECK约束更复杂的数据的完整性。

2. 实现自定义的错误信息提示。

3. 对数据库中的相关表实现级联修改和删除。

4. 可调用更多的存储过程。

5. 禁止或回滚违反引用完整性(主键外键)的更改。

2.2 触发器的类型

1．DML触发器

DML触发器是当数据库服务器中发生数据操作语言(DML)事件时会自动执行的存储过程。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。DML触发器可用于强制业务规则和数据完整性、查询其他表并包括复杂的T-SQL语句。

SQL Server 2014的DML触发器分为两类：

①AFTER触发器

在执行INSERT、UPDATE、MERGE或DELETE语句的操作之后执行AFTER触发器。 AFTER触发器只能在表上定义，可以为针对表的同一操作定义多个触发器。

②INSTEAD OF触发器

与AFTER触发器不同，INSTEAD OF触发器一般用来取代原来的操作，它是在数据变更之前触发的，它并不执行原来的操作语句（INSERT、UPDATE或DELETE），而去执行触发器本身所定义的操作。 INSTEAD OF触发器不仅可以定义在表上，也可以定义在视图上。

2．DDL 触发器

DDL触发器在响应数据定义语言（DDL）语句时触发，DDL触发器一般用于数据库中执行管理任务。

当在执行触发DDL触发器的DDL语句后，DDL触发器才会触发。DDL触发器无法作为INSTEAD OF触发器使用。

3．登录触发器

登录触发器将为响应 LOGIN 事件而激发存储过程。

2.3 T-SQL创建触发器

1. 创建触发器的注意事项

(1) CREATE TRIGGER语句必须是批处理的第一条语句，只能用于一个表或视图。

(2) 创建触发器的权限默认为表的所有者，不能将该权限转让给其他用户。

(3) 虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建。

(4) 虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表，不应引用系统表，而应使用系统架构视图。

(5) TRUNCATE TABLE 语句类似于没有WHERE字句的DELETE语句，但不会激发DELETE触发器。

2. DML触发器

CREATE TRIGGER [schema_name.]trigger_name 
ON {table|view} 
{FOR|AFTER|INSTEAD OF}  -- FOR=AFTER
{[INSERT][,][UPDATE][,][DELETE]}
AS {sql_statement[ ,...n ] }

 ROLLBACK：是回滚，类似于撤销。

inserted表：表中insert的数据。

deleted表：表中delete的数据。update是新数据在inserted表，旧数据在deleted表。 

例1：静止修改course表

use teaching
go
create trigger unupdate_c
on course
after update
as
beginraiserror( '禁止修改course表',16,1)rollback
end

例2：触发器级联更新，更新course表的cno，sc表中的cno也随之改变。

use teaching
go
create trigger C_sc_UP
on course
after update
asif update(cno)begindeclare @cno1 char(4),@cno2 char(4)select @cno1 = cno from insertedselect @cno2 = cno from deletedupdate scset cno = @cno1where cno = @cno2end
go

例3：触发器的引用完整性(主键外键)，向sc表中插入数据，判断数据是否在student和course中存在。

create trigger tr_sc on sc
for insert
asif not exists(select * from student where sno = (select sno from inserted)) ornot exists(select * from course where cno = (select cno from inserted))beginprint '您要插入的sno或cno有误'rollbackend
go

3. DDL触发器

CREATE TRIGGER [schema_name.]trigger_name 
ON {ALL SERVER|DATABASE} -- 当前服务器|当前数据库
{FOR|AFTER}  -- 没有INSTEAD OF
{event_type|event_group}[,...n]
AS {sql_statement[ ,...n ] }

event_type：如：CREATE_TABLE、ALTER_TABLE、DROP_TABLE……

event_group：如：DDL_TABLE_EVENTS(包含了CREATE_TABLE、ALTER_TABLE、DROP_TABLE)

2.4 T-SQL查看触发器

SP_HELPTEXT 'name' -- 命令语句
SP_HELP ['name'] -- 名称、架构名、类型、创建时间、参数
-- name省略时，显示所有数据库对象

2.5 T-SQL修改触发器

与T-SQL创建触发器相同，只是把CREATE 换成 ALTER

2.6 T-SQL禁用和启用触发器

禁用或启用DML触发器的T-SQL语句的语法格式如下：

ALTER TABLE table_name 
ENABLE|DISABLE TRIGGER [ALL|trigger_name[ ,...n ]]

禁用或启用DDL触发器的T-SQL语句的语法格式如下：

ENABLE|DISABLE TRIGGER {[schema_name.][ALL|trigger_name[ ,...n ]]}
ON {object_name|DATABASE|ALL SERVER}

例：禁用teaching数据库中的safety触发器

DISABLE TRIGGER safety on database

2.7 T-SQL删除触发器

DROP TRIGGER trigger_name