案例分享 | SQL Server触发器的简单应用(上)

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
简介: SQL数据库开发

任务需求

有如下四张表:

出勤

80.jpg

81.jpg

组类别

82.jpg

配置

83.jpg

1.更新[出勤_上班时长] 如果:"出勤"表,[出勤_上班时间]或者[出勤_下班时间],列发生改变所触发事件

  • 更新上述两列 "出勤"表,出勤_上班时长 = 出勤_下班时间 - 出勤_上班时间
  • 插入上述两列 "出勤"表,出勤_上班时长不插数据,插入完成后计算它。出勤_上班时长 = 出勤_下班时间 - 出勤_上班时间  


2.插入 如果:"出勤"表,[出勤_日期],列发生改变所触发事件

插入 (配置_日期,组_名,组类别_名,组_号,组类别_号)

查询[a.出勤_日期,b.组_名,c.组类别_名,a.组_号,c.组类别_号]


创建表结构

根据给定的表结构,我们创建到数据库中

/*
时间:2018-12-26
作者:Lyven
需求:创建一个触发器,完成相应的更新和插入功能
*/
Use SQL_Road
CREATE TABLE 出勤
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
出勤_月份 INT ,
出勤_日期 INT ,
出勤_上班时间 VARCHAR(20),
出勤_下班时间 VARCHAR(20),
出勤_上班时长 VARCHAR(20),
组_号 VARCHAR(10)
)
CREATE TABLE 组
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
组_号 VARCHAR(10),
组_名 NVARCHAR(20),
组类别_号 VARCHAR(10),
组_人数 INT
)
CREATE TABLE 组类别
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
组类别_号 VARCHAR(10),
组类别_名 NVARCHAR(20),
组类别_时薪 NUMERIC(18,2)
)
CREATE TABLE 配置
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
配置_日期 INT,
组_名 VARCHAR(20),
组类别_名 NVARCHAR(20),
配置_工时 VARCHAR(20),
配置_工资 NUMERIC(18,2),
组_号 VARCHAR(10),
组类别_号 VARCHAR(10)
)
GO


插入测试数据

INSERT INTO 出勤(出勤_月份,出勤_日期,出勤_上班时间,出勤_下班时间,组_号)
VALUES
( 1, 12, 24, '7:30', '12:35', '01' ),
( 2, 12, 25, '8:00', '12:28', '01' ),
( 3, 12, 26, '8:30', '12:00', '01' )
INSERT INTO 组(组_号,组_名,组类别_号,组_人数)
VALUES
( '01', 'CAD', '01', 2 ),
( '02', 'MAX', '02', 1 ),
( '03', 'U3D', '03', 3 )
INSERT INTO 组类别(组类别_号,组类别_名,组类别_时薪)
VALUES
( '01', N'自动', 100.00 ),
( '02', N'员工', 200.00 ),
( '03', N'学员', 150.00 )
INSERT INTO 配置(配置_日期 , 组_名, 组类别_名, 配置_工资 ,
组_号, 组类别_号)
VALUES
( 24, 'CAD', N'自动', 12.50, '01', '01' ),
( 25, 'MAX', N'员工', 12.60, '02', '02' ),
( 26, 'U3D', N'学员', 12.70, '03', '03' )


分析需求

  1. 第一个需求其实是只要上班时间和下班时间,我们就自动给它算出这个时长,其实这样的需求在插入的时候就可以解决,这里我们不讨论这种优化方案,只是根据这个需求看该如何写出这个触发器。
  2. 第二个需求则是在日期发生变动的时候,需要对配置表插入一条数据

这样我们可以把这两个需求写在一个触发器当中。


需求代码

CREATE TRIGGER T_出勤  --创建 触发器
ON 出勤
AFTER UPDATE,INSERT  
--一个触发器可以同时写更新插入和删除等动作
AS
BEGIN
--定义变量
DECLARE @ID INT;
DECLARE @出勤_上班时间 VARCHAR(20);
DECLARE @出勤_下班时间 VARCHAR(20);  
DECLARE @出勤_日期 INT;
--更新  出勤_上班时长
IF (UPDATE (出勤_上班时间) OR UPDATE (出勤_下班时间) )
--如果出勤_上班时间和出勤_下班时间发生了更新动作,则执行如下代码
BEGIN
--先获取更新后的值保留在变量中,其中inserted表为系统表,存放更新后的值
 SELECT
 @ID=ID,
 @出勤_上班时间=出勤_上班时间,
 @出勤_下班时间=出勤_下班时间
 FROM inserted;
