今天在写sql触发器时遇到这样一个问题:
利用完整性规则创建教师表,包括(教师工号,姓名,性别,职称,工资),在此基础上定义触发器,为教师表Teacher定义完整性规则“教授的工资不能低于4000元,如果低于4000元,自动改为4000元”。
教师表:
create table teacher(
tno char(9) primary key,
tname varchar(15),
tsex char(2),
job varchar(20),
tmoney int
)
错误代码:
在触发器中不能写形如inserted.xx的格式,正确写法是用select选出要使用的变量。可以插入多条语句,但是插入的时候是一条一条插的,所以执行时刻inserted表内应该只有一条语句。
正确写法:
create trigger tri2 on teacher
instead of insert
as
if ((select tmoney from inserted) < 4000 and (select job from inserted) = '教授')
insert into teacher values((select tno from inserted), (select tname from inserted), (select tsex from inserted), '教授', 4000)
else
insert into teacher select * from inserted
也可以用变量:
create trigger tri
on teacher
after insert,update
as
begin
declare @salary int, @jobb varchar(10)
set @salary=(select tmoney from inserted)
set @jobb = (select job from inserted)
if(@salary<4000 and @jobb='教授')
update teacher set tmoney=4000 where tno=(select tno from inserted)
end
还可以索性这样写:
create trigger tri3 on teacher
for insert, update
as
update teacher
set tmoney = 4000
where job = '教授' and tmoney < 4000
定义触发器,当对表SC的Grade属性进行修改时,则将此次操作记录到另一个表SC_U(Sno、Cno、Oldgrade、Newgrade)中,其中Oldgrade是修改前的分数,Newgrade 是修改后的分数。
create trigger tri
on sc
after update
as
if update(grade)
begin
create table sc_u(
sno char(9),
cno char(1),
oldgrade int,
newgrade int
)
insert into sc_u(sno, cno, oldgrade, newgrade)values
((select sno from deleted), (select cno from deleted), (select grade from deleted), (select grade from inserted))
end
参考博客:
instead of 和 after触发器的区别: