SQL笔记06

触发器

--触发器
create trigger t1 on stu after insert
as
begin
	if (select COUNT(*) from stu)>17
	begin
		print 'error'
		rollback tran
	end
	else
	begin
		print 'right'
	end
end
insert into stu (sno,sname) values('20200017','kk')
drop trigger t1
select COUNT(*) from stu
delete from stu where sno='20200017' or sno='20200018'

--插入之前是不会插入的
alter trigger t1 on stu instead of insert
as
begin
	select * from inserted
	select * from deleted
	if (select COUNT(*) from stu)>17
	begin
		print 'error'
		rollback tran
	end
	else
	begin
		print 'right'
		--要在触发器里写插入
		declare @sno varchar(13)
		declare @sname varchar(30)
		declare @age int
		select @sno=sno from inserted
		select @sname=sname from inserted
		select @age=age from inserted
		insert into stu(sno,sname,age) values (@sno,@sname,@age)
	end
end
insert into stu (sno,sname) values('20200017','kk')
select COUNT(*) from stu

use stuinfo
create trigger t2 on stu after delete
as
begin
	if (select COUNT(*) from stu)<16
	begin
		print 'error'
		rollback tran
	end
	else
	begin
		print 'right'
	end
end
delete from stu where sno='20200016'

alter trigger t3 on sc instead of insert
as
begin
	--声明变量
	declare @sno varchar(13)
	declare @cno varchar(13)
	declare @grade decimal(5,2)
	--赋值
	select @sno=sno from inserted
	select @cno=cno from inserted
	select @grade=grade from inserted
	if @grade>=55 and @grade<=59
	begin
		set @grade =60
	end
	insert into sc values(@cno,@sno,@grade)
end
insert into sc values('20202','2020005',57)
select * from sc