Re: Having a problem with my stored procedure

I tried doing two different triggers as you suggested but I kept getting
an error stating:

psql:archive_news_articles.sql:75: ERROR: trigger "archive_articles"
for relation "news_content" already exists
psql:archive_news_articles.sql:80: ERROR: trigger "update_archives" for
relation "news_content" already exists

So, I thought perhaps it couldn't be done.

Ted Byers wrote:
> Would it not be simpler to just create two trigger functions, one that
> acts on insert operations and a second that acts on update
> operations? A 30 second glance at the Postgresql documentation showed
> me that it is possible to have more than one row level trigger for a
> given table, which implies the simpler options is possible. This
> would make for a much simpler design and avoid a conditional block
> that would then be unnecessary. This extra cost is, of course,
> trivial if only a handful of records are modified or created, but if
> the number is large, it could become significant. Or is there
> something in how an RDBMS handles triggers that would make it
> preferable to have a single trigger for all possible operations on a
> record? Something an old C++ programmer would miss if not informed
> about the peculiarities of database development. Did I miss something
> critical? My usual approach is to have functions remain as simple as
> practicable and do only one thing, unless there is a very good reason
> to have them more complex (in which a driver function that calls a
> number of simple functions may be preferable to one that tries to do
> everything). Simple functions are easy to validate, and once
> validated make validation of more complex driver functions easier.
>
> Why bother with so many temporaries? Isn't that a waste of both
> development time (lots of extra typing and opportunity for errors such
> as typos) and runtime CPU cycles? Why not just insert or update
> values directly from the NEW or OLD record into the target table
> rather than copying the values first into the temporaries and then
> from the temporaries into their final destination?
>
> HTH
>
> Ted
>
> ----- Original Message -----
> *From:* William Leite Araújo <mailto:william(dot)bh(at)gmail(dot)com>
> *To:* Laura McCord <mailto:mccordl(at)southwestern(dot)edu>
> *Cc:* pgsql-general(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>
> *Sent:* Tuesday, February 13, 2007 12:19 PM
> *Subject:* Re: [GENERAL] Having a problem with my stored procedure
>
> 2007/2/13, Laura McCord <mccordl(at)southwestern(dot)edu
> <mailto:mccordl(at)southwestern(dot)edu>>:
>
> To make a long story short, I am archiving data from an
> original table
> to a table I created. This is a third party web application
> that I am
> doing this with, so I can't revise the structure/code of this
> application. With this said, if the original table goes
> through an
> insert or update action I want to replicate the information to my
> archive table. I don't want to delete any articles from my archive
> table so this is why I am not wanting to do anything based on
> a delete
> action.
>
> The only problem that I am facing is how to tell the function
> that I want to perform an update if an update occurred and an
> insert if an insert action occurred. I want to have different
> actions occur depending on if the trigger was based on an
> insert or update.
>
> Help, I've been stumped for two days.
> Thanks in advance.
>
> This is what I have so far:
> CREATE TRIGGER archive_articles
> AFTER INSERT OR UPDATE ON
> news_content
> EXECUTE PROCEDURE su_archive_articles();
>
>
>
> CREATE OR REPLACE FUNCTION su_archive_articles()
> RETURNS TRIGGER
> LANGUAGE plpgsql
> AS '
> DECLARE
> tmp_news_id CHARACTER varying(48);
> tmp_title CHARACTER varying(100);
> tmp_abstract CHARACTER varying(300);
> tmp_news_story TEXT;
> tmp_topic_id CHARACTER varying(10);
> tmp_create_date DATE;
> tmp_author CHARACTER varying(50);
> tmp_begin_date DATE;
> tmp_end_date DATE;
> tmp_priority CHARACTER(1);
> tmp_image_name CHARACTER varying(512);
> tmp_image_mime_type CHARACTER varying(50);
> tmp_layout_type CHARACTER varying(10);
>
> BEGIN
> SELECT INTO tmp_news_id news_id from news_content where
> last_inserted(news_id);
> SELECT INTO tmp_title title from news_content where
> last_inserted(news_id);
> SELECT INTO tmp_abstract abstract from news_content where
> last_inserted(news_id);
> SELECT INTO tmp_news_story news_story from news_content where
> last_inserted(news_id);
> SELECT INTO tmp_topic_id topic_id from news_content where
> last_inserted(news_id);
> SELECT INTO tmp_create_date create_date from news_content
> where last_inserted(news_id);
> SELECT INTO tmp_author author from news_content where
> last_inserted(news_id);
> SELECT INTO tmp_begin_date begin_date from news_content where
> last_inserted(news_id);
> SELECT INTO tmp_end_date end_date from news_content where
> last_inserted(news_id);
> SELECT INTO tmp_priority priority from news_content where
> last_inserted(news_id);
> SELECT INTO tmp_image_name image_name from news_content where
> last_inserted(news_id);
> SELECT INTO tmp_image_mime_type image_mime_type from
> news_content where last_inserted(news_id);
> SELECT INTO tmp_layout_type layout_type from news_content
> where last_inserted(news_id);
>
>
> IF TG_OP = 'INSERT' THEN
>
> //This is to be done if an INSERT action was done on the table
>
> INSERT INTO su_archives(news_id, title, abstract, news_story,
> topic_id, create_date, author, begin_date, end_date, priority,
> image_name, image_mime_type, layout_type) VALUES
> (tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_
>
> image_name ,tmp_image_mime_type,tmp_layout_type);
>
>
> ELSEIF TG_OP = 'UPDATE' THEN
>
> //HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN
> UPDATE WAS DONE
>
>
> END IF;
>
> RETURN NEW;
> END
> ';
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
> <http://archives.postgresql.org/>
>
>
>
>
> --
> William Leite Araújo
> Analista de Banco de Dados - QualiConsult
>