Skip site navigation (1) Skip section navigation (2)

R: [postgresql-it] no cascade triggers?

From: "Manera, Villiam" <vmanera(at)manord(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Cc: "Chris Mair" <chris(at)1006(dot)org>,"postgresql-it" <postgresql-it(at)lists(dot)psql(dot)it>
Subject: R: [postgresql-it] no cascade triggers?
Date: 2007-06-27 09:37:01
Message-ID: BC53C974C3B9E542BC0A9BD4C5B1168A0276D392@NEWMAIL.manord.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:

We could possibly re-allow that (see the comments in AlterTable()) but
it seems like an ugly and inefficient technique that we shouldn't be
encouraging.  (The implications for system catalog bloat alone seem
enough reason to not recommend this.)  Isn't there a cleaner way to
design his application?  Maybe refactor the schema to avoid the
recursion in the first place?  Or add an "insert origin" column to the
table so that the trigger can easily detect trigger-inserted rows and do
nothing?


Is not so simple.
I tried to follow the A.M. (agentm(at)temactionfaction(dot)com) suggestion:

"Instead of putting a trigger on the table, put a rule+trigger on a  
wrapper view"

But in my tests I always get the same SQL error  55006.

To better explain my problem I attach one of my functions that is easy
to understand.

For each of my products I must have one main supplier and I may have
also some secondary suppliers. 

Therefore for each of my articles 
I have as many records in table ANAMAT.ART_FOR as the number of my
suppliers for that article.

The main supplier is identified by code 1 in the column ARF_PROGR_FORN.
Secondary suppliers are numbered as 2,3 and so on

If, for example, I want to promote one of the secondary suppliers (let's
say number 3) 
to be the main one, I should issue the following update:

UPDATE ANAMAT.ART_FOR SET ARF_PROGR_FORN = 1 where Article=xx and
SUPPLIER=yy.....

The update fires the trigger that inovokes this function :

alter table anamat.art_for DISABLE TRIGGER  USER;
1)	update the ARF_PROGR_FORN from 3 to 999 for supplier yy
(temporary)
2)	update the ARF_PROGR_FORN from 1 to 3 for supplier zz (the old
main one)
3)	update the ARF_PROGR_FORN from 999 to 1 for supplier yy (the new
main supplier)
alter table anamat.art_for ENABLE TRIGGER  USER;

in other words I switch the code for the two suppliers.



Following is the simplified code, which is working fine with version
8.2.1.
Without disabling triggers I would get an infinite loop of rule
firing...

How can I do the same thing without disabling triggers in version 8.2.4
?

CREATE TRIGGER art_for_upd BEFORE UPDATE ON anamat.art_for
	FOR EACH ROW EXECUTE PROCEDURE anamat.art_for_upd();


CREATE OR REPLACE FUNCTION anamat.art_for_upd() RETURNS trigger AS $$
	BEGIN
		if    new.arf_progr_forn != old.arf_progr_forn
				then return new;
		end if;				

		if old.arf_progr_forn = 1 then	
			raise exception 'You cannot change the main
supplier';
		end if;
		if new.arf_progr_forn = 1 then
			alter table anamat.art_for DISABLE TRIGGER
USER;
	
			UPDATE ANAMAT.ART_FOR SET ARF_PROGR_FORN = 9999
				WHERE ART = new.ART AND ARF_PROGR_FORN =
1 AND
				(CAPOCONTO <> new.CAPOCONTO OR CONTO <>
new.CONTO);			
			
			UPDATE ANAMAT.ART_FOR SET ARF_PROGR_FORN = 1
				WHERE ART = new.ART AND ARF_PROGR_FORN =
old.arf_progr_forn;			

			UPDATE ANAMAT.ART_FOR SET ARF_PROGR_FORN =
old.arf_progr_forn
				WHERE ART = new.ART AND ARF_PROGR_FORN =
9999;			

			alter table anamat.art_for ENABLE TRIGGER  USER;
		end if;
	return new; 
	END;
$$ LANGUAGE plpgsql;



Regards 
Villiam Manera

In response to

Responses

pgsql-hackers by date

Next:From: Fabien COELHODate: 2007-06-27 11:02:45
Subject: Re: Frustrating issue with PGXS
Previous:From: Teodor SigaevDate: 2007-06-27 08:39:57
Subject: Re: tsearch in core patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group