sql触发器

最新推荐文章于 2025-06-19 21:09:13 发布

转载最新推荐文章于 2025-06-19 21:09:13 发布 · 301 阅读

sql 专栏收录该内容

14 篇文章

订阅专栏

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

--触发器
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)