Thread: Using a rule as a trigger.

Hi all,

I have looked at some previous posting and thought that I had found 
exactly what I need. What I need is to insert an id (from a sequence) 
when a new record is inserted. The following example almost does what I 
need :

CREATE TABLE  topics (id int, topic varchar(50), descriotion text);
CREATE SEQUENCE nextid start 1;
CREATE RULE ins_topic AS ON INSERT TO topics WHERE id ISNULL DO UPDATE 
topics SET id=nextval('nextid') WHERE  id ISNULL;


This example updates the last insert. I need it to update the currnet 
insert. How do I do this?

Kind regards
Andrew Higgs

Andrew Higgs wrote:
> 
> Hi all,
> 
> I have looked at some previous posting and thought that I had found
> exactly what I need. What I need is to insert an id (from a sequence)
> when a new record is inserted. The following example almost does what I
> need :
> 
> CREATE TABLE  topics (id int, topic varchar(50), descriotion text);
> CREATE SEQUENCE nextid start 1;
> CREATE RULE ins_topic AS ON INSERT TO topics WHERE id ISNULL DO UPDATE
> topics SET id=nextval('nextid') WHERE  id ISNULL;
> 
> This example updates the last insert. I need it to update the currnet
> insert. How do I do this?
> 
> Kind regards
> Andrew Higgs



Isn't better create the field "id" as "serial" instead "int" ???

this will do what you need.

-- 
======================================================
AKACIA TECNOLOGIA
Desenvolvimento de sistemas para Internet
www.akacia.com.br

As someone else said a serial is probably easier, but 
a trigger is probably a better bet than a rule for this purpose.
Using a plpgsql before insert trigger will do it.

On Wed, 10 Jan 2001, Andrew Higgs wrote:

> Hi all,
> 
> I have looked at some previous posting and thought that I had found 
> exactly what I need. What I need is to insert an id (from a sequence) 
> when a new record is inserted. The following example almost does what I 
> need :
> 
> CREATE TABLE  topics (id int, topic varchar(50), descriotion text);
> CREATE SEQUENCE nextid start 1;
> CREATE RULE ins_topic AS ON INSERT TO topics WHERE id ISNULL DO UPDATE 
> topics SET id=nextval('nextid') WHERE  id ISNULL;
> 
> 
> This example updates the last insert. I need it to update the currnet 
> insert. How do I do this?