| Why no 'update cascade'? [message #52555] |
Mon, 29 July 2002 19:49  |
Edward Ip
Messages: 2
Registered: July 2002
|
Junior Member |
|
|
Hi all,
I have been looking high and low, and have not been able to find the answer to why Oracle decided not to implement 'on update cascade'.
It would be greatly appreciated if someone can please shed some light on the issue.
Thanks in advance!!
Ed
|
|
|
|
|
|
| Re: Why no 'update cascade'? [message #52568 is a reply to message #52555] |
Tue, 30 July 2002 05:33  |
Edward Ip
Messages: 2
Registered: July 2002
|
Junior Member |
|
|
|
Thanks for the URL. I guess a quick trigger will do the job. But I am still baffled as to why the big-O chose not to implement update cascade. Is it just because they done deem it as a necessary constraint?
|
|
|
|
| Re: Why no 'update cascade'? [message #52578 is a reply to message #52555] |
Tue, 30 July 2002 08:45 |
Silpa
Messages: 23
Registered: July 2002
|
Junior Member |
|
|
You can look at this package and triggers. I am using these and they work fine.
Package
-------
CREATE OR REPLACE PACKAGE UDEPT
as
--
rowCnt number default 0;
inTrigger boolean default FALSE;
--
type C1_type is table of "DEPT"."DEPTNO"%type index by binary_integer;
--
empty_C1 C1_type;
old_C1 C1_type;
new_C1 C1_type;
--
--
procedure reset;
--
procedure do_cascade;
--
procedure add_entry
(
p_old_C1 in "DEPT"."DEPTNO"%type
,p_new_C1 in out "DEPT"."DEPTNO"%type
);
--
end "UDEPT";
-------------------------------------------------------
PACKAGE BODY
---------------
CREATE OR REPLACE PACKAGE BODY UDEPT
as
----------------------------------------------
procedure reset
is
begin
--
if ( inTrigger ) then return; end if;
--
rowCnt := 0;
old_C1 := empty_C1;
new_C1 := empty_C1;
end reset;
----------------------------------------------------
procedure add_entry
(
p_old_C1 in "DEPT"."DEPTNO"%type
,p_new_C1 in out "DEPT"."DEPTNO"%type
)
is
begin
--
if ( inTrigger ) then return; end if;
--
if (p_old_C1 <> p_new_C1) then
rowCnt := rowCnt + 1;
old_C1( rowCnt ) := p_old_C1;
new_C1( rowCnt ) := p_new_C1;
p_new_C1 := p_old_C1;
end if;
end add_entry;
-------------------------------------------------------
procedure do_cascade
is
begin
--
if ( inTrigger ) then return; end if;
inTrigger := TRUE;
--
for i in 1 .. rowCnt loop
insert into DEPTNO("DEPTN0","DEPTNAME")
select new_C(i),"DEPTNAME"
from "DEPT" a
where ("DEPTNO") = (select old_C1(i) from dual);
--
update "DEPT"
set ("DEPTNO") =
(select decode("DEPTNO",old_c1(i),new_c1(i),old_c1(i)) from dual)
where ("DEPTNO") = (select new_C1(i) from dual )
OR ("DEPTNO") = (select old_C1(i) from dual);
--
update "EMP"
set ("DEPTNO") = (select new_C1(i) from dual )
where ("DEPTNO") = (select old_C1(i) from dual);
--
update "TABLE2"
set ("DEPTNO") = (select new_C1(i) from dual )
where ("DEPTNO") = (select old_C1(i) from dual);
--
delete from "DEPT"
where ("DEPTNO") = (select old_C1(i) from dual);
end loop;
--
inTrigger := FALSE;
reset;
exception
when others then
inTrigger := FALSE;
reset;
raise;
end do_cascade;
-------------------------------------------------------
end "UDEPT";
-------------------------------------------------------
Three TRIGGERS on DEPT TABLE
----------------------------
1. Trigger Name - TRGDEPT1
------------------------
CREATE OR REPLACE TRIGGER "TRGDEPT1 "
BEFORE UPDATE OF "DEPTNO" ON "DEPT"
begin
"UDEPT".reset;
end;
2. Trigger Name - TRGDEPT2
------------------------
CREATE OR REPLACE TRIGGER "TRGDEPT2"
BEFORE UPDATE OF "DEPTNO" ON "DEPT"
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
begin
"UDEPT".add_entry(:old."DEPTNO",:new."DEPTNO");
end;
3. Trigger Name - TRGDEPT3
------------------------
CREATE OR REPLACE TRIGGER "TRGDEPT3"
AFTER UPDATE OF "DEPTNO" ON "DEPT"
begin
"UDEPT".do_cascade;
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
