SQL触发器实战-优快云博客

本文介绍并演示了如何在SQL中使用触发器来自动执行特定任务，例如在插入学生分数时检查学生是否存在，以及在记录银行交易时更新账户余额。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--触发器
create trigger tr_insertStudent
on score
for insert
as 
	declare @stuId int,@sId int
	select @stuId = studentId,@sId=sId from inserted
	if not exists(select * from student where sId=@stuId)
		delete from score where sId=@sId
	else
		print '插入成功'

select * from score
select * from student

insert into score(studentId,english) values(1,10)



--
drop table Records
create table Records
(
	rId int identity(1,1) primary key,
	rType int ,  -- 1存钱  -1 取钱
	rMoney money,
	userId char(4)
)

select * from bank
--创建触发器
create trigger tr_Records
on Records
for insert
as 
	declare @type int,@money money,@id char(4)
	select @type = rType,@money=rMoney,@id=userId from inserted
	
	update bank set balance = balance + @money*@type
	where  cId = @id

--当插入数据的时候就会引发触发器
insert into Records values(-1,10,'0002')

select * from Records











select * from score
--truncate table score

insert into Score (studentId,english,math) values(1,50,30)
insert into Score (studentId,english,math) values(2,40,60)
insert into Score (studentId,english,math) values(3,59,40)
insert into Score (studentId,english,math) values(4,20,25)
insert into Score (studentId,english,math) values(5,90,10)
insert into Score (studentId,english,math) values(6,20,30)
insert into Score (studentId,english,math) values(7,10,20)

sql触发器