数据库原理 触发器

实验六触发器

1、指挥部和策划部总人数不超过总员工人数的30% create trigger TR_12 on 员工

for insert

as

declare @num1 int,@num2 int,@id char(13)

set @num1=(select count(*) 指挥部和策划部总人数

from 员工

where 部门编号in(select 部门编号from 部门where 部门名称in ('指挥部','策划部'))) print @num1

select @num2=count(*) from 员工

select @id=员工编号from inserted

if(@num1>@num2*0.3)

begin

delete from 员工where 员工编号=@id

raiserror('不符合标准',7,2)

end

insert into 员工(员工编号,员工姓名,性别,部门编号)

values(2011122400102,'查宁静','女',201)

2.员工编号为：入职日期+部门编号+两位子编号，编写脚本实现，若部门编号变更为四位，员工编号随即改变

create trigger tr_123

on 员工

after update

as

declare @id1 char(3),@id2 char(3)

select @id1=部门编号from deleted

select @id2=部门编号from inserted

if update(部门编号)

begin

update 员工

set 员工编号=substring(员工编号,1,8)+@id2+substring(员工编号,12,2)

where 员工编号like @id1 +'%'

end

insert into 员工(员工姓名,性别,部门编号)

values('査宁静','女',2011)

3.有么有办法实现员工编号的自动输入？

create trigger TR_ggon on 员工

for insert

as

declare @head char(8),@middle char(3),@last char(2),@num int select * into #tb from inserted

set @head=convert(varchar,getdate(),112)

select @num=(count(*)+1) from 员工

where 部门编号in (select 部门编号from inserted)

if(@num<10)

set @last='0'+cast(@num as char(1))

else

set @last=@num

select @middle=部门编号from inserted

update #tb

set 员工编号=@head+@middle+@last

insert into 员工

select *from #tb

delete from #tb