触发器的简单应用
一、定义数据环境：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