Ref Int and DELETE

>Michael Zouroudis <mzouroudis(at)idealcorp(dot)com> writes:
>
>>because of integrity constraints, i have to
>>delete the record from book_asset, then book, and finally assets. all
>>inserts, updates, and deletes are done through the assets table, and i
>>have a trigger on assets that first deletes book_asset, then books, and
>>finally assets. the problem is when it deletes assets on that third
>>delete, it sends the db into a recursive loop(the delete on assets
>>starts the trigger again).
>>
>
>Why don't you simply let the system carry on with the deletion that the
>trigger was called for?
>
>Seems to me the answer to the problem "my trigger is infinitely
>recursive" is "make your trigger not recurse".
>
> regards, tom lane
>
i have tried to do this, but when the trigger fires (i have it set to
fire before delete) it does not delete the record from the asset table.
so when i added the sql statement to delete from my asset table into
the function, i get the error. when i take that statement out, it
doesn't delete everything that i need it to delete. i'm going to add my
function so you can see:

create function twoinsertable() returns opaque as '

declare
--declare the variables
a assets%ROWTYPE;
c computers%ROWTYPE;
m misc%ROWTYPE;
b books%ROWTYPE;
s software%ROWTYPE;
ba book_asset%ROWTYPE;
sa software_asset%ROWTYPE;
d text;
t text;
co money;
as integer;
au text;
p text;
i integer;
ti text;
x text;
y integer;
vco text;

begin
--assign values to variables
d := old.descript;
co := old.cost;
t := old.type;
as := old.asset_id;
au := old.author;
p := old.publisher;
i := old.isbn;
ti := old.title;

if TG_OP = ''DELETE'' then
--assign the asset type to a variable
raise notice ''as = %'', as;
select type from assets where asset_id = as into x;
--delete statement for computers
if x = ''computer'' then
raise notice ''Got computer'';
Delete from computers where asset_id = as;
delete from assets where asset_id = as;
--delete book record from assets, books, and
book_asset
elsif x = ''books'' then
raise notice ''Got books'';
select book_id from book_asset where asset_id =
as into y;
Delete from book_asset where asset_id = as;
delete from books where book_id = y;
--delete from assets where asset_id = as;
--delete record from assets, software,
and software_asset
elsif x = ''software'' then
raise notice ''Got software'';
raise notice ''x = %'', x;
select software_id from software_asset
where asset_id = as into y;
raise notice ''software_id = %'', y;
delete from software_asset where
asset_id = as;
delete from software where software_id = y;
--delete from assets where asset_id = as;
else
--x != ''software'' and x !=
''computer'' and x != ''book'' then
raise notice ''Got s'';
--delete record from assets and
misc
delete from misc where
asset_id = as;
--delete from assets where
asset_id = as;

end if;

end if;

return null;

end;

' language 'plpgsql';

create trigger last before delete on assets
for each row execute procedure twoinsertable();

as you can see i have commented out the delete on assets so i don't get
that error, but i don't know how to delete from the asset table w/o
setting off the trigger.

also to Ragnar,

*You should be able to set you constraint to delete the entries in the
other tables automaticly instead of refusing. Then you don't need the
triggers.*

i don't follow. i don' know how to set the constraintto delete other tables automatically. just because i have foreign keys on my tables doesn't mean it is going to delete the other info. if i delete in the correct order all it does is delete it from that table, but not from the corresponding tables. if you could detail what you're saying it would be helpful.

thanks for the replys,

--
Mike Zouroudis
Intern
__________________________________________________
I.D.E.A.L. Technology Corporation - Orlando Office
http://www.idealcorp.com - 407.999.9870 x14