This would definitely be something I would want to do in a stored procedure - NOT in an automagic "stuff happens by accident" in the background trigger. The stored procedures (two of them, one to delete from tbl_host, the other from tbl_alias) would be able to accomplish this in a very very very straight forward, managable, maintainable, understandable fashion.

Doing this in triggers - ugly, complex, hard to understand, automagic (side effects, stuff just "happens"), hard to maintain.

but it would work like this (strongly discourage this, really)...

A state package in which to "build the email":

ops$tkyte@ORA10GR2> create or replace package state_pkg
2 as
3 g_text long;
4 newline constant varchar2(2) := chr(10);
5 end;
6 /

Package created.


with the on delete cascade, the before and after DELETE triggers will fire on tbl_alias, so we'll use the before delete to reset the package state. Even if the act of deleting from tbl_host deletes many rows - the before delete trigger will fire once:


ops$tkyte@ORA10GR2> create or replace trigger tbl_alias_SETUP
2 before delete on tbl_alias
3 begin
4 state_pkg.g_text := null;
5 end;
6 /

Trigger created.

then we use delete, for each row triggers to capture the rows being deleted:

ops$tkyte@ORA10GR2> create or replace trigger tbl_host_before_delete_fer
2 before delete on tbl_host for each row
3 begin
4 state_pkg.g_text := state_pkg.g_text || state_pkg.newline ||
5 'tbl_host( hostid, ip, name ) = ' ||
6 :old.hostid || ', ' ||
7 :old.ipaddress || ', ' ||
8 :old.hostname;
9 end;
10 /

Trigger created.

ops$tkyte@ORA10GR2> create or replace trigger tbl_alias_after_delete_fer
2 after delete on tbl_alias for each row
3 begin
4 state_pkg.g_text := state_pkg.g_text || state_pkg.newline ||
5 'tbl_alias( aliasid, hostid, alias ) = ' ||
6 :old.aliasid || ', ' ||
7 :old.hostid || ', ' ||
8 :old.alias;
9 end;
10 /

Trigger created.

and it would be in the single invocation of the after delete trigger on alias that we would queue the email to be sent...

ops$tkyte@ORA10GR2> create or replace trigger tbl_alias_CLEANUP
2 after delete on tbl_alias
3 begin
4 dbms_output.put_line( 'we would email ' || state_pkg.newline || state_pkg.g_text );
5 end;
6 /

Trigger created.

I broadened your example out

ops$tkyte@ORA10GR2> select * from tbl_host;

HOSTID IPADDRESS HOSTNAME
---------- ------------ ------------------------------
0 100100100100 mypc
1 100100100100 mypc
2 202020202020 myotherpc

ops$tkyte@ORA10GR2> select * from tbl_alias;

ALIASID HOSTID ALIAS
---------- ---------- ------------------------------
1 1 pc1
2 1 pc2
3 2 Apc1
4 2 Bpc2

ops$tkyte@ORA10GR2> delete from tbl_host where hostid = 0;
we would email

tbl_host( hostid, ip, name ) = 0, 100100100100, mypc

1 row deleted.

delete from host, no rows to cascade - we capture what we needed...



ops$tkyte@ORA10GR2> delete from tbl_host where hostid = 1;
we would email

tbl_host( hostid, ip, name ) = 1, 100100100100,
mypc
tbl_alias( aliasid, hostid, alias ) = 1, 1, pc1
tbl_alias( aliasid,
hostid, alias ) = 2, 1, pc2

1 row deleted.

when it cascades - we get it...

ops$tkyte@ORA10GR2> delete from tbl_alias where aliasid = 3;
we would email

tbl_alias( aliasid, hostid, alias ) = 3, 2, Apc1

1 row deleted.

and when no cascade - just alias, we get it. and even if we delete multiple host records:


ops$tkyte@ORA10GR2> rollback;

Rollback complete.

ops$tkyte@ORA10GR2> delete from tbl_host;
we would email

tbl_host( hostid, ip, name ) = 0, 100100100100, mypc
tbl_host(
hostid, ip, name ) = 1, 100100100100, mypc
tbl_alias( aliasid, hostid, alias )
= 1, 1, pc1
tbl_alias( aliasid, hostid, alias ) = 2, 1, pc2
tbl_host( hostid,
ip, name ) = 2, 202020202020, myotherpc
tbl_alias( aliasid, hostid, alias ) =
3, 2, Apc1
tbl_alias( aliasid, hostid, alias ) = 4, 2, Bpc2

3 rows deleted.


we get one email with all of the details.

(notes: you can store the data in the state_pkg however you want and for real you might use a clob if the email is "large")