SQL Server学习笔记：存储过程与触发器

2018年2月5日 · 986 字

学习背景

在上一篇笔记中，我学习了SQL Server的基础语法和查询操作。今天的学习主题是SQL Server的存储过程和触发器，这些是SQL Server的高级特性，能够提高数据库操作的效率和安全性。

存储过程

1. 存储过程的概念

存储过程是预编译的SQL语句集合，存储在数据库中，可通过名称调用。它具有以下优点：

提高性能：预编译执行计划，减少网络传输
增强安全性：可以控制用户对数据的访问权限
简化代码：将复杂的业务逻辑封装在存储过程中
可维护性：集中管理业务逻辑，便于修改和维护

2. 创建存储过程

基本语法

CREATE PROCEDURE ProcedureName
    @Parameter1 DataType,
    @Parameter2 DataType = DefaultValue
AS
BEGIN
    -- SQL语句
END
GO

示例：创建获取员工信息的存储过程

CREATE PROCEDURE GetEmployeeInfo
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees
    WHERE EmployeeID = @EmployeeID;
END
GO

示例：创建带输出参数的存储过程

CREATE PROCEDURE GetDepartmentSalary
    @Department NVARCHAR(50),
    @AvgSalary DECIMAL(12, 2) OUTPUT
AS
BEGIN
    SELECT @AvgSalary = AVG(Salary)
    FROM Employees
    WHERE Department = @Department;
END
GO

3. 执行存储过程

执行基本存储过程

EXEC GetEmployeeInfo @EmployeeID = 1;
GO

执行带输出参数的存储过程

DECLARE @Result DECIMAL(12, 2);
EXEC GetDepartmentSalary @Department = 'IT', @AvgSalary = @Result OUTPUT;
PRINT '平均工资: ' + CAST(@Result AS NVARCHAR(20));
GO

4. 修改和删除存储过程

修改存储过程

ALTER PROCEDURE GetEmployeeInfo
    @EmployeeID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department, Salary
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END
GO

删除存储过程

DROP PROCEDURE GetEmployeeInfo;
GO

触发器

1. 触发器的概念

触发器是一种特殊的存储过程，当表发生特定事件（如INSERT、UPDATE、DELETE）时自动执行。它具有以下特点：

自动执行：不需要手动调用
事件驱动：由特定事件触发
数据验证：可以验证数据的完整性
业务逻辑：可以实现复杂的业务规则

2. 创建触发器

AFTER触发器

CREATE TRIGGER tr_Employee_AfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    -- 插入后的操作
    INSERT INTO EmployeeAudit (EmployeeID, Action, ActionDate)
    SELECT EmployeeID, 'INSERT', GETDATE()
    FROM inserted;
END
GO

INSTEAD OF触发器

CREATE TRIGGER tr_Employee_InsteadOfDelete
ON Employees
INSTEAD OF DELETE
AS
BEGIN
    -- 替代删除操作，改为标记为已删除
    UPDATE Employees
    SET IsDeleted = 1
    WHERE EmployeeID IN (SELECT EmployeeID FROM deleted);
END
GO

3. 触发器中的特殊表

inserted表：存储INSERT或UPDATE操作中插入的新数据
deleted表：存储DELETE或UPDATE操作中删除的旧数据

4. 修改和删除触发器

修改触发器

ALTER TRIGGER tr_Employee_AfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO EmployeeAudit (EmployeeID, FirstName, LastName, Action, ActionDate)
    SELECT EmployeeID, FirstName, LastName, 'INSERT', GETDATE()
    FROM inserted;
END
GO

删除触发器

DROP TRIGGER tr_Employee_AfterInsert;
GO

实践练习

练习1：创建存储过程

创建一个存储过程，根据部门名称获取该部门的所有员工
创建一个存储过程，计算员工的年度奖金（基于工资的10%）
创建一个存储过程，更新员工的工资，并记录更新前后的工资

练习2：创建触发器

创建一个触发器，在插入新员工时自动计算年龄
创建一个触发器，在更新员工工资时验证工资不能为负数
创建一个触发器，在删除员工时记录删除操作到审计表

总结

通过今天的学习，我掌握了SQL Server的存储过程和触发器的基本概念和使用方法。存储过程和触发器是SQL Server的重要特性，它们可以提高数据库操作的效率和安全性，实现复杂的业务逻辑。在实践过程中，我发现存储过程和触发器的使用需要谨慎，避免过度使用导致系统复杂度增加。接下来，我将学习SQL Server的索引和视图，进一步提高数据库的性能和可维护性。

SeekCyber's Blog