Re: Having a problem with my stored procedure

I did check with customer support (they are actual developers of the
system not middle men) and they said there aren't any triggers in the db
structure so it's safe for me to include triggers. So, that helps me
feel reassured because you had a good point. So more than likely, it was
my error and I must have not dropped my trigger properly before
recreating it.

Thanks everyone for your advice. I am going to try this afternoon to
take your suggestions and make this work. I never had a problem creating
mysql stored procedures and I just learned that with postgres it is so
much different.

Thanks Again,
  Laura

Ted wrote:
>> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>