数据库存储过程和触发器

存储过程

存储过程的相关知识

定义：由sql语句编写完成后，编译器优化后存储在数据库的服务器中，使用后就可以调用（我自己理解的时相当于编程语言中的预处理，或者是提前封装号的函数）

数据库的完整性包括：实体完整性、参照完整性、用户定义完整性、触发器

存储过程的优势：

①增强了代码的复用率和共享性（一次编译多次使用）；

②提高了系统的性能（预先编译和优化好）；

③减少了网络流量（服务端执行，只向客户端返回结果）；

④可以作为安全机制使用（只允许用户执行部分权限，不允许用户直接访问数据库对象）

存储过程包括：系统存储过程和本地存储过程。

系统存储过程：存储在master数据库中，其中前缀是sp_

eg:sp_hlep  ,  sp_hlepindex  ,  sp_rename  , sp_dbremove

创建存储过程的方式包括：1、直接运用鼠标创建  2、运用T-SQL语句创建（我们这里主要将这个）

注意事项：

存储过程不能够超过128MB；

用户定义的存储过程只能在当前数据库中创建；

create procedure必须是批处理的第一条语句；

SQL sever允许存储过程引用一个不存在的对象，它只检测语发问题。

---

创建存储过程

上代码

执行存储过程使用T-SQL 语中的EXECUTE命令。如果执行存储过程是批处理中的第一条语句，则可以不使用EXECUTE关键字。 注意： 对于存储过程的所有者或任何一名对此过程拥有EXECUTE权限的用户，都可以执行此存储过程。 输入参数在存储过程名后逐一给定， 用逗号隔开，不必使用括号。 如果没有使用@参数名= value这种方式传入值，则参数的排列必须和建立存储过程所定义的次序对应。 用来接受输出值的参数必须加上OUTPUT

题目：创建无参存储过程，查询每个学生的平均成绩

最后不要忘记执行存储过程哦！（execute stu_avg）

group by要放在最后！！！！

create procedure stu_avg
as
selecet s#,avg(score) as '平均成绩' 
from sc
group by s#

execute stu_avg

题目：创建存储过程，查询某个学生的平均成绩(有参数传入)

create procedure stu_avg2 @sno char(7)
as
select avg(score)
from sc
where s#=@sno

execute stu_avg2 '2019001'

题目：修改某个学生某门课的成绩

create procedure update_score @sno char(7),@cno char(3),@score int 
as
update sc set score=@score 
where s#=@sno add c#=cno

execute update_score '2019001','001',100

题目：创建加密过程，利用sp_helptext查看其内容

创建加密的存储过程只需要在后面加上with encryption

create procedure Encrypt_s with encryption
as
select *from student

execute sp_helptext Encrypt_s

题目：创建带有参数和默认值（通配符）的存储过程，返回指定的学生（提供姓名）的信息。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则返回所有学生的信息。

注意：

这里需要给参数一个默认值；

同时后面的查询是模糊查询，所以需要用到like

create procedure student_name @sname varchar(40)='%'
as
select * from student 
where sname like @sname

execute student_name 
execute student_name '张三' 

题目：创建带OUTPUT参数的存储过程，用于计算指定课程的平均成绩，使用一个输入参数（课程号）和一个输出参数（平均成绩）。

注意：

因为有一个输出参数output,所以在参数命名时要在后面加上output

同时因为是输出参数，所以在执行这个存储过程的时候需要声明这个输出参数

create procedure avgscore2 @cno char(3),@avgs float output
as
select @avgscore=avg(score)
from sc
where c#=@cno

declare @avgs float
execute avgscore2 '001',@avgs output
select @avgs

查看、修改和删除存储过程

题目：修改存储过程student_avg，除了用于计算每个学生的平均成绩外，还要计算学生的最高分

alter procedure student_avg
as 
select s#,avgs(score),max(score)
from sc
group by s#

execute student_avg

题目：删除存储过程student_avg

drop procedure student_avg

---

触发器trigger

触发器相关的知识

定义：触发器也是一种存储过程，是一种特殊类型的存储过程。他是定义在关系表上的一类由事件驱动的过程。

完整性和安全性的区分:

①完整性：

对象：不合语义、不正确的数据

作用：阻止合法用户通过合法操作向数据库中加入不正确的数据

②安全性：

对象：非法用户和非法数据

作用：非法用户和非法操作存取数据库中的正确数据

触发器的作用：保持数据的完整性，而不是返回查询的结果

触发器的具体作用：

①强化约束

②使用自定义的错误提示信息

③实现数据库中多表的级联操作

④跟踪变化

⑤调用存储过程

触发器的分类：

第一种分类方式：

DML触发器：insert、update、delete

DDL触发器：drop、alter、create

第二种分类方式：

after触发器：记录变化后激活，用于记录变更后的处理或者检查

instead of 触发器：用于取代原本的操作，在记录变更前触发

创建触发器应该注意的问题

①create trigger语句必须要是批处理中的第一个语句；而且只能用于一个表或者视图

②创建触发器的权限默认为表的所有者，不能转让权限

③触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建

