触发器的简单应用

最新推荐文章于 2023-01-13 18:26:27 发布

最新推荐文章于 2023-01-13 18:26:27 发布 · 119 阅读

文章标签：

本文介绍了一个具体案例，展示了如何使用SQL触发器来记录教师表中工资的变化，并确保教授的最低工资标准。通过创建不同的触发器，如insert_sal、update_sal和insert_sal_little，实现了自动记录工资变动和设置工资下限等功能。

一、定义数据环境：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

--新触发器
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

--新触发器
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