/*
时间：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' )

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