触发器的设计,以表student为例: Create Trigger tri_student on student for Insert as Begin Declare @max_id varchar(10) If @@rowcount = 0 Return Select @max_id = max(id) from log Insert into log(id,datetime,tablename,action) values(cast(@max_id as int)+1,getdate(),'student','insert') If @@error <> 0 Begin Raiserror ('insert log error!',11,1) Rollback tran Return End Return End--以log.id 为数字型字符
Create proc Up_GetResult (@db varchar(20),@srv varchar(20)) as Begin declare @cmd varchar(200) Insert into score_hz(id,name,score) select s.id,t.name,0 from student t,score s,course c where t.id = s.id and s.Course_id = c.id and not exists(select * from score_hz where score_hz.id = t.id) update score_hz set score = (case when zscore =100 then 5 when zscore between 90 and 99 then 4 when zscore between 80 and 89 then 3 when zscore between 70 and 79 then 2 when zscore between 60 and 69 then 1 when zscore < 60 then 0 end) from (select s.id,sum(s.Score*c.Credit_hour)/sum(c.Credit_hour) as zscore from score s,course c group by s.id) where score_hz.id = s.id select @cmd = 'bcp '+ @db + '.dbo.'+ 'score_hz out e:\aaa.txt -c -S' + @srv + ' -Usa -P' exec master..xp_cmdshell @cmd
Create Trigger tri_student on student
for Insert
as
Begin
Declare @max_id varchar(10)
If @@rowcount = 0
Return
Select @max_id = max(id) from log
Insert into log(id,datetime,tablename,action)
values(cast(@max_id as int)+1,getdate(),'student','insert')
If @@error <> 0
Begin
Raiserror ('insert log error!',11,1)
Rollback tran
Return
End
Return
End--以log.id 为数字型字符
Create proc Up_GetResult (@db varchar(20),@srv varchar(20))
as
Begin
declare @cmd varchar(200)
Insert into score_hz(id,name,score)
select s.id,t.name,0
from student t,score s,course c
where t.id = s.id and s.Course_id = c.id
and not exists(select * from score_hz
where score_hz.id = t.id)
update score_hz
set score = (case when zscore =100 then 5
when zscore between 90 and 99 then 4
when zscore between 80 and 89 then 3
when zscore between 70 and 79 then 2
when zscore between 60 and 69 then 1
when zscore < 60 then 0
end)
from (select s.id,sum(s.Score*c.Credit_hour)/sum(c.Credit_hour) as zscore
from score s,course c
group by s.id)
where score_hz.id = s.id
select @cmd = 'bcp '+ @db + '.dbo.'+ 'score_hz out e:\aaa.txt -c -S' + @srv + ' -Usa -P'
exec master..xp_cmdshell @cmd
Return
End
--score_hz为学生成绩统计表