现在的位置: 首页 > 综合 > 正文

T-SQL数据库 建立触发器

2013年10月10日 ⁄ 综合 ⁄ 共 1020字 ⁄ 字号 评论关闭
--触发器
create trigger  IsLaterAdvance
ON dbo.StaffRecord
after insert
as
begin
  declare @timemorning time(0)='08:00:00';
  declare @timeafternoon time(0)='18:00:00';
  declare @afternoon time(0);
  declare @morning time(0);
  set @morning= (select Rstartwork from inserted);
  set @afternoon= (select Rendwork from inserted);
  if @morning>@timemorning 
    update dbo.StaffRecord set Rislater=1;
   if @afternoon<@timeafternoon
    update dbo.StaffRecord set Risinadvance=1;
 end
go

create trigger DepartCount --注意修改Snum的初始值为0部门人数触发器
on dbo.StaffInfo
after insert
as
 begin 
	declare @depart varchar(20);
	declare @num int;--用户读取部门人数
	set @depart = (select Ssdept from inserted);
	set @num =(select Snum from dbo.department where Sdept=@depart);
	set @num= (select(@num+1));
	update dbo.department set Snum=@num;
 end
 go
 
create trigger DepartCountDelte --注意修改Snum的初始值为0部门人数触发器
on dbo.StaffInfo
after delete
as
 begin 
	declare @depart varchar(20);
	declare @num int;--用户读取部门人数
	set @depart = (select Ssdept from deleted);--如果这里变成了insert,那么@num会一直为NULL
	set @num =(select Snum from dbo.department where Sdept=@depart);
	set @num= (select(@num-1));
	update dbo.department set Snum=@num;
 end
 go
 

抱歉!评论已关闭.