by Harald Mitterhofe » Sun, 31 Dec 1899 09:00:00
i am having troubles with tables with "on delete cascade"-constraint and
triggers on it; oracle-support told me, that it is a not yet fixed bug
in oracle 8i;
i would like to solve the problem by not using the "on delete cascade"
constraint and do exactly the same by triggers, eg. having a trigger on
the father-table, which deletes all its children;
is there a way to simulate "on dele..." by triggers?
thanx, harald mitterhofer
by Mario Bucsic » Sun, 31 Dec 1899 09:00:00
Hello Harald !
I would prefer to use an AFTER DELETE ON .... trigger.
create trigger work_on_delete
after delete on table_name
for each row
begin
-- action
dbms_output.put_line('row deleted');
end;
/
Regards,
Mario Bucsics, OCP
* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!
1. Bug in cascading deletes in SQL Server 2000 ?
Is it possible that if you have 2 columns in a table the reference the same
table, you cannot setup a cascading delete on both columns. I have a table
that link to requests in a request database, if either request is deleted, I
want any row(s) the linking table to be deleted as well. I am missing
something or is this a bug ?
create table dbo.x
( x int identity
, PRIMARY KEY NONCLUSTERED (x)
)
create table dbo.xx
( xx int identity ,
x1 int not null,
x2 int not null,
PRIMARY KEY NONCLUSTERED (xx),
FOREIGN KEY (x1)
REFERENCES dbo.x(x) ON DELETE CASCADE ,
FOREIGN KEY (x2)
REFERENCES dbo.x(x) ON DELETE CASCADE
)
go
-- this gets you: Introducing FOREIGN KEY constraint 'FK__xx__x2__69C6B1F5'
on table 'xx' may cause cycles or multiple cascade paths. Specify ON DELETE
NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
drop table dbo.x
drop table dbo.xx
go
--
TIA,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
2. .bmp files
3. Cascade update/delete BUG???
7. Cascade Update/Delete BUG???
9. Cascade delete trigger - one more optimizer bug?
10. How to use ON UPDATE CASCADE ON DELETE CASCADE
11. Can't enforce FK contrain on Delete Cascade and Update Cascade
12. cascade update , cascade delete
13. Cascade Delete and Cascade Update on SQL 7