Having a problem with my stored procedure

2007/2/13, Laura McCord <mccordl@southwestern.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

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 <mccordl@southwestern.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

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:

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:

Laura McCord <mccordl@southwestern.edu> writes:

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.

You need to pick different names for the triggers ... or if you're
trying to replace an existing trigger definition, you need to DROP it first.

regards, tom lane

Here is a question that I am stumped on:

Does postgres even recognize last_inserted() as mysql does? I notice
that the function fails on that line.

Thanks,
Laura

Tom Lane wrote:

On Tuesday 13 February 2007 11:35, Laura McCord
<mccordl@southwestern.edu> wrote:

Here is a question that I am stumped on:

Does postgres even recognize last_inserted() as mysql does? I notice
that the function fails on that line.

Not, that's just a MySQL function. You could, however, look for the
last value of the sequence generator for the table's primary key, which
should be (but may not always be) equivalent.

However, you are doing far more work than necessary getting the
inserted/updated data; you can just access it through the OLD. and NEW.
records already provided to the trigger.

http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html

--
"If a nation expects to be ignorant and free, in a state of
civilization, it expects what never was and never will be." -- Thomas
Jefferson

Ok, I think I am starting to put two-and-two together.... based on your
thread and Ted's thread, I just realized that OLD. and NEW. are keywords
in postgres.

Alan Hodgson wrote:

On Feb 13, 1:20 pm, mcco...@southwestern.edu (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...@gmail.com>
*To:* Laura McCord <mailto:mcco...@southwestern.edu>
*Cc:* pgsql-gene...@postgresql.org
<mailto:pgsql-gene...@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 <mcco...@southwestern.edu
<mailto:mcco...@southwestern.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/
<http://archives.postgresql.org/&gt;

--
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/- Hide quoted text -

- Show quoted text -

The following doesn't give me any errors (I show here everything
required to set up my test example). Mind you, I haven't gone all the
way to thoroughly test it for all possible ways it could fail, but it
does show that you can create separate trigger functions for each kind
of action. Maybe you encountered a name collision in the function or
trigger names you tried using? Perhaps someone can find where you
went awry if you posted the code that generated the errors you
encountered.

CREATE SEQUENCE tests.a_test_sequence
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE tests.a_test_sequence OWNER TO postgres;

CREATE TABLE tests.Insert_archive
(
id int8 NOT NULL,
dummy_data varchar(1024) NOT NULL,
when_action_taken timestamp DEFAULT now()
)
WITHOUT OIDS;
ALTER TABLE tests.Insert_archive OWNER TO postgres;

CREATE TABLE tests.update_archive
(
ID int8 NOT NULL,
dummy_data varchar(1024) NOT NULL,
when_action_taken timestamp NOT NULL DEFAULT now()
)
WITHOUT OIDS;
ALTER TABLE tests.update_archive OWNER TO postgres;

CREATE OR REPLACE FUNCTION tests.insert_trigger()
RETURNS "trigger" AS
$BODY$begin
INSERT INTO Insert_archive (ID,dummy_data) VALUES
(NEW.ID,NEW.dummy_data);
RETURN NEW;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION tests.insert_trigger() OWNER TO postgres;

CREATE OR REPLACE FUNCTION tests.update_trigger()
RETURNS "trigger" AS
$BODY$begin
INSERT INTO update_archive (ID,dummy_data) VALUES
(NEW.ID,NEW.dummy_data);
RETURN NEW;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION tests.update_trigger() OWNER TO postgres;

CREATE TABLE tests.a_test_table
(
dummy_data varchar(1024) NOT NULL,
id int8 DEFAULT nextval(('tests.a_test_sequence'::text)::regclass)
)
WITHOUT OIDS;
ALTER TABLE tests.a_test_table OWNER TO postgres;

CREATE TRIGGER an_insert_trigger
BEFORE INSERT
ON tests.a_test_table
FOR EACH ROW
EXECUTE PROCEDURE tests.insert_trigger();

CREATE TRIGGER an_update_trigger
BEFORE UPDATE
ON tests.a_test_table
FOR EACH ROW
EXECUTE PROCEDURE tests.update_trigger();

HTH

Ted

On Feb 13, 1:45 pm, mcco...@southwestern.edu (Laura McCord) wrote:

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

I don't think I was suggesting you do anything with the original
application. Rather, I was suggesting you alter what you're were
already trying to do. You can apparently create as many trigger
functions as you like (implied by what I have read in the PostgreSQL
documentation), presumably without name collisions though, and you
have absolute control of what you do within the functions you develop,
as long as it is syntactically correct. So far, I see nothing
preventing you from writing two trigger functions or requiring you to
use so many temporaries. How does not touching the third party
application affect the triggers you add to the database?

