SQLserver中的触发器和存储过程

2024-08-26 06:12

本文主要是介绍SQLserver中的触发器和存储过程,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

在 SQL Server 中,触发器是一种特殊的存储过程,它在指定的数据库表上发生特定的数据修改事件时自动执行。触发器可以用于执行各种任务,如数据验证、数据审计、自动更新相关表等。

触发器的类型

SQL Server 支持以下几种类型的触发器:

  1. INSERT 触发器:在向表中插入新行时触发。

  2. UPDATE 触发器:在修改表中的现有行时触发。

  3. DELETE 触发器:在从表中删除行时触发。

  4. INSTEAD OF 触发器:在执行 UPDATE、DELETE 或 INSERT 操作之前触发,允许你自定义操作而不是执行默认操作。

触发器的创建

创建触发器的基本语法如下:

CREATE TRIGGER trigger_name
ON table_name
AFTER|INSTEAD OF {INSERT | UPDATE | DELETE}
AS
BEGIN-- 触发器的 SQL 代码
END

示例

假设我们有一个名为 Employees 的表,我们想要在每次插入新员工记录时自动记录这一操作到 AuditLog 表中。

  1. 创建触发器

CREATE TRIGGER trg_AfterInsertEmployee
ON Employees
AFTER INSERT
AS
BEGININSERT INTO AuditLog (Action, EmployeeID, ChangedDate)SELECT 'Insert', EmployeeID, GETDATE()FROM inserted
END

在这个例子中,trg_AfterInsertEmployee 是触发器的名称,它在 Employees 表上定义。当有新记录插入 Employees 表时,触发器会将操作记录到 AuditLog 表中。

  1. 使用 INSTEAD OF 触发器

如果你想要自定义 INSERT 操作,可以使用 INSTEAD OF 触发器。例如,假设我们想要在插入新员工之前验证数据:

CREATE TRIGGER trg_InsteadOfInsertEmployee
ON Employees
INSTEAD OF INSERT
AS
BEGINIF (SELECT COUNT(*) FROM inserted WHERE Age < 18) > 0BEGINRAISERROR ('Cannot insert employee under 18 years old.', 16, 1)ROLLBACK TRANSACTIONRETURNENDELSEBEGININSERT INTO Employees (EmployeeID, Name, Age)SELECT EmployeeID, Name, AgeFROM insertedEND
END

在这个例子中,如果尝试插入年龄小于 18 岁的员工,触发器将阻止插入并返回错误。

注意事项

  • 触发器可以影响数据库性能,特别是在高频率更新的表上。

  • 触发器中的错误可能导致数据不一致,因此在生产环境中使用触发器时需要谨慎。

  • 触发器可以嵌套,但嵌套触发器可能导致复杂的逻辑和性能问题,应尽量避免。

创建一个insert的触发器

CREATE TRIGGER TRIGGER_STUDENT_AFTERINSERT ON STUDENT AFTER INSERTAS DECLARE @STUID INT, @STUNAME NVARCHAR(50),@STUAGE INTSELECT @STUID=STUID, @STUNAME=STUNAME,@STUAGE=STUAGE FROM STUDENTPRINT CONVERT(VARCHAR,@STUID)+','+@STUNAME+','+ CONVERT(VARCHAR,@STUAGE)INSERT INTO STUDENT01(STUID,STUNAME,STUAGE) VALUES(@STUID,@STUNAME,@STUAGE)

SQLserver中存储过程

在 SQL Server 中,存储过程是一组为了完成特定功能的 SQL 语句集合,这些语句被保存在数据库中,可以通过一个调用语句来执行。存储过程可以接收参数、返回结果,并且可以进行复杂的逻辑处理。

存储过程的优点

  1. 性能优化:预编译的 SQL 语句可以提高执行效率。

  2. 安全性:通过存储过程,可以限制用户直接访问数据库表,只允许通过存储过程来操作数据,从而提高数据安全性。

  3. 重用性:可以重复使用存储过程,减少代码冗余。

  4. 减少网络流量:逻辑封装在服务器端,减少了客户端和服务器端之间的通信。

  5. 事务管理:可以在存储过程中方便地管理事务。

创建存储过程

创建存储过程的基本语法如下:

CREATE PROCEDURE ProcedureName@param1 DataType,@param2 DataType
AS
BEGIN-- SQL statements
END

示例

假设我们想要创建一个存储过程来插入新员工的信息:

CREATE PROCEDURE AddEmployee@EmployeeID INT,@EmployeeName NVARCHAR(100),@Department NVARCHAR(100)
AS
BEGININSERT INTO Employees (EmployeeID, Name, Department)VALUES (@EmployeeID, @EmployeeName, @Department)
END

调用存储过程

调用存储过程使用 EXECEXECUTE 语句:

EXEC AddEmployee @EmployeeID = 1, @EmployeeName = 'John Doe', @Department = 'Sales'

参数化存储过程

存储过程可以有输入参数、输出参数和返回值:

CREATE PROCEDURE GetEmployeeByID@EmployeeID INT,@EmployeeName NVARCHAR(100) OUTPUT
AS
BEGINSELECT @EmployeeName = Name FROM Employees WHERE EmployeeID = @EmployeeID
END

调用带有输出参数的存储过程:

DECLARE @Name NVARCHAR(100)
EXEC GetEmployeeByID @EmployeeID = 1, @EmployeeName = @Name OUTPUT
SELECT @Name AS EmployeeName

错误处理

存储过程中可以使用 TRY...CATCH 语句进行错误处理:

CREATE PROCEDURE ProcessData
AS
BEGINTRY-- 可能出错的 SQL 语句ENDCATCHBEGINSELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessageEND
END

