数据库 触发器基础用法（备份删除行+删除多行）
时间：2021-07-01 10:21:17
帮助过：146人阅读
--
触发器特殊的地方就在于，触发器是通过对数据库表的操作，来引发
--
存储过程是通过人为exec来执行
select *
from Student
create trigger Student_Insert --
创建触发器
on Student --
指定触发器所在的表，还可以写成after，for和after都是在操作后执行
for Insert --
当执行insert操作的时候自动执行触发器
as
update Student set sbirthday=
‘1980-08-20‘where Sno=
110
go
insert into Student values(110,
‘王二小‘,
‘男‘,
‘1979-09-02‘,
95033)
create trigger Student_Delete --
创建触发器
on Student
instead of delete --
替换某个操作，用触发器的过程来替换删除的操作，不在能够删除student表
as
update Student set sbirthday=
‘1980-08-20‘where Sno=
110
go
delete from Student
drop trigger Student_Delete --
删除触发器
create trigger Student_Delete2 --
删除某一条数据
on Student
instead of delete
as
delete from Score
where Sno=
105
delete from Student
where Sno=
105
go
delete from Student
delete from Course
where Cno=
‘3-105‘
create trigger Course_Delete --course表中的cno有主外键关系--
deleted触发器中的临时表
on Course
instead of delete
as
select *
from deleted --
deleted相当于虚拟的临时表，里面存着你要删除的那些数据
go
delete from Course
where Cno=
‘3-105‘
drop trigger Course_Delete --
删除触发器
--
一次性删除Course表数据，实用触发器替换删除操作，线删除外键表相关数据，再删除主键表course表相关数据
create trigger Course_Delete2--
只能删除一行，要是删除多行由于赋值的问题遍历后只把最后一个数赋值执行，所以只删除了最后一行
on Course
instead of delete
as
declare @cno varchar(20)
select @cno =
cno
delete from score
where cno=
@cno
delete from course
where cno=
@cno
go
create trigger Course_Delete4--
一条一条删除多行
on Course
instead of delete
as
delete from score
where cno
in (selete cno
from deleted)
delete from course
where cno
in (selete cno
from deleted)
go
create trigger Course_Delete3
on Course
instead of delete
as
declare @count int
select @coount = count(*)
from deleted
if @count==
1 --
执行删除一行的操作
begin
declare @cno varchar(20)
select @cno =
cno
delete from score
where cno=
@cno
delete from course
where cno=
@cno
end
if @count>
1 --
循环遍历每一行，分别执行删除一行的操作
begin
delete from score
where cno
in (selete cno
from deleted)
delete from course
where cno
in (selete cno
from deleted)
end
go
drop table Scorebak
drop trigger Score_Delete
--
删除前先备份数据到备份表之后再删除
create table Scorebak
(
ids int identity(
1,
1) primary key,
sno varchar(50),
cno varchar(50),
degree decimal(
4,
1),
dayetime datetime
)
create trigger Score_Delete --
备份删除
on Score
instead of delete
as
declare @count int
select @count = count(*)
from deleted
declare @i int
set @i =
0
while @i<@count --
循环遍历deleted临时表的数据,然后转移到Scorebak
begin
declare @sno varchar(20)
declare @cno varchar(20)
declare @degree decimal(
4,
1)
select top
1 @sno=sno,@cno=cno,@degree=degree
from deleted
where sno not
in (
select top (@i) sno
from deleted) or cno not
in(
select top (@i) cno
from deleted)
--or degree not
in (
select top(@i) degree
from score)--两条就能确定时，不用全写，这道题不用写degree not
in，分页查询每一行
insert into Scorebak values(@sno,@cno,@degree,getdate())
delete from score
where sno=@sno and cno=
@cno
set @i=@i+
1
end
go
select *
from Scorebak
select *
from Score
delete from Score
where Cno=
‘3-105‘
数据库 触发器基础用法（备份删除行+删除多行）
标签：
