| Errors (ORA-03113, ORA-03114) for trigger and deref() [message #273554] |
Wed, 10 October 2007 22:10  |
VladSel
Messages: 1
Registered: October 2007
|
Junior Member |
|
|
Help ???
CREATE TYPE v1_type1 AS OBJECT (
id number,
name VARCHAR(200)
);
CREATE TABLE v1_table_type1 OF v1_type1;
CREATE TABLE v1_table1 (
id number,
name varchar2(150),
kol number,
t1 ref v1_type1 scope is v1_table_type1,
ID_T1 NUMBER
);
alter table V1_TABLE1
add constraint V1_TABLE1_FK foreign key (ID_T1)
references V1_TABLE_TYPE1 (ID);
create or replace trigger v1_table1
before insert or update or delete on v1_table1
for each row
declare
cnt_ number;
begin
----------------------------------------------------------------------
if inserting then
if :new.id_t1 is null then
:new.t1:=null;
else
select count(*) into cnt_ from v1_table_type1 t where t.id=:new.id_t1;
if cnt_=1 then
select ref(t) into :new.t1 from v1_table_type1 t where t.id=:new.id_t1;
else
raise_application_error(-20000,'Объектная ошибка!');
end if;
end if;
end if;
----------------------------------------------------------------------
if updating then
if :new.id_t1 is null then
:new.t1:=null;
else
select count(*) into cnt_ from v1_table_type1 t where t.id=:new.id_t1;
if cnt_=1 then
select ref(t) into :new.t1 from v1_table_type1 t where t.id=:new.id_t1;
else
raise_application_error(-20000,'Объектная ошибка!');
end if;
end if;
end if;
----------------------------------------------------------------------
if deleting then
null;
end if;
----------------------------------------------------------------------
end v1_table1;
insert into v1_table_type1 (id, name)
values (10,'aaa');
insert into v1_table_type1 (id, name)
values (11,'bbb');
insert into v1_table_type1 (id, name)
values (12,'ccc');
commit;
insert into v1_table1 (id, name, kol, id_t1)
values (10,'sss', 12, 1);
commit;
insert into v1_table1 (id, name, kol, id_t1)
values (11,'sss', 12, null);
commit;
ORA-03113: end-of-file on communication channel
ORA-03114: not connected to ORACLE
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
