Re: Having a problem with my stored procedure

2007/2/13, Laura McCord <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/
>

--
William Leite Araújo
Analista de Banco de Dados - QualiConsult