trigger issue

Question

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!!!

Answer 1

这样写

[code="sql"]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'); [/code]

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

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

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