/*==============================================================*/
/* Table: tb_course 课程表 */
/*==============================================================*/
create table tb_course
(
c_id NUMBER(4) not null,
sub_id VARCHAR2(12) not null,
starttime DATE not null,
endtime DATE not null,
week NUMBER(1) not null ,
constraint PK_TB_COURSE primary key (c_id)
);
/*==============================================================*/
/* Table: tb_stu_kq 学生考勤表 */
/*==============================================================*/
create table tb_stu_kq
(
stu_kq_id NUMBER(4) not null,
s_id VARCHAR2(12) not null,
QDtime DATE not null,
starttime DATE null,
endtime DATE null,
c_id NUMBER(4) not null,
kq_description VARCHAR2(20),
constraint PK_TB_STU_KQ primary key (stu_kq_id)
);
求帮忙写一个触发器 在 tb_stu_kq表中插入数据之后 根据 c_id去查询课程表中的开始时间和结束时间，然后写触发器把考勤表中的这两个时间更新
下面是我自己写的触发器 和插入语句，不知道插入的时候为什么会报错 ？
CREATE or replace TRIGGER tr_stu_Kq after
insert ON tb_stu_kq REFERENCING NEW AS New OLD AS Old
for each row
begin
update tb_stu_kq set starttime =(select starttime from tb_course where :new.c_id = tb_course.c_id),
endtime =(select endtime from tb_course where :new.c_id = tb_course.c_id) where :old.stu_kq_id = :new.stu_kq_id;
end;
insert into tb_stu_kq
(stu_kq_id, s_id, qdtime, c_id, kq_description)
values
(seq_stu_kq.nextval, '201017010210', sysdate, 1, '哈哈');
commit;
------解决方案--------------------
触发器中不能对本表使用更新操作
下次把错误信息贴出来吧，节省别人的时间
行级触发器的一个好处就是，可以直接对该记录的字段值进行更新，不必用Update
直接对:new.starttime和:new.endtime赋值即可
------解决方案--------------------
create or replace trigger trigger_stu_kq
before insert on tb_stu_kq
for each row
declare
minute number;
begin
minute:=mod (TO_NUMBER((:old.qdtime - :old.STARTTIME )) * 24 * 60,24*60) ;
if minute < 5 then
select '正常' into :new.kq_description from dual;
else
select '迟到' into :new.kq_description from dual;
end if;
end trigger_stu_kq;