Re: Having a problem with my stored procedure

> As suggested earlier, it is probably cleaner to define separate triggers
> on insert and on update. That is possible, but they can't have the same
> names.
> You probably want to name them accordingly too, or you'll get naming
> conflicts.
>
> I suggest:
> DROP TRIGGER archive_articles ON news_content;
>
While this is something I'd do if I had absolute control over all the
software, in the OP's case, there is a third party application
involved that can't be modified. I would therefore worry that the
developer of that software may have also placed triggers of that name
on that table. This would suggest a little slop in the practices of
that developer (i.e. of the third party application, not the OP) since
a decent naming convention would make a name collision between that
developer's code and the OP's code highly unlikely, but that is
another issue. If the OP is getting name collision when trying to
create these triggers, the implication is that the developer of the
third party app in fact defined triggers of the same names, so
dropping previously created triggers may well break that app. It
seems to me that dropping something I haven't created is a high risk
action.

A naming convention similar to what I use would solve that problem
without the risk associated with dropping something someone else has
developed. If I am working on something to be distributed, I use a
naming scheme that prepends a very short string that makes it clear
the code was developed by myself or one of my staff, and in languages
that support a namespace, such as C++, I make certain there is a
namespace ID unique to my organization. This eliminates the risk of a
name collision unless some developer actually tries to impersonate
me. In some cases, where I am working as part of a team, my
preference is to do the same with the developer's ID (since always the
developer who developed a given peice of code is responsible for
fixing any bugs in it whever possible). Of course, always the fact is
fully documented, both in the code and in design documents provided to
the client. This is a discipline I impose on myself, as a courtesy to
those who come after me, and it involves considerations any library
developer necessarily worries about. It is not something I want to
impose on those who come after me, but which I would require of those
who develop libraries or databases or tools I need to use in order to
be productive.

Damn. It just occured to me that the OP had to be able to see the
structure of the DB using a tool like pgAdmin, in order to just get
the names of the tables and columns. Therefore, the OP should have
also been able to see the definitions of any existing triggers and
trigger functions. Further, the OP should be able to create more
functions that could be called at the end of any existing trigger
functions, thereby obtaining the functionality desired without
compromizing the third party app. You can edit these functions from
within pgAdmin. I have done so myself on trigger functions I created
myself. This would make the OP's task almost trivially simple. Don't
you just hate when you see the obvious solution only after spending
time on other options? ;-)

Cheers,

Ted