Re: Having a problem with my stored procedure - Mailing list pgsql-general

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.bh@gmail.com>
>     *To:* Laura McCord <mailto:mccordl@southwestern.edu>
>     *Cc:* pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.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@southwestern.edu
>     <mailto:mccordl@southwestern.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
>