Thread: Help creating rules/triggers/functions

Help creating rules/triggers/functions

From

Blaise Carrupt

Date:

02 March 2001, 11:20:55

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

RE: Help creating rules/triggers/functions

From

Sondaar Roelof

Date:

02 March 2001, 14:49:23

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

Re: Help creating rules/triggers/functions

From

Stephan Szabo

Date:

02 March 2001, 15:18:16

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 ?

Re: Help creating rules/triggers/functions

From

Jan Wieck

Date:

03 March 2001, 07:30:28

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

RE: Help creating rules/triggers/functions

From

Sondaar Roelof

Date:

06 March 2001, 13:54:52

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

Attachment

DBcreate.scania.02.sql