现在有两个表 一个student表内容为一个sno int,sname varchar(20),sbirth date 一个stu表sno int age int
现在我想做个触发器在student 表上 实现这样的功能当向student表中插入数据的时候实现student学号自动加1 同时计算学生的年龄放到stu表中 age列中我现在分别做好了这两个触发器 一个实现向stu表中插入年龄的触发器 一个是实现学号自动加1的触发器 我向把他们整合成一个触发器
这个是学号自动加1的触发器 这个触发器还有点问题顺便给修改下吧
create or replace trigger set_add
before insert on student
for each row
declare
sn number;
begin
if (select count(*) from student)>=1
:new.sno:=1;
else
select sno into sn from student where rownum<2 order by sno desc ;
sn:=sn+1;
:new.sno:=sn;
end;
end;
这个是向stu表中插入年龄的触发器
create or replace trigger set_no
after insert on student
for each row
begin
insert into stu(age,sno) values((sysdate-:new.sbirth)/365,:new.sno );end;
/
现在我想做个触发器在student 表上 实现这样的功能当向student表中插入数据的时候实现student学号自动加1 同时计算学生的年龄放到stu表中 age列中我现在分别做好了这两个触发器 一个实现向stu表中插入年龄的触发器 一个是实现学号自动加1的触发器 我向把他们整合成一个触发器
这个是学号自动加1的触发器 这个触发器还有点问题顺便给修改下吧
create or replace trigger set_add
before insert on student
for each row
declare
sn number;
begin
if (select count(*) from student)>=1
:new.sno:=1;
else
select sno into sn from student where rownum<2 order by sno desc ;
sn:=sn+1;
:new.sno:=sn;
end;
end;
这个是向stu表中插入年龄的触发器
create or replace trigger set_no
after insert on student
for each row
begin
insert into stu(age,sno) values((sysdate-:new.sbirth)/365,:new.sno );end;
/
create or replace view myview
as
select student.sno,student.sname,student.sbirth,stu.age from student,stu where student.sno=stu.sno;连接两个表下面是我的触发器
create or replace trigger set_add
before insert on emp
for each row
begin
select NVL(max(emp_id)+1,1) into :new.emp_id from emp ;
end;create or replace trigger set_add_no
instead of insert on myview
for each row
declare
sn number
begin
if (select count(*) from student)>=1
:new.sno:=1;
insert into stu(age,sno) values((sysdate-:new.sbirth)/365,:new.sno ); else
select sno into sn from student where rownum <2 order by sno desc ;
sn:=sn+1;
:new.sno:=sn;
insert into stu(age,sno) values((sysdate-:new.sbirth)/365,:new.sno ); end;
end;
帮看下 怎么回事
create or replace trigger set_add
before insert on student
for each row
begin
select NVL(max(sno)+1,1) into :new.sno from student ;
insert into stu values(:new.sno,to_char(sysdate,'yyyy')-to_char(:new.birth,'yyyy'));
end;
before insert on student
for each row
begin
select seq1.nextval into :new.sno from dual;
insert into stu values (:new.sno, trunc((sysdate - :new.sbirth) / 365));
end;
SQL> insert into student(sname,sbirth) values('zhangsan',to_date('1990-9-3','yyyy-mm-dd'));
1 row inserted
SQL> select * from student;
SNO SNAME SBIRTH
--------------------------------------- -------------------- -----------
1 zhangsan 1990-9-3
SQL> select * from stu;
SNO AGE
--------------------------------------- ---------------------------------------
1 18