trigger issue - ITeye问答

0 0

trigger issue5

trigger codes:
create or replace trigger limitinsertbefor
before insert on student
for each row
when((sysdate-old.birthday)/365<18)
begin
       insert into trigger_table values('update',:old.studentid,:new.studentid,sysdate,'student');
end limittinsertbefor;

// Table student structure
    Name        Type         Nullable Default Comments
    ----------- ------------ -------- ------- --------
    STUDENTID   VARCHAR2(8)
    STUDENTNAME VARCHAR2(8)
    BIRTHDAY    DATE         Y
    CLASSID     VARCHAR2(12) Y

execution statement;
insert into student values('20053822','lanlan',to_date('19860825','yyyyhhdd'),'201001');
ERROR CODE:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

But if i get rid of this condition:when((sysdate-old.birthday)/365<18) this execution statement will run succeed.

Maybe this condition which when((sysdate-old.birthday)/365<18) doesn't work. but i can't find reason about this issue .

feel free pls help me ! thans

i very sorry that i don't have enough score with me so that i can't prove more score for this question.

SORRY!!!

问题补充：谢谢你的答案，很满意。

But:

select sysdate-to_date('19861103','yyyymmdd') from dual;

SYSDATE-TO_DATE('19861103','YY
------------------------------
8544.41739583333

上面是一个日期型-日期型，但是结果不是数字型吗.

thanks .

问题补充：

suziwen 写道

这样写

create or replace trigger limitinsertbefor 
  before insert on student  
  for each row 
  when((to_number(sysdate-new.birthday))/365<18) 
  begin 
       insert into trigger_table values('update',:old.studentid,:new.studentid,sysdate,'student'); 
  end limittinsertbefor; 

  insert into student values('20052422','lanlan',to_date('19960825','yyyyMMdd'),'201001');

日期相减后还是日期型，不能直接跟数字操作，得先转换成数字型

你代码里的when((sysdate-old.birthday)/365<18)
old.birthday应该是new.birthday
因为是插入的数据，没有老数据

insert into student values('20053822','lanlan',to_date('19860825','yyyyhhdd'),'201001');
里的yyyyhhdd应该为yyyyMMdd

谢谢你的答案，很满意。

But:

select sysdate-to_date('19861103','yyyymmdd') from dual;

SYSDATE-TO_DATE('19861103','YY
------------------------------
8544.41739583333

上面是一个日期型-日期型，但是结果不是数字型吗.

thanks .

Oracle 数据库

2010年3月25日 17:38

CathyTan
5
0 0 0

1个答案按时间排序按投票排序

0 0

采纳的答案

这样写

create or replace trigger limitinsertbefor 
  before insert on student  
  for each row 
  when((to_number(sysdate-new.birthday))/365<18) 
  begin 
       insert into trigger_table values('update',:old.studentid,:new.studentid,sysdate,'student'); 
  end limittinsertbefor; 

  insert into student values('20052422','lanlan',to_date('19960825','yyyyMMdd'),'201001');

2010年3月25日 21:56

suziwen
3226
0 0 13

添加评论

已解决问题

未解决问题

排行榜

查看全部排名>>

trigger issue5

1个答案按时间排序按投票排序

采纳的答案

相关推荐

相关推荐

已解决问题

未解决问题

排行榜

trigger issue5

1个答案 按时间排序 按投票排序

采纳的答案

相关推荐

相关推荐

已解决问题

未解决问题

排行榜

1个答案按时间排序按投票排序