You could try triggering a database event from the trigger that does the
actual drop of the trigger(s); maybe after a brief pause to allow the trigger
to complete execution and cleanup.

Shuchit

On Tue, 31 Aug 2010 20:53:54 +0200, <AH> wrote:

any sort of dynamic schema change is a bad smell. there must be a
better solution.

without knowing more about the situation, i suggest you keep the
triggers fixed, and inside the triggers, decide if you must do
things or not.

let's have an example. we want to create a run-once trigger. on
some condition, we need to mark a table "sensitive" to changes,
but we need action only once, for the first change. you can set
up a table like trigger_action_needed, with one column table_name
of type varchar(128). then in the trigger, you write

if not exists (select * from trigger_action_needed where table_name =
'this_table') then
return;
end if;
.... do stuff
delete from trigger_action_needed where table_name = 'this_table';

moral of the story: during normal operations, keep the schema fixed,
and rely on data modifications only.