有三个表
S(SNO,SNAME,CLS)(学号,姓名,班级)
C(CNO,CNAME)(课程号,课程名)
SC(SNO,CNO)(学号,课程号)
求在s表建立触发器,如果对s 表进行插入操作,则也对sc表的数据进行相应地操作,也就是在sc表插入该学生的选课记录(C表中所有的课程,刚插入的该学生都要选择)?
S(SNO,SNAME,CLS)(学号,姓名,班级)
C(CNO,CNAME)(课程号,课程名)
SC(SNO,CNO)(学号,课程号)
求在s表建立触发器,如果对s 表进行插入操作,则也对sc表的数据进行相应地操作,也就是在sc表插入该学生的选课记录(C表中所有的课程,刚插入的该学生都要选择)?
on s
for insert
as
begin
insert into sc
select a.sno,b.cno from inserted a,(select distinct cno from sc) b
end
for insert as
insert SC(SNO,CNO) select a.学号,b.CNO from inserted a,(select distinct CNO from c ) b
create trigger trig_insert_s on s
for insert as
insert SC(SNO,CNO) select a.SNO,b.CNO from inserted a,(select distinct CNO from c ) b
--加个判断条件
where not exists(select 1 from SC where SNO=a.SNO and CNO=b.CNO)
修改删除操作触发器将更麻烦
on s
for insert
as
begin
insert into sc
select sno,cno
from inserted,c
for insert
as
begin
insert into sc select sno,cno from inserted,c
end
ON S
FOR INSERT
AS
BEGIN
INSERT INTO SC (SELECT A.SNO, B.CNO FROM INSERTED A, (SELECT DISTINCT FROM C) B)
END
on s
for insert
as
declare @sno char(8),
@cno char(4);
select @sno = sno from inserted;
declare c1 cursor for
select cno from c;
open c1;
fetch c1 into @cno;
while(@@fetch_status=0)
begin
insert into sc(sno,cno) values(@sno,@cno);
fetch c1 into @cno;
end;
close c1;
deallocate c1;注:语句我没有调试,思路如此。