|
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 -----
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>:
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
|
