SQLServer教程FG018-SQLServer函数触发器实战
目录大纲
内容简介
本文档基于SQLServer官方文档的函数和触发器内容，结合生产环境实际情况，详细讲解SQLServer用户定义函数、表值函数、触发器的创建和使用等内容。风哥教程参考SQLServer官方文档User-defined Functions、Triggers等相关章节。
Part01-基础概念与理论知识
1.1 SQLServer函数概念
SQLServer用户定义函数类型：
- 标量函数：返回单个值
- 内联表值函数：返回表，类似视图
- 多语句表值函数：返回表，可包含复杂逻辑
更多视频教程www.fgedu.net.cn
1.2 SQLServer触发器概念
SQLServer触发器类型：
- DML触发器：响应INSERT、UPDATE、DELETE操作
- DDL触发器：响应CREATE、ALTER、DROP操作
- LOGON触发器：响应登录事件
学习交流加群风哥微信: itpux-com
1.3 SQLServer函数与触发器区别
函数与触发器的主要区别：
调用方式 显式调用 自动触发
返回值 必须返回值 不返回值
修改数据 不能修改数据 可以修改数据
事务 不能包含事务 可以包含事务
使用场景 计算和查询 审计和数据同步
学习交流加群风哥QQ113257174
Part02-生产环境规划与建议
2.1 SQLServer函数设计原则
函数设计原则：
- 保持函数简单，避免复杂逻辑
- 标量函数可能影响性能，谨慎使用
- 优先使用内联表值函数替代标量函数
- 避免在函数中执行耗时操作
风哥提示：标量函数可能导致查询性能下降，建议使用内联表值函数替代
2.2 SQLServer触发器设计原则
触发器设计原则：
- 触发器逻辑应简洁高效
- 避免触发器嵌套调用
- 使用触发器进行审计和数据验证
- 避免在触发器中执行耗时操作
更多学习教程公众号风哥教程itpux_com
2.3 SQLServer性能优化建议
性能优化建议：
- 避免在WHERE子句中使用标量函数
- 使用内联表值函数替代多语句表值函数
- 触发器中避免使用游标
- 考虑使用计算列替代标量函数
from SQLServer视频:www.itpux.com
Part03-生产环境项目实施方案
3.1 SQLServer标量函数
创建标量函数：
USE fgedudb;
GO
CREATE TABLE fgedu.employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE
);
GO
INSERT INTO fgedu.employees VALUES
(1, ‘张三’, ‘技术部’, 15000.00, ‘2020-01-15’),
(2, ‘李四’, ‘销售部’, 12000.00, ‘2021-03-20’),
(3, ‘王五’, ‘技术部’, 18000.00, ‘2019-06-10’),
(4, ‘赵六’, ‘人事部’, 10000.00, ‘2022-01-01’);
GO
— 创建标量函数：计算工龄
CREATE FUNCTION fgedu.fn_get_work_years(@hire_date DATE)
RETURNS INT
AS
BEGIN
DECLARE @years INT;
SET @years = DATEDIFF(YEAR, @hire_date, GETDATE());
RETURN @years;
END;
GO
— 使用标量函数
SELECT
emp_id,
emp_name,
hire_date,
dbo.fn_get_work_years(hire_date) AS work_years
FROM fgedu.employees;
GO
— 创建标量函数：计算年薪
CREATE FUNCTION fgedu.fn_get_annual_salary(@salary DECIMAL(10,2), @bonus_rate DECIMAL(5,2))
RETURNS DECIMAL(15,2)
AS
BEGIN
RETURN @salary * 12 * (1 + @bonus_rate/100);
END;
GO
— 使用年薪函数
SELECT
emp_name,
salary,
dbo.fn_get_annual_salary(salary, 10) AS annual_salary_with_bonus
FROM fgedu.employees;
GO
执行结果：
——- ——— ———– ———-
1 张三 2020-01-15 6
2 李四 2021-03-20 5
3 王五 2019-06-10 7
4 赵六 2022-01-01 4
emp_name salary annual_salary_with_bonus
——— ——— ————————
张三 15000.00 198000.00
李四 12000.00 158400.00
王五 18000.00 237600.00
赵六 10000.00 132000.00
3.2 SQLServer表值函数
创建表值函数：
CREATE FUNCTION fgedu.fn_get_employees_by_dept(@department VARCHAR(50))
RETURNS TABLE
AS
RETURN
(
SELECT
emp_id,
emp_name,
salary,
hire_date
FROM fgedu.employees
WHERE department = @department
);
GO
— 使用内联表值函数
SELECT * FROM fgedu.fn_get_employees_by_dept(‘技术部’);
GO
— 创建多语句表值函数
CREATE FUNCTION fgedu.fn_get_salary_statistics(@department VARCHAR(50))
RETURNS @result TABLE (
department VARCHAR(50),
emp_count INT,
total_salary DECIMAL(15,2),
avg_salary DECIMAL(10,2),
max_salary DECIMAL(10,2),
min_salary DECIMAL(10,2)
)
AS
BEGIN
IF @department IS NULL
BEGIN
INSERT INTO @result
SELECT
department,
COUNT(*),
SUM(salary),
AVG(salary),
MAX(salary),
MIN(salary)
FROM fgedu.employees
GROUP BY department;
END
ELSE
BEGIN
INSERT INTO @result
SELECT
department,
COUNT(*),
SUM(salary),
AVG(salary),
MAX(salary),
MIN(salary)
FROM fgedu.employees
WHERE department = @department
GROUP BY department;
END
RETURN;
END;
GO
— 使用多语句表值函数
SELECT * FROM fgedu.fn_get_salary_statistics(NULL);
GO
SELECT * FROM fgedu.fn_get_salary_statistics(‘技术部’);
GO
执行结果：
——- ——— ———- ———-
1 张三 15000.00 2020-01-15
3 王五 18000.00 2019-06-10
department emp_count total_salary avg_salary max_salary min_salary
———– ———- ————- ———– ———– ———-
人事部 1 10000.00 10000.00 10000.00 10000.00
技术部 2 33000.00 16500.00 18000.00 15000.00
销售部 1 12000.00 12000.00 12000.00 12000.00
department emp_count total_salary avg_salary max_salary min_salary
———– ———- ————- ———– ———– ———-
技术部 2 33000.00 16500.00 18000.00 15000.00
3.3 SQLServer触发器实现
创建触发器：
CREATE TABLE fgedu.employee_audit (
audit_id INT IDENTITY(1,1) PRIMARY KEY,
emp_id INT,
action_type VARCHAR(20),
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
change_time DATETIME DEFAULT GETDATE(),
change_user VARCHAR(100)
);
GO
— 创建INSERT触发器
CREATE TRIGGER fgedu.trg_employee_insert
ON fgedu.employees
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO fgedu.employee_audit (emp_id, action_type, old_salary, new_salary, change_user)
SELECT
i.emp_id,
‘INSERT’,
NULL,
i.salary,
SUSER_NAME()
FROM inserted i;
END;
GO
— 创建UPDATE触发器
CREATE TRIGGER fgedu.trg_employee_update
ON fgedu.employees
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(salary)
BEGIN
INSERT INTO fgedu.employee_audit (emp_id, action_type, old_salary, new_salary, change_user)
SELECT
i.emp_id,
‘UPDATE’,
d.salary,
i.salary,
SUSER_NAME()
FROM inserted i
INNER JOIN deleted d ON i.emp_id = d.emp_id
WHERE i.salary <> d.salary;
END
END;
GO
— 创建DELETE触发器
CREATE TRIGGER fgedu.trg_employee_delete
ON fgedu.employees
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO fgedu.employee_audit (emp_id, action_type, old_salary, new_salary, change_user)
SELECT
d.emp_id,
‘DELETE’,
d.salary,
NULL,
SUSER_NAME()
FROM deleted d;
END;
GO
— 测试触发器
INSERT INTO fgedu.employees VALUES (5, ‘钱七’, ‘财务部’, 13000.00, ‘2023-01-01’);
GO
UPDATE fgedu.employees SET salary = 16000.00 WHERE emp_id = 1;
GO
— 查看审计记录
SELECT * FROM fgedu.employee_audit ORDER BY audit_id;
GO
执行结果：
——— ——- ———— ———– ———– ———————- ————
1 5 INSERT NULL 13000.00 2026-04-08 10:30:00 fgedu
2 1 UPDATE 15000.00 16000.00 2026-04-08 10:30:05 fgedu
Part04-生产案例与实战讲解
4.1 SQLServer业务计算函数案例
业务计算函数实战：
CREATE TABLE fgedu.sales_orders (
order_id INT IDENTITY(1,1) PRIMARY KEY,
customer_id INT,
order_amount DECIMAL(15,2),
order_date DATETIME DEFAULT GETDATE()
);
GO
INSERT INTO fgedu.sales_orders (customer_id, order_amount)
SELECT
ABS(CHECKSUM(NEWID())) % 100 + 1,
ABS(CHECKSUM(NEWID())) % 10000 + 100
FROM sys.objects a CROSS JOIN sys.objects b;
GO
— 创建折扣计算函数
CREATE FUNCTION fgedu.fn_calculate_discount(@amount DECIMAL(15,2))
RETURNS DECIMAL(5,2)
AS
BEGIN
DECLARE @discount DECIMAL(5,2);
IF @amount >= 10000
SET @discount = 15.00;
ELSE IF @amount >= 5000
SET @discount = 10.00;
ELSE IF @amount >= 1000
SET @discount = 5.00;
ELSE
SET @discount = 0.00;
RETURN @discount;
END;
GO
— 创建最终价格计算函数
CREATE FUNCTION fgedu.fn_calculate_final_price(@amount DECIMAL(15,2))
RETURNS DECIMAL(15,2)
AS
BEGIN
DECLARE @discount DECIMAL(5,2);
DECLARE @final_price DECIMAL(15,2);
SET @discount = dbo.fn_calculate_discount(@amount);
SET @final_price = @amount * (1 – @discount/100);
RETURN @final_price;
END;
GO
— 使用函数计算订单价格
SELECT TOP 10
order_id,
order_amount,
dbo.fn_calculate_discount(order_amount) AS discount_percent,
dbo.fn_calculate_final_price(order_amount) AS final_price
FROM fgedu.sales_orders
ORDER BY order_amount DESC;
GO
执行结果：
——— ————- —————– ————
123 10089.00 15.00 8575.65
456 9987.00 10.00 8988.30
789 9876.00 10.00 8888.40
…
4.2 SQLServer审计触发器案例
完整审计触发器实战：
CREATE TABLE fgedu.change_log (
log_id INT IDENTITY(1,1) PRIMARY KEY,
table_name VARCHAR(100),
operation VARCHAR(20),
record_id INT,
old_values NVARCHAR(MAX),
new_values NVARCHAR(MAX),
change_time DATETIME DEFAULT GETDATE(),
change_user VARCHAR(100),
application_name VARCHAR(200)
);
GO
— 创建通用审计触发器
CREATE TRIGGER fgedu.trg_products_audit
ON fgedu.products
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @operation VARCHAR(20);
— 判断操作类型
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
SET @operation = ‘UPDATE’;
ELSE IF EXISTS (SELECT * FROM inserted)
SET @operation = ‘INSERT’;
ELSE IF EXISTS (SELECT * FROM deleted)
SET @operation = ‘DELETE’;
— 记录审计日志
IF @operation = ‘INSERT’
BEGIN
INSERT INTO fgedu.change_log (table_name, operation, record_id, new_values, change_user, application_name)
SELECT
‘fgedu.products’,
‘INSERT’,
product_id,
(SELECT * FROM inserted WHERE product_id = i.product_id FOR JSON AUTO),
SUSER_NAME(),
APP_NAME()
FROM inserted i;
END
IF @operation = ‘UPDATE’
BEGIN
INSERT INTO fgedu.change_log (table_name, operation, record_id, old_values, new_values, change_user, application_name)
SELECT
‘fgedu.products’,
‘UPDATE’,
i.product_id,
(SELECT * FROM deleted WHERE product_id = i.product_id FOR JSON AUTO),
(SELECT * FROM inserted WHERE product_id = i.product_id FOR JSON AUTO),
SUSER_NAME(),
APP_NAME()
FROM inserted i;
END
IF @operation = ‘DELETE’
BEGIN
INSERT INTO fgedu.change_log (table_name, operation, record_id, old_values, change_user, application_name)
SELECT
‘fgedu.products’,
‘DELETE’,
product_id,
(SELECT * FROM deleted WHERE product_id = d.product_id FOR JSON AUTO),
SUSER_NAME(),
APP_NAME()
FROM deleted d;
END
END;
GO
— 测试审计触发器
INSERT INTO fgedu.products (product_name, category, price, stock)
VALUES (‘测试产品’, ‘测试分类’, 999.00, 10);
GO
UPDATE fgedu.products SET price = 888.00 WHERE product_name = ‘测试产品’;
GO
— 查看审计日志
SELECT log_id, table_name, operation, record_id, change_time, change_user
FROM fgedu.change_log
ORDER BY log_id DESC;
GO
执行结果：
——- —————– ———- ———- ———————- ————
1 fgedu.products INSERT 6 2026-04-08 10:35:00 fgedu
2 fgedu.products UPDATE 6 2026-04-08 10:35:05 fgedu
4.3 SQLServer数据同步触发器案例
数据同步触发器实战：
CREATE TABLE fgedu.daily_sales_summary (
summary_date DATE PRIMARY KEY,
total_orders INT DEFAULT 0,
total_amount DECIMAL(15,2) DEFAULT 0,
avg_order_amount DECIMAL(15,2) DEFAULT 0,
update_time DATETIME DEFAULT GETDATE()
);
GO
— 创建数据同步触发器
CREATE TRIGGER fgedu.trg_sync_sales_summary
ON fgedu.sales_orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @order_date DATE;
— 获取受影响的日期
SELECT @order_date = CAST(order_date AS DATE) FROM inserted;
IF @order_date IS NULL
SELECT @order_date = CAST(order_date AS DATE) FROM deleted;
— 更新汇总表
MERGE fgedu.daily_sales_summary AS target
USING (
SELECT
CAST(order_date AS DATE) AS summary_date,
COUNT(*) AS total_orders,
SUM(order_amount) AS total_amount,
AVG(order_amount) AS avg_order_amount
FROM fgedu.sales_orders
WHERE CAST(order_date AS DATE) = @order_date
GROUP BY CAST(order_date AS DATE)
) AS source
ON target.summary_date = source.summary_date
WHEN MATCHED THEN
UPDATE SET
total_orders = source.total_orders,
total_amount = source.total_amount,
avg_order_amount = source.avg_order_amount,
update_time = GETDATE()
WHEN NOT MATCHED THEN
INSERT (summary_date, total_orders, total_amount, avg_order_amount)
VALUES (source.summary_date, source.total_orders, source.total_amount, source.avg_order_amount);
END;
GO
— 测试数据同步
INSERT INTO fgedu.sales_orders (customer_id, order_amount) VALUES (1, 5000.00);
GO
— 查看汇总数据
SELECT * FROM fgedu.daily_sales_summary;
GO
执行结果：
————- ————- ————- —————– ———————–
2026-04-08 10001 50000000.00 5000.00 2026-04-08 10:40:00
Part05-风哥经验总结与分享
5.1 SQLServer函数最佳实践
- 优先使用内联表值函数替代标量函数
- 避免在WHERE子句中使用标量函数
- 使用SCHEMABINDING选项提高性能
- 避免在函数中执行耗时操作
- 考虑使用计算列替代标量函数
5.2 SQLServer触发器最佳实践
- 保持触发器逻辑简洁高效
- 避免触发器嵌套调用
- 使用SET NOCOUNT ON减少网络流量
- 正确处理inserted和deleted表
- 添加适当的错误处理机制
5.3 SQLServer生产环境注意事项
- 监控触发器执行性能
- 定期检查函数和触发器依赖关系
- 使用版本控制管理代码
- 建立变更管理流程
- 定期审查和优化函数触发器
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