--将变量传入到表中,使取到的值唯一,对出勤_上班时长进行更新
UPDATE 出勤 SET 出勤_上班时长=
CONVERT(varchar(100) , DATEADD(ss, DATEDIFF(ss, 出勤_上班时间, 出勤_下班时间), 0), 108)
WHERE ID=@ID
AND (出勤_上班时间=@出勤_上班时间
OR 出勤_下班时间=@出勤_下班时间);
END
--插入配置信息
IF UPDATE (出勤_日期)
--当出勤_日期发生了变动,我们执行如下更新。
BEGIN
--获取更新后的值传给变量
 SELECT
 @ID=ID ,
 @出勤_日期=出勤_日期
 FROM inserted;
 --执行插入操作
INSERT INTO  配置(配置_日期,组_名,组类别_名,组_号,组类别_号)
 SELECT
 a.出勤_日期,b.组_名,c.组类别_名,a.组_号,c.组类别_号
 FROM 出勤 a
 JOIN 组 b ON a.组_号 = b.组_号
 JOIN 组类别 c ON b.组类别_号 = c.组类别_号
 WHERE a.ID=@ID
 AND  a.出勤_日期=@出勤_日期  
END  
END

代码解读

1、触发器的语法这个必须掌握,本案例是在SQL Server下执行的,其他关系数据库的语法可能不同,请注意一下。

2、触发器中可以实现多种不同的操作,更新,删除,插入均可写在一个触发器上,当然要视情况而定

3、触发器在执行时会将更新前的数据存放在临时表deleted中,在更新后会将数据存放在临时表inserted中,这里我们就用到了临时表inserted

4、在更新上班时长时用到了时间处理函数DATEDIFF和DATEADD,两个函数是比较常用的时间处理函数,必须掌握。

5、参数传递是代码中比较重要一环,我们是先将临时表中的数据存放在一个变量中保存,在我们真正进行更新或插入操作时候再把这个变量取出来使用,就是将变量再次传递给条件语句。




相关文章
|
6月前
|
SQL 数据库 数据安全/隐私保护
数据库数据恢复——sql server数据库被加密的数据恢复案例
SQL server数据库数据故障: SQL server数据库被加密,无法使用。 数据库MDF、LDF、log日志文件名字被篡改。 数据库备份被加密,文件名字被篡改。
|
SQL 数据库
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
SQL Server附加数据库出现错误823,附加数据库失败。数据库没有备份,无法通过备份恢复数据库。 SQL Server数据库出现823错误的可能原因有:数据库物理页面损坏、数据库物理页面校验值损坏导致无法识别该页面、断电或者文件系统问题导致页面丢失。
253 13
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
|
6月前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
8月前
|
SQL 数据库
数据库数据恢复—SQL Server报错“错误 823”的数据恢复案例
SQL Server数据库附加数据库过程中比较常见的报错是“错误 823”,附加数据库失败。 如果数据库有备份则只需还原备份即可。但是如果没有备份,备份时间太久,或者其他原因导致备份不可用,那么就需要通过专业手段对数据库进行数据恢复。
|
11月前
|
SQL 数据库
gbase 8a 数据库 SQL优化案例-关联顺序优化
gbase 8a 数据库 SQL优化案例-关联顺序优化
|
11月前
|
SQL 数据库 UED
SQL性能提升秘籍:5步优化法与10个实战案例
在数据库管理和应用开发中,SQL查询的性能优化至关重要。高效的SQL查询不仅可以提高应用的响应速度,还能降低服务器负载,提升用户体验。本文将分享SQL优化的五大步骤和十个实战案例,帮助构建高效、稳定的数据库应用。
853 3
|
11月前
|
SQL 缓存 监控
SQL性能提升指南:五大优化策略与十个实战案例
在数据库性能优化的世界里,SQL优化是提升查询效率的关键。一个高效的SQL查询可以显著减少数据库的负载,提高应用响应速度,甚至影响整个系统的稳定性和扩展性。本文将介绍SQL优化的五大步骤,并结合十个实战案例,为你提供一份详尽的性能提升指南。
899 0
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
12月前
|
SQL 大数据 API
大数据-132 - Flink SQL 基本介绍 与 HelloWorld案例
大数据-132 - Flink SQL 基本介绍 与 HelloWorld案例
224 0
|
SQL 存储 数据管理
深入理解SQL中的触发器
【8月更文挑战第31天】
491 0

热门文章

最新文章