SQL触发器使用参考代码

admin管理员组

文章数量:1532656

2024年7月4日发(作者：)

触发器

use EBuy

if exists (select * from _objects where name='custDel' and type='TR') --判断custDel存储过

程是否存在，存在则删除它

drop trigger custDel

go

create trigger custDel on customer --创建存储过程

for delete --触发器类型

as

print '客户记录被删除'

delete from customer where cusid='1201' --删除验证

select * from customer --查询customer表

sp_helptext custDel --查看存储过程

use Study

--创建新表

create table major_stat(

majorName varchar(50),

toStu int,

toCredit int

)

create trigger updateMajorStat --创建存储过程

on student

for insert,update,delete --触发器类型

as

begin

delete from major_stat

insert into major_stat select majorName,count(*), sum(credit)

from student s inner join major m on d=d group by majorName

end

insert into student values('071126','李静',2,'男','1985-10-20',42,null)

update student set credit=credit+2 where majorid=1

delete from student where stuid='071126'

select * from student

select * from major_stat

--insert触发器

use Study

go

select * from course

select * from student_course

select * from student

１

if exists(select name from _objects where name='insertScore' and type='TR')

drop trigger insertScore

go

create trigger insertScore

on student_course

for insert

as

if(select score from inserted)>=60

begin

declare @stuid char(6),@couid char(3),@credit int

select @stuid=stuid,@couid=couid from inserted

select @credit=couCredit from course where couid=@couid

update student set credit=credit+@credit where stuid=@stuid

end

insert into student_course values('071001','C17',70)

--update触发器

if exists(select name from _objects where name='updateCouHour' and type='TR')

drop trigger insertScore

go

create trigger updateCouHour

on course

for update

as

declare @oldnum int,@newnum int

begin

select @oldnum=couHour from deleted

select @newnum=couHour from inserted

if @newnum>@oldnum or @newnum<50

begin

print '课时不能高于原课时数并且不能低于50'

rollback tran

end

end

go

update course set couHour=couHour-2 where couid like 'A01'

--delete触发器

create trigger delMajor

on major

for delete

as

if(select majorId from deleted)=1

begin

print '不能删除计算专业'

rollback tran

end

go

２

alter table student

drop constraint fk_student_major

go

select * from major

delete from major where majorId=2

select * into quit_stu from student where 1=2 --创建一个与student表结构相同的表quit_stu

create trigger delStuBackup --创建存储过程(把从student表删除的记录备份到quit_stu表)

on student

for delete

as

insert into quit_stu select * from deleted

go

delete from student_course where stuid='071001'

delete from student where stuid='071001'

select * from student

--instead of

use Study

if exists(select name from _objects where name='delStuBackup' and type='TR')

drop trigger insertScore

go

create trigger delStuBackup --创建存储过程(把从student表删除的记录备份到quit_stu表)

on student

instead of delete

as

declare @stuid char(6)

begin try

select @stuid=stuId from deleted

delete from student_course where stuId=@stuid

delete from student where stuId=@stuid

insert into quit_stu select * from deleted

commit tran

print '操作成功'

end try

begin catch

rollback tran

end catch

go

set nocount on --不显示影响行数的消息

go

delete from student where stuId='071011'

select * from student

３

select * from student_course

select * from quit_stu

--在视图上创建存储过程

create table employee(

empid char(5) primary key,

name varchar(20),

age int

)

--触发器的查看、修改和删除

sp_helptext 'ins_emp_job' --查看触发器

alter trigger delStuBackup --修改触发器

on student

for delete

as

print '触发器已经修改'

drop trigger delStuBackup --删除触发器

create table jobs(

id int identity(1,1) primary key,

empid char(5),

job varchar(30),

salary money,

foreign key(empid) references employee(empid)

)

insert into emp_job values('33012','jane',26,'打字员',2000) --创建完存储过程后，可以操作多个

表

select * from emp_job

alter table student

disable trigger delStuBackup --禁用触发器(在表上)

create trigger ins_emp_job --在视图上创建存储过程

on emp_job

instead of insert

as

insert into employee select empid,name,age from inserted

insert into jobs select empid,job,salary from inserted

go

create view emp_job --创建视图

as

select ,name,age,job,salary from employee e inner join jobs j on =

go

insert into emp_job values('33012','jane',26,'打字员',2000) --通过视图操作多个表提示有错误

４

alter table student

enable trigger delStuBackup -- 启用触发器(在表上)

--也可以用以下方式

disable trigger ins_emp_job on emp_job --禁用触发器(在表上)

enable trigger ins_emp_job on emp_job --启用触发器(在表上)

５

本文标签： 触发器删除不能过程