Thread: TG_OP and DELETE

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