pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp creating rules/triggers/functions
5+ messages / 4 participants
[nested] [flat]
* Help creating rules/triggers/functions
@ 2001-02-27 16:43 Blaise Carrupt <bc@mjtsa.com>
2001-03-02 15:34 ` Re: Help creating rules/triggers/functions Jan Wieck <janwieck@Yahoo.com>
2001-03-02 17:05 ` Re: Help creating rules/triggers/functions Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 5+ messages in thread
From: Blaise Carrupt @ 2001-02-27 16:43 UTC (permalink / raw)
To: pgsql-sql
Hi all !
I use PostgreSQL 7.0.2 on a HP-UX system.
I would like to create a simple function and a simple trigger (or rule) that
deny a delete from a table if the row is referenced in another table.
I though it should look like this (from my Ingres experience... :) :
create function A_del(int4 i_id)
BEGIN
SELECT id
FROM b
where a_id = :i_id;
if rowcount > 0 then
RAISE EXCEPTION "not allowed !"
end if;
END
create trigger before delete from A for each row execute procedure A_del(old.id)
But it seems to be much more complicated with Postgres (create a C function
using CurrentTriggerData,...). May I have missed something or is it really much
more complicated ?
Thanks for help.
_____________
B. Carrupt
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help creating rules/triggers/functions
2001-02-27 16:43 Help creating rules/triggers/functions Blaise Carrupt <bc@mjtsa.com>
@ 2001-03-02 15:34 ` Jan Wieck <janwieck@Yahoo.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jan Wieck @ 2001-03-02 15:34 UTC (permalink / raw)
To: Blaise Carrupt <bc@mjtsa.com>; +Cc: pgsql-sql
Blaise Carrupt wrote:
> Hi all !
>
> I use PostgreSQL 7.0.2 on a HP-UX system.
>
> I would like to create a simple function and a simple trigger (or rule) that
> deny a delete from a table if the row is referenced in another table.
>
> I though it should look like this (from my Ingres experience... :) :
>
> create function A_del(int4 i_id)
> BEGIN
> SELECT id
> FROM b
> where a_id = :i_id;
>
> if rowcount > 0 then
> RAISE EXCEPTION "not allowed !"
> end if;
> END
>
CREATE FUNCTION A_del () RETURNS opaque AS '
DECLARE
nrefs integer;
BEGIN
nrefs := count(*) FROM b WHERE a_id = OLD.i_id;
IF nrefs > 0 THEN
RAISE EXCEPTION ''a_id % still referenced from b'', OLD.i_id;
END IF;
RETURN OLD;
END;'
LANGUAGE 'plpgsql';
>
> create trigger before delete from A for each row execute procedure A_del(old.id)
CREATE TRIGGER A_del BEFORE DELETE ON A
FOR EACH ROW EXECUTE PROCEDURE A_del();
>
>
> But it seems to be much more complicated with Postgres (create a C function
> using CurrentTriggerData,...). May I have missed something or is it really much
> more complicated ?
Alternatively (IMHO preferred) you could use a referential
integrity constraint in table B, which would also cover
UPDATE on A and check values inserted/updated into/in B.
CREATE TABLE B ( ...
FOREIGN KEY (i_id) REFERENCES A (a_id)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help creating rules/triggers/functions
2001-02-27 16:43 Help creating rules/triggers/functions Blaise Carrupt <bc@mjtsa.com>
@ 2001-03-02 17:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2001-03-02 17:05 UTC (permalink / raw)
To: Blaise Carrupt <bc@mjtsa.com>; +Cc: pgsql-sql
If you're only doing a simple check for reference, why not use
foreign keys?
In general however, you probably want to use plpgsql to define the
trigger. And trigger functions don't take parameters in the normal
sense, the function should be created taking no args and returning
opaque; the parameters you add in create trigger are passed in
TG_ARGS (i believe). You might want to look at the user and programmer
guides for more information on trigger functions.
On Tue, 27 Feb 2001, Blaise Carrupt wrote:
> Hi all !
>
> I use PostgreSQL 7.0.2 on a HP-UX system.
>
> I would like to create a simple function and a simple trigger (or rule) that
> deny a delete from a table if the row is referenced in another table.
>
> I though it should look like this (from my Ingres experience... :) :
>
> create function A_del(int4 i_id)
> BEGIN
> SELECT id
> FROM b
> where a_id = :i_id;
>
> if rowcount > 0 then
> RAISE EXCEPTION "not allowed !"
> end if;
> END
>
>
> create trigger before delete from A for each row execute procedure A_del(old.id)
>
>
> But it seems to be much more complicated with Postgres (create a C function
> using CurrentTriggerData,...). May I have missed something or is it really much
> more complicated ?
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Help creating rules/triggers/functions
@ 2001-03-02 14:09 Sondaar Roelof <roelof.sondaar@scania.com>
0 siblings, 0 replies; 5+ messages in thread
From: Sondaar Roelof @ 2001-03-02 14:09 UTC (permalink / raw)
To: 'Blaise Carrupt' <bc@mjtsa.com>; pgsql-sql
Hello Blaise,
The following is how I tested your question;
DROP SEQUENCE b_id_seq;
DROP TABLE b;
CREATE TABLE b (
id SERIAL,
description TEXT
);
INSERT INTO b (description) VALUES('aaaaa');
INSERT INTO b (description) VALUES('bbbbb');
SELECT * FROM b;
DROP FUNCTION a_del(int4);
CREATE FUNCTION a_del(int4)
RETURNS int4
AS 'DECLARE
BEGIN
IF (SELECT count(*) FROM b WHERE id = $1) > 0 THEN
RAISE EXCEPTION ''not allowed !'';
end if;
RETURN $1;
END;'
LANGUAGE 'plpgsql';
SELECT A_del(45);
SELECT A_del(1);
The answer is:
dhcp=# SELECT a_del(45);
a_del
-------
45
(1 row)
dhcp=# SELECT a_del(1);
ERROR: not allowed !
I hope this helps.
Best regrards,
Roelof
> -----Original Message-----
> From: Blaise Carrupt [SMTP:bc@mjtsa.com]
> Sent: 27 February 2001 17:43
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Help creating rules/triggers/functions
>
> Hi all !
>
> I use PostgreSQL 7.0.2 on a HP-UX system.
>
> I would like to create a simple function and a simple trigger (or rule)
> that
> deny a delete from a table if the row is referenced in another table.
>
> I though it should look like this (from my Ingres experience... :) :
>
> create function A_del(int4 i_id)
> BEGIN
> SELECT id
> FROM b
> where a_id = :i_id;
>
> if rowcount > 0 then
> RAISE EXCEPTION "not allowed !"
> end if;
> END
>
>
> create trigger before delete from A for each row execute procedure
> A_del(old.id)
>
>
> But it seems to be much more complicated with Postgres (create a C
> function
> using CurrentTriggerData,...). May I have missed something or is it really
> much
> more complicated ?
>
> Thanks for help.
>
> _____________
> B. Carrupt
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Help creating rules/triggers/functions
@ 2001-03-05 08:39 Sondaar Roelof <roelof.sondaar@scania.com>
0 siblings, 0 replies; 5+ messages in thread
From: Sondaar Roelof @ 2001-03-05 08:39 UTC (permalink / raw)
To: 'Blaise Carrupt' <bc@mjtsa.com>; +Cc: pgsql-sql
Hello Blaise,
I included a script I used to build/maintain a database.
It also includes triggers and history logging.
I hope you can use it.
In the DO NOT use this part are test things which might not be correct.
Best regards,
Roelof
<<DBcreate.scania.02.sql>>
> -----Original Message-----
> From: Blaise Carrupt [SMTP:bc@mjtsa.com]
> Sent: 02 March 2001 16:29
> To: roelof.sondaar@scania.com
> Subject: RE: [SQL] Help creating rules/triggers/functions
>
> Hi Roelof !
>
> I thank you for your answer. It allowed me to find a way to resolve my
> problems.
> What is missing to your answer is the trigger. In the documentation, I
> found a
> way doing it with a C procedure. But I was sure Postgres could do it in a
> simpler way.
>
> If it interests you, what I got now is :
>
> DROP FUNCTION a_del();
>
> CREATE FUNCTION a_del() RETURNS OPAQUE AS
> 'DECLARE
> id INT4;
> BEGIN
> SELECT a_id INTO id /* I'm not sure INTO is
> mandatory... */
> FROM a
> WHERE a_id = OLD.addr_id;
>
> IF FOUND THEN
> RAISE EXCEPTION ''not allowed !'';
> END IF;
>
> RETURN OLD;
> END;'
> LANGUAGE 'plpgsql';
>
>
>
> DROP TRIGGER a_del_trg ON a;
>
> CREATE TRIGGER a_del_trg
> BEFORE DELETE ON a
> FOR EACH ROW
> EXECUTE PROCEDURE a_del();
>
>
> It's much more complicate than Ingres, but it works as well. I don't know
> how
> a_del knows OLD...
>
> I thank you again for your help.
>
>
> ___________________
> B. Carrupt
>
>
>
Attachments:
DBcreate.scania.02.sql (application/octet-stream, 12.3K) download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-03-05 08:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-27 16:43 Help creating rules/triggers/functions Blaise Carrupt <bc@mjtsa.com>
2001-03-02 15:34 ` Jan Wieck <janwieck@Yahoo.com>
2001-03-02 17:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-02 14:09 RE: Help creating rules/triggers/functions Sondaar Roelof <roelof.sondaar@scania.com>
2001-03-05 08:39 RE: Help creating rules/triggers/functions Sondaar Roelof <roelof.sondaar@scania.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox