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
To: Laura McCord
Cc: 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 <[EMAIL PROTECTED]>:
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
