存储过程的使用方法\触发器的使用方法

pitytheriver

于 2023-09-23 17:05:38 发布

阅读量337

点赞数 1

文章标签：数据库网络 java sql

本文链接：https://blog.csdn.net/qq_64077978/article/details/133210639

版权

(1) 创建添加员工记录的存储过程EmployeeAdd，并给表Employees插入一条记录。

(2) 创建一个存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室工作。

(3) 向表Employees插入或修改一个记录时，通过触发器检查记录的DepartmentID值在Departments表中是否存在，若不存在，则取消插入或修改操作。

(4) 创建触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在Employees中是否存在。如果存在，则执行插入操作；否则，则提示“员工号不存在”。

(5) 创建触发器，当Salary表中InCome值增加500时，OutCome值增加50。

【实验的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)

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)

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

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

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

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

CREATE DATABASE YGGL

use yggl

--创建表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]

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'

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'

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'

--创建表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]

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'

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'

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'

--创建表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]

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'

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'

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'

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'

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'

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'

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'

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'

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'

ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_DepartMents] FOREIGN KEY([DepartmentID])

REFERENCES [dbo].[DepartMents] ([DepartmentID])

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;

--create default nullchar as ''

--drop default nullchar

CREATE TABLE Dept (

sdept char (20) NOT NULL ,

pname char (20) NULL

) ON [PRIMARY]

CREATE TABLE Course (

Cno char (4) NOT NULL ,

Cname char (40) NULL ,

Cpno char (4) NULL ,

Ccredit smallint NULL

) ON [PRIMARY]

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]

CREATE TABLE SC (

Sno char (9) NOT NULL ,

Cno char (4) NOT NULL ,

Grade smallint NULL

) ON [PRIMARY]

ALTER TABLE Dept WITH NOCHECK ADD

CONSTRAINT PK_Dept PRIMARY KEY CLUSTERED

(

sdept

) ON [PRIMARY]

ALTER TABLE Course WITH NOCHECK ADD

CONSTRAINT PK_CNO PRIMARY KEY CLUSTERED

(

Cno

) ON [PRIMARY]

ALTER TABLE Student WITH NOCHECK ADD

CONSTRAINT PK_SNO PRIMARY KEY CLUSTERED

(

Sno

) ON [PRIMARY]

ALTER TABLE SC WITH NOCHECK ADD

CONSTRAINT PK_SCNO PRIMARY KEY CLUSTERED

(

Sno,

Cno

) ON [PRIMARY]

ALTER TABLE Course ADD

UNIQUE NONCLUSTERED

(

Cname

) ON [PRIMARY]

--删除一个约束定义

--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]

setuser

--绑定默认值给表字段

--EXEC sp_bindefault N'nullchar', N'Course.Cname'

--取消绑定

--EXEC sp_unbindefault 'Course.Cname'

setuser

ALTER TABLE Course ADD

CONSTRAINT FK__Course__Cpno FOREIGN KEY

(

Cpno

) REFERENCES Course (

Cno

) NOT FOR REPLICATION

ALTER TABLE Student ADD

CONSTRAINT FK_Student_Dept FOREIGN KEY

(

Sdept

) REFERENCES Dept (

sdept

) ON UPDATE CASCADE

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

--表的检查约束

ALTER TABLE SC ADD

CONSTRAINT CK_Grade CHECK (grade>=0 AND Grade<=100)

-- 绩点表;

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);

if exists(select * from sysobjects where xtype='P' and name='jss')

drop procedure jss;

--阶乘

create procedure jss

@n float, --输入参数

@isum float output --输出参数

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

if exists(select * from sysobjects where xtype='FN' and name='js')

drop function js;

create function js

(@n float)

returns float

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模式名必须加

-- 打印每个学院绩点最高的学生姓名;

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)

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)

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

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

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

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'

pitytheriver

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
存储过程的使用方法\触发器的使用方法

安装sql sever，创建数据库和表，增删改查语句的使用和实例
复制链接

扫一扫

存储过程的使用方法\触发器的使用方法

【实验的sql脚本】

【实验步骤】

1 安装 SQL Server 2000

2 创建数据库和表

(1) 在管理器中创建数据库YGGL

(2)向数据库YGGL表加入数据

3 实验过程

(1) 创建添加员工记录的存储过程EmployeeAdd，并给表Employees插入一条记录。

(2) 创建一个存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室工作。

(3) 向表Employees插入或修改一个记录时，通过触发器检查记录的DepartmentID值在Departments表中是否存在，若不存在，则取消插入或修改操作。

(4) 创建触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在Employees中是否存在。如果存在，则执行插入操作；否则，则提示“员工号不存在”。

(5) 创建触发器，当Salary表中InCome值增加500时，OutCome值增加50。

“相关推荐”对你有帮助么？