About your last comment, I can't do any revisions of the third party application where the inserts and updates are occurring. Plus, this whole idea came from a workaround based on a glitch in the software where the expiration of articles is not occurring ,therefore I have to do a delete articles to prevent them from being displayed on the web interface. Also, I don't entirely want to get rid of them completely and that is why I am saving records in an archive table to be used in another application that I need to write in the future. So, this is the reason for the redundancy.....it's a long story. -Laura Laura McCord wrote: > 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@xxxxxxxxx> >> *To:* Laura McCord <mailto:mccordl@xxxxxxxxxxxxxxxx> >> *Cc:* pgsql-general@xxxxxxxxxxxxxx >> <mailto:pgsql-general@xxxxxxxxxxxxxx> >> *Sent:* Tuesday, February 13, 2007 12:19 PM >> *Subject:* Re: [GENERAL] Having a problem with my stored procedure >> >> 2007/2/13, Laura McCord <mccordl@xxxxxxxxxxxxxxxx >> <mailto:mccordl@xxxxxxxxxxxxxxxx>>: >> >> 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 >> >> > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ >