自用自用触发器

建表

create table sc_log( 
    type char(6),
  	st_id char(10),
  	c_no char(10),
  	oldscore int,
  	newscore int,
  	uname varchar(20) default 'user',
  	udate datetime default getdate()
)

建立触发器1

create trigger tr_sc
on s_c_info
for update,insert
as

	if (select count(*) from deleted) != 0
		
		begin
			if update(score) 
			insert into sc_log(type,st_id,c_no,oldscore,newscore) 
			
			select 'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score
			from deleted,inserted
		end
	else  
		begin
			insert into sc_log(type,st_id,c_no,newscore)
			select 'insert',inserted.st_id,inserted.c_no,inserted.score
			from inserted
		end

调试

insert into s_c_info(st_id,c_no,score)
values('0603060108','9720013',88)

select * from sc_log




update s_c_info
set score = 99
where st_id = '0603060108'
and c_no = '9720013'

select * from sc_log

再建触发器

create trigger tr_updasc
on s_c_info
for update
as
	if update(score)
	begin
		declare @oldscore int
		declare @newscore int
		set @oldscore = (select score from deleted)
		set @newscore = (select score from inserted)
		if @oldscore > @newscore
		begin
			print('新成绩不允许低于旧成绩，更新失败')
			rollback transaction
		end
	end

调试

-- 更新记录 --sc_log
update s_c_info
set score = 100
where st_id='0603060108'
and c_no='9720013'

select *
from s_c_info
where st_id='0603060108'
and c_no='9720013'

-- 更新记录不生效 --sc_log
update s_c_info
set score = 98 
where st_id='0603060108'
and c_no='9720013'

select *
from s_c_info
where st_id='0603060108'
and c_no='9720013'

create procedure getPractice
	
	@name varchar(24)
as
	
	if (select count(*) from D_Info where D_Name=@name) <> 0 
		begin
			select st_info.St_ID,st_info.St_Name,s_c_info.c_no,C_Info.C_Name
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID 
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='实践'
		end
	else
		print('院系不存在')
go

exec getPractice '法学院'
exec getPractice '材料科学与工程学院'

sp_rename 'getPractice','getPctStu'


alter procedure getPctStu
	
	@name varchar(24) 
as
	
	if (select count(*) from D_Info where D_Name=@name) <> 0 
		begin
			select count(*) 
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID 
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='实践'
		end
	else
		print('院系不存在')
go

alter procedure getPctStu
	
	@name varchar(24) -- mysql中表示24表示24个字符 sqlserver也不是utf-8
as
	
	if (select count(*) from D_Info where D_Name=@name) <> 0 
		begin
			-- distinct st_info.St_ID 
			select count(distinct st_info.St_ID)
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID 
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='实践'
		end
	else
		print('院系不存在')
go

exec getPractice '法学院'
exec getPractice '材料科学与工程学院'

 
 
create trigger tr_sc
on s_c_info
for update,insert
as
 
	if (select count(*) from deleted) != 0
		
		begin
			if update(score) 
			insert into sc_log(type,st_id,c_no,oldscore,newscore) 
			
			select 'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score
			from deleted,inserted
		end
	else  
		begin
			insert into sc_log(type,st_id,c_no,newscore)
			select 'insert',inserted.st_id,inserted.c_no,inserted.score
			from inserted
		end

insert into s_c_info(st_id,c_no,score)
values('0603060108','9720013',88)
 
select * from sc_log
 
 
 
 
update s_c_info
set score = 99
where st_id = '0603060108'
and c_no = '9720013'
 
select * from sc_log
 

create trigger tr_updasc
on s_c_info
for update
as
	if update(score)
	begin
		declare @oldscore int
		declare @newscore int
		set @oldscore = (select score from deleted)
		set @newscore = (select score from inserted)
		if @oldscore > @newscore
		begin
			print('新成绩不允许低于旧成绩，更新失败')
			rollback transaction
		end
	end

-- 更新记录 --sc_log
update s_c_info
set score = 100
where st_id='0603060108'
and c_no='9720013'
 
select *
from s_c_info
where st_id='0603060108'
and c_no='9720013'
 
-- 更新记录不生效 --sc_log
update s_c_info
set score = 98 
where st_id='0603060108'
and c_no='9720013'
 
select *
from s_c_info
where st_id='0603060108'
and c_no='9720013'