④触发器可以引用临时表，但是不能够创建在临时表上面

⑤truncate  table语句不会激发delete触发器

---

创建触发器

上代码！

题目：为学生表创建一个简单DML触发器，在插入和修改数据时，都会自动显示提示信息。

注意数据是怎么插入的insert into....values()

create trigger reminder
on student
for insert,update
as print '你在插入或修改学生表的数据' 


insert into student values('2018005','李飞','男',20,'01')

题目：为学生表创建一个DML触发器，在插入和修改数据时，都会自动显示所有学生的信息。

create trigger print_table 
on student
for
insert,update 
as
select *from student


update student set sage=30 
where s#='2019001'

题目：在SC表上创建一个DELETE类型的触发器，删除数据时，显示删除记录的个数。

create trigger del_count
on sc
for delete
as
declare @count varchar(50)
set @count=str(@@rowcount)+'行记录被删除'
select @count
return 


delete sc where s#='2019002'

创建涉及inserted表和deleted表的触发器

什么是inserted表和deleted表？

 SQL Server 为每个DML触发器都定义了两个特殊的表，一个是插入表:Inserted，一个是删除表:Deleted。     它们建在数据库服务器的内存中，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。     在触发器的执行过程中，SQL Server建立和管理这两个临时表。这两个表的结构与触发器所在数据表的结构是完全一致的，包含了在激发触发器的操作中插入或删除的所有记录。     当触发器的工作完成之后，这两个表也将会从内存中删除。

创建这类触发器须知：

① 在用户执行INSERT语句时，所有被添加的记录都会存储在Inserted表以及触发器所在的表中； ② 在用户执行DELETE语句时，从触发器所在的表中被删除的行会发送到Deleted表；    

③而对于UPDATE语句，SQL Server先将要进行修改的记录行存储到Deleted表中，然后再将修改后的数据行存储到Inserted表以及触发器所在的表中。

题目：为Major表创建一个名为d_tr的触发器，当执行增、删、改操作时，激活该触发器，查看Inserted表和Deleted表里的信息

create trigger d_tr
on major
for insert,update,delete
as
select * from deleted
select * from inserted


DELETE FROM major where  m#='06'
INSERT INTO Major  VALUES('07','大数据','AA')
UPDATE major SET dean='张三' WHERE M#='01'

题目：为教师表定义完整性规则：教师的工资不得低于4000元，如果低于4000元，自动改为4000元。

create trigger trig6 
on teacher
for update,insert
as
update teacher set salary=4000
from teacher t,insert i
where t.T#=i.T# add i.salary<4000


update teacher set salary=3900 where T#='001'

题目：在student表中增加一个属性Sumsc,Sumsc为该学生已选修课程的门数，初始值为其选课门数，以后每选修一门课程都要对其加1，设计一个触发器自动完成上述功能。

update student set sumsc=
(select count(*) from sc where sc.s#=student.s#)
go


create trigger trig1 
on sc
after insert
as
update student set sumsc=sumsc+1
where s#=(select s# from inserted)
select sumsc from student 
where s#=(select s# from inserted)

题目：SC与STUDENT有外码关系，创建一个实现级联删除的触发器。当删除student表中的某个学生时，先删除SC表中该学生的选课记录，再删除student表中的该学生

create trigger trig2 
on student
instead of delete
as
delete sc where s# in
(select s# from deleted)
delete student where s# 
in(select s# from delectd)

题目：设计一个触发器，教师的工资只能升不能降

create trigger change_sala1
on teacher 
for update
as
declare @newsalary int ,@oldsalary int
select @newsalary=inerted.salary,@oldsalary=deleted.salary
from insert,delete
if @newsalary<@oldsalary
begin 
    print '工资只能升不能降'
    rollback
end




update teacher set salary=7500 where tname='张三'

题目：在教师工资表上创建一个触发器：在对教师的工资进行录入和修改时，按职称级别进行约束。

create trigger tr1 
on teacher
for insret,update
as
declare @prof char(10),@min int,@max int ,@salary int
select @prof=i.prof,@salary=i.salary,@min=s.minsalary,@max=s.maxsalary
from salary s,insert i
where s.prof=i.prof
if @salary not between @min and @max
begin
    print  @prof+'工资应该在'+str(@min)+'到'+str(@max)+'之间'
    rollback
end



insert teacher valus('010’, '张三','教授',6000)

查看和修改触发器

查看触发器

1、在SQL Server Management Studio中查看触发器 在选择相应触发器名后，在“查询编辑器窗口”查看。

2、使用系统存储过程查看触发器 系统存储过程sp_help、sp_helptext和sp_helptrigger分别提供有关触发器的不同信息。

修改触发器

例题：修改教学库中的学生表上的触发器reminder，使得在用户执行添加或修改操作时，自动给出错误提示信息，并撤销此次操作。

ALTER TRIGGER reminder
ON student
INSTEAD OF INSERT , UPDATE  
AS print  '你执行的添加或修改操作无效！ 




update student set sname='张三' 

上述知识仅是个人学习笔记分享，如有错误欢迎提出更正😊