存储过程的修改和删除

  • 修改存储过程:使用 ALTER PROCEDURE 语句。

  • 删除存储过程:使用 DROP PROCEDURE 语句。

ALTER PROCEDURE ProcedureName
AS
BEGIN-- 新的 SQL 语句
END
​
DROP PROCEDURE ProcedureName

注意事项

  • 存储过程可以非常复杂,包含循环、条件语句等。

  • 存储过程可以调用其他存储过程。

  • 存储过程的权限可以精细控制,例如,可以限制谁可以执行特定的存储过程。

  • 存储过程的执行可以通过动态 SQL 来实现更高级的功能。

使用存储过程按日期生产编号

CREATE PROC PROCCREATENO  AS 
BEGIN 
DECLARE @NO VARCHAR(20),@LASTSTOCKNO VARCHAR(20) ,@STRTIMESTR VARCHAR(8)
SET @STRTIMESTR= CONVERT(VARCHAR(8),GETDATE(),112)
SELECT @LASTSTOCKNO =(SELECT TOP 1 STOCKNO FROM STOCK WHERE SUBSTRING(STOCKNO,1,8)=@STRTIMESTR ORDER BY STOCKNO DESC)
PRINT @LASTSTOCKNO
IF @LASTSTOCKNO IS NULL
BEGIN 
SET @NO= @STRTIMESTR+'00001'
END
ELSE 
BEGIN
DECLARE @LEN INT ,@LASTNO INT,@TEMPNO VARCHAR(5),@N INT
SET @LASTNO =CONVERT(INT,SUBSTRING(@LASTSTOCKNO,9,5))+1
SET @LEN=LEN(@LASTNO)
SET @TEMPNO=CONVERT(VARCHAR,@LASTNO)
SET @N=0;
WHILE(@N<5-@LEN)
BEGIN
SET @TEMPNO='0'+@TEMPNO
SET @N+=1
END
SET @NO=@STRTIMESTR+@TEMPNO
END
INSERT INTO STOCK(STOCKNO) VALUES(@NO)
END

使用EXEC PROCCREATENO执行结果

结果为

这篇关于SQLserver中的触发器和存储过程的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/1107770

相关文章

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更

MySQL深分页进行性能优化的常见方法

《MySQL深分页进行性能优化的常见方法》在Web应用中,分页查询是数据库操作中的常见需求,然而,在面对大型数据集时,深分页(deeppagination)却成为了性能优化的一个挑战,在本文中,我们将... 目录引言:深分页,真的只是“翻页慢”那么简单吗?一、背景介绍二、深分页的性能问题三、业务场景分析四、

MySQL 迁移至 Doris 最佳实践方案(最新整理)

《MySQL迁移至Doris最佳实践方案(最新整理)》本文将深入剖析三种经过实践验证的MySQL迁移至Doris的最佳方案,涵盖全量迁移、增量同步、混合迁移以及基于CDC(ChangeData... 目录一、China编程JDBC Catalog 联邦查询方案(适合跨库实时查询)1. 方案概述2. 环境要求3.

Linux进程CPU绑定优化与实践过程

《Linux进程CPU绑定优化与实践过程》Linux支持进程绑定至特定CPU核心,通过sched_setaffinity系统调用和taskset工具实现,优化缓存效率与上下文切换,提升多核计算性能,适... 目录1. 多核处理器及并行计算概念1.1 多核处理器架构概述1.2 并行计算的含义及重要性1.3 并

Spring boot整合dubbo+zookeeper的详细过程

《Springboot整合dubbo+zookeeper的详细过程》本文讲解SpringBoot整合Dubbo与Zookeeper实现API、Provider、Consumer模式,包含依赖配置、... 目录Spring boot整合dubbo+zookeeper1.创建父工程2.父工程引入依赖3.创建ap

SpringBoot3.X 整合 MinIO 存储原生方案

《SpringBoot3.X整合MinIO存储原生方案》本文详细介绍了SpringBoot3.X整合MinIO的原生方案,从环境搭建到核心功能实现,涵盖了文件上传、下载、删除等常用操作,并补充了... 目录SpringBoot3.X整合MinIO存储原生方案:从环境搭建到实战开发一、前言:为什么选择MinI

SQL server数据库如何下载和安装

《SQLserver数据库如何下载和安装》本文指导如何下载安装SQLServer2022评估版及SSMS工具,涵盖安装配置、连接字符串设置、C#连接数据库方法和安全注意事项,如混合验证、参数化查... 目录第一步:打开官网下载对应文件第二步:程序安装配置第三部:安装工具SQL Server Manageme

C#连接SQL server数据库命令的基本步骤

《C#连接SQLserver数据库命令的基本步骤》文章讲解了连接SQLServer数据库的步骤,包括引入命名空间、构建连接字符串、使用SqlConnection和SqlCommand执行SQL操作,... 目录建议配合使用:如何下载和安装SQL server数据库-CSDN博客1. 引入必要的命名空间2.

Linux下进程的CPU配置与线程绑定过程

《Linux下进程的CPU配置与线程绑定过程》本文介绍Linux系统中基于进程和线程的CPU配置方法,通过taskset命令和pthread库调整亲和力,将进程/线程绑定到特定CPU核心以优化资源分配... 目录1 基于进程的CPU配置1.1 对CPU亲和力的配置1.2 绑定进程到指定CPU核上运行2 基于

全面掌握 SQL 中的 DATEDIFF函数及用法最佳实践

《全面掌握SQL中的DATEDIFF函数及用法最佳实践》本文解析DATEDIFF在不同数据库中的差异,强调其边界计算原理,探讨应用场景及陷阱,推荐根据需求选择TIMESTAMPDIFF或inte... 目录1. 核心概念:DATEDIFF 究竟在计算什么?2. 主流数据库中的 DATEDIFF 实现2.1