TG_OP , Ref Int, and DELETE - Mailing list pgsql-admin

 Tom Lane wrote:

Michael Zouroudis <mzouroudis@idealcorp.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;
                                         --delete record from
assets, books, book_asset
        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