触发器的简单应用

一、定义数据环境：teacher表记录教师情况，sal_log记录teacher表工资修改，添加情况。

create database T
go

use T
go

create table teacher
(
Eno numeric(4) primary key,
EName varchar(8) unique,
pJob varchar(8),
Sal numeric(7,2)
);
go

create table Sal_log
(
eno numeric(4) references teacher(eno),
Sal Numeric(7,2),
uSERNAME CHAR(10),
Date datetime);
go 

二、定义触发器

1.insert_sal触发器，当向teacher表插入新元组时，向sal_log添加一条记录

if exists (select * from sysobjects
            where name='insert_sal' and type='TR')
drop trigger insert_sal

go

--新触发器
Create trigger insert_sal
 on teacher
 for insert as
 set Nocount off
 declare @new_eno numeric(4),
  @new_sal numeric(7,2)
 --if @@rowcount=1
 begin
  select @new_eno=eno,@new_sal=sal from inserted
  insert into sal_log
  values(@new_eno,@new_sal,current_user,current_timestamp)
 end

2.update_sal触发器，当teacher表修改工资时，向sal_log添加修改前和修改后，两条元组。

 Create trigger update_sal
  on teacher
 for update as
        declare @new_sal numeric(7,2),
  @old_sal numeric(7,2),
  @eno numeric(4)
 if @@rowcount=1
 begin
  select @eno=eno,@new_sal=sal from inserted
  select @old_sal=sal from deleted
  if (@new_sal<>@old_sal)
   insert into
   sal_log values(@eno,@old_sal,
   current_user,current_timestamp);
   insert into
   sal_log values(@eno,@new_sal,
   current_user,current_timestamp);
 end

3.定义触发器insert_sal_little，当向teacher添加记录时，检测若是教授，应该把工资最低为4000

if exists (select * from sysobjects
            where name='insert_sal_little' and type='TR')
drop trigger insert_sal_little

go

--新触发器
Create trigger insert_sal_little
 on teacher
 for insert,update as
 set Nocount off
 declare @new_eno numeric(4),
  @new_sal numeric(7,2)
 --if @@rowcount=1
 begin
  select @new_eno=eno,@new_sal=sal from inserted
  if (@new_sal-4000.00<0)
  begin 
   update teacher set sal=4000.00
    where eno=@new_eno
  end
 end

三、检测

--检测inser_salt触发器

insert into teacher values(2112,'Li','教授',6000)
go

select * from teacher
select * from sal_log

--检测update_sal触发器--

update teacher set sal=7300
where eno=2112

select * from teacher
select * from sal_log

--检测inser_sal_littlet触发器

insert into teacher values(2113,'wang','教授',3000)
go

select * from teacher
select * from sal_log