(1) 创建添加员工记录的存储过程EmployeeAdd,并给表Employees插入一条记录。
(2) 创建一个存储过程,要求当一个员工的工作年份大于6年时将其转到经理办公室工作。
(3) 向表Employees插入或修改一个记录时,通过触发器检查记录的DepartmentID值在Departments表中是否存在,若不存在,则取消插入或修改操作。
(4) 创建触发器,当向Salary表中插入记录时,先检查EmployeeID列上的值在Employees中是否存在。如果存在,则执行插入操作;否则,则提示“员工号不存在”。
【实验的sql脚本】
创建添加职员记录的存储过程EmployeeAdd;
create proc insertEM
@Employeeid char(6) ,@Name char(10),@Education char(4),
@Birthday datetime,@Sex bit,@WorkYear tinyint,@Address varchar(40),
@Phonenumber char(12),@DepartmentID char(3)
as
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID)
values( @Employeeid ,@Name ,@Education, @Birthday ,@Sex,@WorkYear ,@Address , @Phonenumber,@DepartmentID)
--给表Employees插入一条记录;
execute insertem '000101','乡干部','大专','1966-01-23 00:00:00.000',1,8,'中山路32-1-508','83355668','2'
创建一个存储过程,要求当一个员工的工作年份大于6年时将其转到经理办公室工作。
create procedure yuangong_infol
@EmployeeID char(6),@name nchar(20)
as
begin
declare @year int set @year=(
select WorkYear from Employees where EmployeeID=@EmployeeID)
declare @DepartmentID char(3) set @DepartmentID=(
select DepartmentID from Departments where DepartmentName=@name)
if (@year>6) update Employees set DepartmentID=@DepartmentID where EmployeeID=@EmployeeID
End
execute dbo.yuangong_infol '000001','经理办公室'
select * from Employees
向表Employees插入或修改一个记录时,通过触发器检查记录的DepartmentID值在Departments表中是否存在,若不存在,则取消插入或修改操作。
create trigger employeeins on employees
for update
as
begin
if((select departmentid from inserted) not in (select departmentid from departments))
rollback
end
select * from DepartMents
select * from Employees
update Employees set departmentid='12' where Employeeid='102201'
alter table employees drop constraint FK_Employees_DepartMents --有外键限制,删除外键
创建INSTEAD OF触发器,当向SALARY表中插入记录时,先检查Employeeid列上的值在Employees中是否存在,如果存在则执行插入操作,否则提示“员工号不存在”。
create trigger em_exists on salary
instead of insert
as
begin
if ((select employeeid from inserted) in (select employeeid from employees))
insert into salary select* from inserted
else
print '该员工ID编号不存在!'
end
select * from Employees
insert into salary values('000000','2000','100')
select * from salary
创建触发器,当Salary表中InCome值增加500时,OutCome值增加50。
create trigger SA_INCOME on salary
for update
as
begin
if(select Income from INSERTED)-(select Income FROM deleted)=500
update salary
set OutCome=OutCome+50
where EmployeeID=(select EmployeeID from INSERTED)
end
select INCOME,OUTCOME from salary where employeeID='000001'
update salary set InCome=InCome+500 where employeeID='000001'
select INCOME,OUTCOME from salary where employeeID='000001'
【实验步骤】
1 安装 SQL Server 2000
1、根据软硬件环境,选择一个合适版本的 SQL Server 2000。
2、利用企业管理器访问系统自带的pubs数据库
(1)启动 SQL Server服务管理器。
(2)以系统管理员身份登录到企业管理器并访问pubS数据库。
3、查询分析器的使用
(1)在查询分析器对象浏览器的目录树中展开pubs数据库图标,则将列出该数据库的所有对象,如表、视图、存储过程等。
(2) 以pubs数据库的用户表anthors为例,选中该表的图标,单击鼠标右键,出现一快捷菜单,执行“打开”菜单项,打开该表,查看其内容。
2 创建数据库和表
(1) 在管理器中创建数据库YGGL
点击数据库,点击‘新建查询’,输入脚本,创建YGGL数据库:
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'YGGL')
DROP DATABASE YGGL
GO
CREATE DATABASE YGGL
go
use yggl
go
--创建表departments
CREATE TABLE [dbo].[DepartMents](
[DepartmentID] [char](3) NOT NULL,
[Departmentname] [char](20) NOT NULL,
[Note] [varbinary](100) NULL,
CONSTRAINT [PK_DepartMents] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门编号,主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DepartMents', @level2type=N'COLUMN',@level2name=N'DepartmentID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DepartMents', @level2type=N'COLUMN',@level2name=N'Departmentname'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DepartMents', @level2type=N'COLUMN',@level2name=N'Note'
GO
--创建表salary
CREATE TABLE [dbo].[Salary](
[EmployeeID] [char](6) NOT NULL,
[InCome] [float] NOT NULL,
[OutCome] [float] NOT NULL,
CONSTRAINT [PK_Salary] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)
) ON [PRIMARY]
go
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工编号,主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Salary', @level2type=N'COLUMN',@level2name=N'EmployeeID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'收入' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Salary', @level2type=N'COLUMN',@level2name=N'InCome'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支出' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Salary', @level2type=N'COLUMN',@level2name=N'OutCome'
GO
--创建表employees
CREATE TABLE [dbo].[Employees](
[Employeeid] [char](6) NOT NULL,
[Name] [char](10) NOT NULL,
[Education] [char](4) NOT NULL,
[Birthday] [datetime] NOT NULL,
[Sex] [bit] NOT NULL CONSTRAINT [DF_Employees_sex] DEFAULT ((1)),
[WorkYear] [tinyint] NULL,
[Address] [varchar](40) NULL,
[Phonenumber] [char](12) NULL,
[DepartmentID] [char](3) NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[Employeeid] ASC
)
) ON [PRIMARY]
go
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工编员,主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Employeeid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学历' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Education'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'出生日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Birthday'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别,默认值为1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Sex'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工作时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'WorkYear'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Address'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'电话号码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Phonenumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工部门号,外键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'DepartmentID'
GO
ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_DepartMents] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[DepartMents] ([DepartmentID])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_DepartMents]
--输入departments表内容
insert departments(DepartmentID,Departmentname,Note) values( '1 ' , '财务部 ' , NULL )
insert departments(DepartmentID,Departmentname,Note) values( '2 ' , '人力资源部 ' , NULL )
insert departments(DepartmentID,Departmentname,Note) values( '3 ' , '经理办公室 ' , NULL )
insert departments(DepartmentID,Departmentname,Note) values( '4 ' , '研发部 ' , NULL )
insert departments(DepartmentID,Departmentname,Note) values( '5 ' , '市场部 ' , NULL )
--输入employees表内容
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '000001' , '王林 ' , '大专' , '1966-01-23 00:00:00.000' , 1 , 8 , '中山路32-1-508' , '83355668 ' , '2 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '010008' , '伍容华 ' , '本科' , '1976-03-28 00:00:00.000' , 1 , 3 , '北京东路100-2' , '83321321 ' , '1 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '020010' , '王向容 ' , '硕士' , '1982-12-09 00:00:00.000' , 1 , 2 , '四牌楼10-0-108' , '83792361 ' , '1 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '020018' , '李丽 ' , '大专' , '1960-07-30 00:00:00.000' , 0 , 6 , '中山东路102-2' , '83413301 ' , '1 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '102201' , '刘明 ' , '本秒' , '1972-10-18 00:00:00.000' , 1 , 3 , '虎距路100-2' , '83606608 ' , '5 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '102208' , '朱俊 ' , '硕士' , '1965-09-28 00:00:00.000' , 1 , 2 , '牌楼巷5-3-106' , '84708817 ' , '3 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '108991' , '钟敏 ' , '硕士' , '1979-08-10 00:00:00.000' , 0 , 4 , '中山路10-3-105' , '83346722 ' , '3 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '111006' , '张石兵 ' , '本科' , '1974-10-01 00:00:00.000' , 1 , 1 , '解放路34-1-203' , '84563418 ' , '5 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '210678' , '林涛 ' , '大专' , '1977-04-02 00:00:00.000' , 1 , 2 , '中山北路24-35' , '83467336 ' , '3 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '302566' , '李玉珉 ' , '本科' , '1968-09-20 00:00:00.000' , 1 , 3 , '热和路209-3' , '58765991 ' , '4 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '308759' , '叶凡 ' , '本科' , '1978-11-18 00:00:00.000' , 1 , 2 , '北京西路3-7-52' , '83308901 ' , '4 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '504209' , '陈林琳 ' , '大专' , '1969-09-03 00:00:00.000' , 0 , 5 , '汉中路120-4-12' , '84468158 ' , '4 ' )
--输入salary表内容
insert salary(EmployeeID,InCome,OutCome) values( '000001' , 2100.8 , 123.09 )
insert salary(EmployeeID,InCome,OutCome) values( '010008' , 1582.62 , 88.03 )
insert salary(EmployeeID,InCome,OutCome) values( '020010' , 2860 , 198 )
insert salary(EmployeeID,InCome,OutCome) values( '020018' , 2347.68 , 180 )
insert salary(EmployeeID,InCome,OutCome) values( '102201' , 2569.88 , 185.65 )
insert salary(EmployeeID,InCome,OutCome) values( '102208' , 1980 , 100 )
insert salary(EmployeeID,InCome,OutCome) values( '108991' , 3259.98 , 281.52 )
insert salary(EmployeeID,InCome,OutCome) values( '111006' , 1987.01 , 79.58 )
insert salary(EmployeeID,InCome,OutCome) values( '210678' , 2240 , 121 )
insert salary(EmployeeID,InCome,OutCome) values( '302566' , 2980.7 , 210.2 )
insert salary(EmployeeID,InCome,OutCome) values( '308759' , 2531.98 , 199.08 )
insert salary(EmployeeID,InCome,OutCome) values( '504209' , 2066.15 , 108 )
创建结果为:
(2)向数据库YGGL表加入数据
点击YGGL数据库,点击‘新建查询’,输入脚本,向数据库YGGL表加入数据:
if exists (select * from sysobjects where xtype='U' and name='sc')
drop table sc;
if exists (select * from sysobjects where xtype='U' and name='course')
drop table course;
if exists (select * from sysobjects where xtype='U' and name='student')
drop table student;
if exists (select * from sysobjects where xtype='U' and name='dept')
drop table dept;
if exists (select * from sysobjects where xtype='U' and name='jdb')
drop table jdb;
go
--create default nullchar as ''
--drop default nullchar
GO
CREATE TABLE Dept (
sdept char (20) NOT NULL ,
pname char (20) NULL
) ON [PRIMARY]
GO
CREATE TABLE Course (
Cno char (4) NOT NULL ,
Cname char (40) NULL ,
Cpno char (4) NULL ,
Ccredit smallint NULL
) ON [PRIMARY]
GO
CREATE TABLE Student (
Sno char (9) NOT NULL ,
Sname char (20) NULL ,
Ssex char (2) NULL ,
Sage smallint NULL ,
Sdept char (20) NULL
) ON [PRIMARY]
GO
CREATE TABLE SC (
Sno char (9) NOT NULL ,
Cno char (4) NOT NULL ,
Grade smallint NULL
) ON [PRIMARY]
GO
ALTER TABLE Dept WITH NOCHECK ADD
CONSTRAINT PK_Dept PRIMARY KEY CLUSTERED
(
sdept
) ON [PRIMARY]
GO
ALTER TABLE Course WITH NOCHECK ADD
CONSTRAINT PK_CNO PRIMARY KEY CLUSTERED
(
Cno
) ON [PRIMARY]
GO
ALTER TABLE Student WITH NOCHECK ADD
CONSTRAINT PK_SNO PRIMARY KEY CLUSTERED
(
Sno
) ON [PRIMARY]
GO
ALTER TABLE SC WITH NOCHECK ADD
CONSTRAINT PK_SCNO PRIMARY KEY CLUSTERED
(
Sno,
Cno
) ON [PRIMARY]
GO
ALTER TABLE Course ADD
UNIQUE NONCLUSTERED
(
Cname
) ON [PRIMARY]
GO
--删除一个约束定义
--ALTER TABLE Student Drop CONSTRAINt DF_Student_Sname
ALTER TABLE Student ADD
CONSTRAINT DF_Student_Sname DEFAULT ('') FOR Sname,
--Drop CONSTRAINt DF_Student_Sname DEFAULT for sname
UNIQUE NONCLUSTERED
(
Sname
) ON [PRIMARY]
GO
setuser
GO
--绑定默认值给表字段
--EXEC sp_bindefault N'nullchar', N'Course.Cname'
--取消绑定
--EXEC sp_unbindefault 'Course.Cname'
GO
setuser
GO
ALTER TABLE Course ADD
CONSTRAINT FK__Course__Cpno FOREIGN KEY
(
Cpno
) REFERENCES Course (
Cno
) NOT FOR REPLICATION
GO
ALTER TABLE Student ADD
CONSTRAINT FK_Student_Dept FOREIGN KEY
(
Sdept
) REFERENCES Dept (
sdept
) ON UPDATE CASCADE
GO
ALTER TABLE SC with nocheck ADD
CONSTRAINT FK_SC_Course FOREIGN KEY
(
Cno
) REFERENCES Course (
Cno
) ON UPDATE CASCADE ,
CONSTRAINT FK_SC_Student FOREIGN KEY
(
Sno
) REFERENCES Student (
Sno
) ON UPDATE CASCADE
alter table Course nocheck constraint FK__Course__Cpno
GO
--表的检查约束
ALTER TABLE SC ADD
CONSTRAINT CK_Grade CHECK (grade>=0 AND Grade<=100)
GO
-- 绩点表;
create table JDB(
jdh int primary key,
jds float ,
xxf numeric(4,1) ,
sxf numeric(4,1) ,
bz char(20)
)
;
insert into jdb
select 1 , 4 , 90 , 100, 'A(优)' union
select 2 , 3.7 , 87 , 89.9, 'A-' union
select 3 , 3.3 , 84 , 86.9 ,'B+' union
select 4 , 3 , 81 , 83.9 ,'B(良)' union
select 5 , 2.7 , 78 , 80.9 ,'B-' union
select 6 , 2.3 , 75 , 77.9 ,'C+' union
select 7 , 2 , 72 , 74.9 ,'C(中)' union
select 8 , 1.7 , 69 , 71.9 ,'C-' union
select 9 , 1.3 , 66 , 68.9 ,'D+' union
select 10 , 1 , 60 , 65.9 ,'D' union
select 12 , 0 , 0 , 59.9 ,'F(不及格)' ;
--插入Dept表数据
insert Dept(sdept,pname) values( 'CS' ,'计算机科学系' )
insert Dept(sdept,pname) values( 'IS' ,'信息工程系' )
insert Dept(sdept,pname) values( 'MA' ,'物理系' )
-- 插入Student表数据;
insert Student(Sno,Sname,Ssex,Sage,Sdept) values('200215121','李勇','男',20,'CS')
insert Student(Sno,Sname,Ssex,Sage,Sdept) values('200215122','刘晨','女',19,'IS')
insert Student(Sno,Sname,Ssex,Sage,Sdept) values('200215123','王敏','女',18,'MA')
insert Student(Sno,Sname,Ssex,Sage,Sdept) values('200215125','张立','男',19,'IS');
-- 插入Course表数据;
insert Course(Cno,Cname,Cpno,Ccredit) values('6','数据处理',NULL,2)
insert Course(Cno,Cname,Cpno,Ccredit) values('2','数学',NULL,2)
insert Course(Cno,Cname,Cpno,Ccredit) values('7','PASCAL语言','6',4)
insert Course(Cno,Cname,Cpno,Ccredit) values('5','数据结构','7',4)
insert Course(Cno,Cname,Cpno,Ccredit) values('1','数据库','5',4)
insert Course(Cno,Cname,Cpno,Ccredit) values('3','信息系统','1',4)
insert Course(Cno,Cname,Cpno,Ccredit) values('4','操作系统','6',3);
-- 插入SC表数据;
insert sc(Sno,Cno,Grade) values('200215121','1',92)
insert sc(Sno,Cno,Grade) values('200215121','2',85)
insert sc(Sno,Cno,Grade) values('200215121','3',88)
insert sc(Sno,Cno,Grade) values('200215122','2',90)
insert sc(Sno,Cno,Grade) values('200215122','3',80);
--insert sc(Sno,Cno,Grade) values('200215125','3',101);
go
if exists(select * from sysobjects where xtype='P' and name='jss')
drop procedure jss;
go
--阶乘
create procedure jss
@n float, --输入参数
@isum float output --输出参数
as
begin
declare @i float
select @i=1,@isum=1
while @i<=@n
begin
set @isum=@isum*@i
set @i=@i+1
end
set @n=100
end
--declare @a float=5 ,@b float=0
--exec jss @a ,@b output
--print @a
-- print @b
go
if exists(select * from sysobjects where xtype='FN' and name='js')
drop function js;
go
create function js
(@n float)
returns float
as
begin
declare @i float,@isum float
select @i=1,@isum=1
while @i<=@n
begin
set @isum=@isum*@i
set @i=@i+1
end
return (@isum)
end
-- print dbo.js(5) --dbo模式名必须加
-- 打印每个学院绩点最高的学生姓名;
go
select pname,sname,y.最高绩点 from
(select dept.sdept, pname ,sname,sc.sno,avg(jds) as 平均绩点
from sc,jdb,student,dept
where student.sno=sc.sno and grade>=xxf and grade<=sxf and
dept.sdept=student.sdept group by sc.sno,dept.sdept,pname,sname) x,
(select sdept,max(平均绩点) as 最高绩点 from student,(select sno,avg(jds) as 平均绩点
from sc,jdb where grade>=xxf and grade<=sxf group by sc.sno) x
where student.sno=x.sno group by sdept) y where x.sdept=y.sdept and x.平均绩点=y.最高绩点;
创建结果为:
3 实验过程
点击YGGL数据库,点击‘新建查询’
(1) 创建添加员工记录的存储过程EmployeeAdd,并给表Employees插入一条记录。
输入以下代码创建存储过程EmployeeAdd:
create proc insertEM
@Employeeid char(6) ,@Name char(10),@Education char(4),
@Birthday datetime,@Sex bit,@WorkYear tinyint,@Address varchar(40),
@Phonenumber char(12),@DepartmentID char(3)
as
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID)
values( @Employeeid ,@Name ,@Education, @Birthday ,@Sex,@WorkYear ,@Address , @Phonenumber,@DepartmentID)
创建结果为:
给表Employees插入一条记录,插入前:
输入代码:
execute insertem '000101','乡干部','大专','1966-01-23 00:00:00.000',1,8,'中山路32-1-508','83355668','2'
插入后:
(2) 创建一个存储过程,要求当一个员工的工作年份大于6年时将其转到经理办公室工作。
输入以下代码创建存储过程yuangong_infol:
create procedure yuangong_infol
@EmployeeID char(6),@name nchar(20)
as
begin
declare @year int set @year=(
select WorkYear from Employees where EmployeeID=@EmployeeID)
declare @DepartmentID char(3) set @DepartmentID=(
select DepartmentID from Departments where DepartmentName=@name)
if (@year>6) update Employees set DepartmentID=@DepartmentID where EmployeeID=@EmployeeID
End
创建结果为:
输入以下代码查询创建结果:
创建前:
创建后:
(3) 向表Employees插入或修改一个记录时,通过触发器检查记录的DepartmentID值在Departments表中是否存在,若不存在,则取消插入或修改操作。
输入以下代码创建存储过程:
create trigger employeeins on employees
for update
as
begin
if((select departmentid from inserted) not in (select departmentid from departments))
rollback
end
创建结果为:
输入代码查询DepartMents 的DepartmentID值:
select * from DepartMents
输入代码查询修改前的值:
select * from Employees
输入代码修改DepartmentID值:
update Employees set departmentid='12' where Employeeid='102201'
因为有外键限制,修改出错,输入代码删除外键:
alter table employees drop constraint FK_Employees_DepartMents --删除外键
再次修改DepartmentID值,结果为:
(4) 创建触发器,当向Salary表中插入记录时,先检查EmployeeID列上的值在Employees中是否存在。如果存在,则执行插入操作;否则,则提示“员工号不存在”。
输入代码查询EmployeeID列上的值:
输入以下代码创建INSTEAD OF触发器:
create trigger em_exists on salary
instead of insert
as
begin
if ((select employeeid from inserted) in (select employeeid from employees))
insert into salary select* from inserted
else
print '该员工ID编号不存在!'
end
创建结果为:
向Salary表中插入记录:
insert into salary values('000000','2000','100')
(5) 创建触发器,当Salary表中InCome值增加500时,OutCome值增加50。
输入以下代码创建触发器:
create trigger SA_INCOME on salary
for update
as
begin
if(select Income from INSERTED)-(select Income FROM deleted)=500
update salary
set OutCome=OutCome+50
where EmployeeID=(select EmployeeID from INSERTED)
end
创建结果为:
查询修改前的salary:
select INCOME,OUTCOME from salary where employeeID='000001'
输入代码进行修改:
update salary set InCome=InCome+500 where employeeID='000001'
查询修改后的结果:
select INCOME,OUTCOME from salary where employeeID='000001'