Cheers

Ted

Laura McCord wrote:

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();

As suggested earlier, it is probably cleaner to define separate triggers
on insert and on update. That is possible, but they can't have the same
names.
You probably want to name them accordingly too, or you'll get naming
conflicts.

I suggest:
DROP TRIGGER archive_articles ON news_content;

CREATE TRIGGER archive_articles_insert AFTER INSERT ON news_content
EXECUTE PROCEDURE su_archive_articles_insert();

CREATE TRIGGER archive_articles_update AFTER UPDATE ON news_content
EXECUTE PROCEDURE su_archive_articles_update();

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);

It is far easier and just as valid to perform an INSERT or an UPDATE
with the values from the predefined NEW record, like this:

CREATE OR REPLACE FUNCTION su_archive_articles_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS '
BEGIN
INSERT INTO su_archives (news_id, title, abstract)
VALUES (NEW.news_id, NEW.title, NEW.abstract);
END;
';

I'm sure you can add the rest of the columns to that statement yourself.
The update trigger function is similar to this one, but with an UPDATE
statement of course.

In insert and update triggers there's always a record called NEW. In
delete and update triggers there's a record called OLD (and yes, that
means in update triggers you get both).

If you really have to go the path you took, may I suggest:

DECLARE
news_rec su_archives%ROWTYPE;
BEGIN
SELECT INTO news_rec news_id, title, abstract
FROM news_content
WHERE last_inserted NEW.news_id;

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);

//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);

//HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN UPDATE WAS DONE

RETURN NEW;
END
';

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

As suggested earlier, it is probably cleaner to define separate triggers
on insert and on update. That is possible, but they can't have the same
names.
You probably want to name them accordingly too, or you'll get naming
conflicts.

I suggest:
DROP TRIGGER archive_articles ON news_content;

While this is something I'd do if I had absolute control over all the
software, in the OP's case, there is a third party application
involved that can't be modified. I would therefore worry that the
developer of that software may have also placed triggers of that name
on that table. This would suggest a little slop in the practices of
that developer (i.e. of the third party application, not the OP) since
a decent naming convention would make a name collision between that
developer's code and the OP's code highly unlikely, but that is
another issue. If the OP is getting name collision when trying to
create these triggers, the implication is that the developer of the
third party app in fact defined triggers of the same names, so
dropping previously created triggers may well break that app. It
seems to me that dropping something I haven't created is a high risk
action.

A naming convention similar to what I use would solve that problem
without the risk associated with dropping something someone else has
developed. If I am working on something to be distributed, I use a
naming scheme that prepends a very short string that makes it clear
the code was developed by myself or one of my staff, and in languages
that support a namespace, such as C++, I make certain there is a
namespace ID unique to my organization. This eliminates the risk of a
name collision unless some developer actually tries to impersonate
me. In some cases, where I am working as part of a team, my
preference is to do the same with the developer's ID (since always the
developer who developed a given peice of code is responsible for
fixing any bugs in it whever possible). Of course, always the fact is
fully documented, both in the code and in design documents provided to
the client. This is a discipline I impose on myself, as a courtesy to
those who come after me, and it involves considerations any library
developer necessarily worries about. It is not something I want to
impose on those who come after me, but which I would require of those
who develop libraries or databases or tools I need to use in order to
be productive.

Damn. It just occured to me that the OP had to be able to see the
structure of the DB using a tool like pgAdmin, in order to just get
the names of the tables and columns. Therefore, the OP should have
also been able to see the definitions of any existing triggers and
trigger functions. Further, the OP should be able to create more
functions that could be called at the end of any existing trigger
functions, thereby obtaining the functionality desired without
compromizing the third party app. You can edit these functions from
within pgAdmin. I have done so myself on trigger functions I created
myself. This would make the OP's task almost trivially simple. Don't
you just hate when you see the obvious solution only after spending
time on other options? ;-)

Cheers,

Ted

I did check with customer support (they are actual developers of the
system not middle men) and they said there aren't any triggers in the db
structure so it's safe for me to include triggers. So, that helps me
feel reassured because you had a good point. So more than likely, it was
my error and I must have not dropped my trigger properly before
recreating it.

Thanks everyone for your advice. I am going to try this afternoon to
take your suggestions and make this work. I never had a problem creating
mysql stored procedures and I just learned that with postgres it is so
much different.

Thanks Again,
Laura

Ted wrote: