Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

i'm triying to create an autoincrement field (like SERIAL) using a trigger and sequence. I know that only can use a sequence or SERIAL type on field, but i must resolve this using both methods (triggers and secuences)

CREATE SEQUENCE AlimentosSequencia;

CREATE OR REPLACE FUNCTION AlimentoFuncion()
  RETURNS "trigger" AS
$BODY$
    BEGIN
      New.id:=nextval('AlimentosSequencia');
      Return NEW;
    END;
$BODY$

LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER AlimentosTrigger
  BEFORE INSERT
  ON alimento
  FOR EACH ROW
  EXECUTE PROCEDURE AlimentoFuncion();

I try this combination but dosen't works, the table alimento has two fields only, integer id(the autoincrement with trigger and sequence) and the varchar name.

Any suggestion ?

Thanks

share|improve this question
5  
just give some more explanation of "it doesn't work". – Szymon Guz Jun 20 '12 at 18:02
2  
Why don't you want to use the simple SERIAL type? – Szymon Guz Jun 20 '12 at 18:03
3  
You can just call nextval() on the associated sequence of a serial column for other purposes. No need to complicate things with a trigger. Aside from that, without error message, your question is just noise. Include it verbatim, please. – Erwin Brandstetter Jun 20 '12 at 18:09
2  
You could set the nextval('AlimentosSequencia') as the default value for this column, even when it is integer. No need of triggers. – Szymon Guz Jun 20 '12 at 18:18
it's an exercise guys, i must doit using trigger and sequence, here is not evaluating the performance. Thanks – jgiunta Jun 22 '12 at 14:01
show 1 more comment

1 Answer

up vote 2 down vote accepted

As others users have told you, you don't need to use a trigger. You can declare the table like this:

CREATE SEQUENCE AlimentosSequencia;

CREATE TABLE alimento (
  id integer NOT NULL DEFAULT nextval('AlimentosSequencia') PRIMARY KEY
 ,name VARCHAR(255));

And when you insert a new record:

INSERT INTO alimento (name) VALUES ('lemon');

Another possibility is declared the id field as serial type, that it would create the sequence automatically.

UPDATE: Ok, it's an exercise. Then I don't understand what's the problem? I have tested this code:

CREATE SEQUENCE AlimentosSequencia;

CREATE TABLE alimento (
  id integer NOT NULL PRIMARY KEY
 ,name VARCHAR(255));

 CREATE OR REPLACE FUNCTION AlimentoFuncion()
 RETURNS "trigger" AS
 $BODY$
 BEGIN
   New.id:=nextval('AlimentosSequencia');
   Return NEW;
 END;
 $BODY$
 LANGUAGE 'plpgsql' VOLATILE;

 CREATE TRIGGER AlimentosTrigger
 BEFORE INSERT
 ON alimento
 FOR EACH ROW
 EXECUTE PROCEDURE AlimentoFuncion();

 INSERT INTO alimento (name) VALUES ('lemon');

And it works without problems.

share|improve this answer
Great !!! Thanks!! – jgiunta Jun 23 '12 at 18:20
The problem was when i declared de ID field ... :( !!!! But readng your answer i detect the problem. Thanks again – jgiunta Jun 23 '12 at 18:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.