自己写了一个视图Create or replace view view_project_application as
Select t1.project_id,t1.project_name,t1.project_date,t1.project_address, t1.project_type,
t1.project_admin,t1.project_count,t1.project_price,t1.project_payed,t1.project_re,t1.customer_id,t2.customer_name,t2.customer_tel,t2.customer_company,t2.customer_re
from 项目信息 t1,客户信息 t2
where t1.customer_id =t2.customer_id;
为了更新他 又写了一个触发器
create or replace trigger INSERT_PROJECT_INFO
instead of insert on view_project_application
for each row
declare
exist number;
c_account 客户信息.customer_account%type;
begin
if inserting then
select count(*) into exist
from 客户信息
where customer_id=:new.customer_id;
c_account:= :new.project_payed-:new.project_count*:new.project_price;
if exist>0 then
insert into 项目信息
values(:new.project_id,:new.project_name,:new.project_date,:new.project_address,:new.project_type,:new.project_admin,:new.project_count,:new.project_price,:new.project_payed,:new.project_re,:new.customer_id);
update 客户信息
set customer_account =customer_account+c_account
where customer_id=:new.customer_id;
else
insert into 客户信息
values(:new.project_id,:new.customer_name,:new.customer_tel,:new.customer_company,:new.customer_re,c_account);
insert into 项目信息
values(:new.project_id,:new.project_name,:new.project_date,:new.project_address,:new.project_type,:new.project_admin,:new.project_count,:new.project_price,:new.project_payed,:new.project_re,:new.customer_id); end if;
end if;
end;
Select t1.project_id,t1.project_name,t1.project_date,t1.project_address, t1.project_type,
t1.project_admin,t1.project_count,t1.project_price,t1.project_payed,t1.project_re,t1.customer_id,t2.customer_name,t2.customer_tel,t2.customer_company,t2.customer_re
from 项目信息 t1,客户信息 t2
where t1.customer_id =t2.customer_id;
为了更新他 又写了一个触发器
create or replace trigger INSERT_PROJECT_INFO
instead of insert on view_project_application
for each row
declare
exist number;
c_account 客户信息.customer_account%type;
begin
if inserting then
select count(*) into exist
from 客户信息
where customer_id=:new.customer_id;
c_account:= :new.project_payed-:new.project_count*:new.project_price;
if exist>0 then
insert into 项目信息
values(:new.project_id,:new.project_name,:new.project_date,:new.project_address,:new.project_type,:new.project_admin,:new.project_count,:new.project_price,:new.project_payed,:new.project_re,:new.customer_id);
update 客户信息
set customer_account =customer_account+c_account
where customer_id=:new.customer_id;
else
insert into 客户信息
values(:new.project_id,:new.customer_name,:new.customer_tel,:new.customer_company,:new.customer_re,c_account);
insert into 项目信息
values(:new.project_id,:new.project_name,:new.project_date,:new.project_address,:new.project_type,:new.project_admin,:new.project_count,:new.project_price,:new.project_payed,:new.project_re,:new.customer_id); end if;
end if;
end;
VALUES('xq20110312','士大夫',TO_DATE('2011-3-12 21:36:01','YYYY-MM-DD HH24:RR:SS'),'大夫的',1,7,4,1000,4000,'ddf',1,null,null,null,null)
ORA-02291: 违反完整约束条件 (PROJECT_DEFAULT.EMPLOYEE_FK) - 未找到父项关键字
ORA-06512: 在 "PROJECT_DEFAULT.INSERT_PROJECT_INFO", line 11
ORA-04088: 触发器 'PROJECT_DEFAULT.INSERT_PROJECT_INFO' 执行过程中出错INSERT INTO PROJECT_DEFAULT.VIEW_PROJECT_APPLICATION
VALUES('xq20110312','士大夫',TO_DATE('2011-3-12 21:36:01','YYYY-MM-DD HH24:RR:SS'),'大夫的',1,7,4,1000,4000,'ddf',2,'kobe','11112222444','losangel','dfafdf')
ORA-01722: 无效数字
ORA-06512: 在 "PROJECT_DEFAULT.INSERT_PROJECT_INFO", line 17
ORA-04088: 触发器 'PROJECT_DEFAULT.INSERT_PROJECT_INFO' 执行过程中出错
出现这个错误。
想请教下 大家 在新建视图的时候 如果存在外键的话 是否要引用父表的字段呢?~刚学的ORACLE...神马都不是太懂的 呵呵大家帮我看看吧~~
视图中的project_type 这个也是一个外键引用 是和视图一个表空间的。没有错误。