数据库之SQL Server 触发器

最近在做一个人事管理系统写了几个简单的触发器

1.在删除员工信息表中员工信息时结果区提示被删除员工信息

create trigger test on 员工信息表 after delete as select 工号 as 被删除员工工号,姓名,电话 from deleted go

2.在删除信息时比如开除员工在其他表中也删除对应员工信息

create trigger yuan_delete on 员工信息表 after delete as declare @工号 char(10) select @工号=工号 from deleted delete from 签约信息表 where 工号=@工号 delete from 就职信息表 where 工号=@工号 delete from 学历信息表 where 工号=@工号 go

3.删除员工信息后添加员工信息到对应的离职信息表

CREATE TRIGGER yuan_lizhi ON 员工信息表 AFTER DELETE AS declare @工号 char(10) select @工号=工号 from deleted INSERT INTO 离职信息表 values (@工号,CONVERT(varchar,GETDATE(),120) ,(select 姓名 from deleted),(select 性别 from deleted),(select 电话 from deleted),(select 出生日期 from deleted),(select 住址 from deleted),(select 部门编号 from 就职信息表 where 工号=@工号),(select 职位编号 from 就职信息表 where 工号=@工号)) GO

4.根据员工的加班天数矿工天数确定工资

CREATE TRIGGER test2 ON 考核记录表 AFTER INSERT AS declare @a char(10),@b bigint,@c bigint,@d char(10),@e varchar(10),@date date,@salary1 int,@salary2 int,@salary3 int,@sa int begin set @b=3000000001 set @c=@b+(SELECT COUNT(*) FROM 发薪记录表) set @a=cast(@c as char(10)) set @e=(CONVERT(varchar(10),GETDATE(),120)) set @date=cast(@e as varchar(30)) set @d=(select 工号 from inserted) set @sa=(select 底薪 from 员工底薪表 where @d=工号) set @salary1= (select 旷工天数 from inserted)*100 set @salary2=(select 加班天数 from inserted)*50 set @salary3=(@sa)+@salary2-@salary1 insert into 发薪记录表 values(@a,@date,@d,@sa,@salary2,@salary1,@salary3) end GO