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 >
