Thread: simple trigger question ...

I want to create a trigger on a table that for every insert, in performs a
nextval on a sequence ...

Something like:

CREATE TRIGGER on_company_company_id_seq    AFTER INSERT ON company FOR EACH ROW  EXECUTE PROCEDURE
nextval('company_company_id_seq');

I'm embaressingly new at triggers ... is this possible?

"Marc G. Fournier" <scrappy@hub.org> writes:
> I want to create a trigger on a table that for every insert, in performs a
> nextval on a sequence ...

Er ... you just want to bump the sequence and throw away the actual
value?  You don't want to store the value somewhere?
        regards, tom lane

On Wed, 2 Apr 2003, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@hub.org> writes:
> > I want to create a trigger on a table that for every insert, in performs a
> > nextval on a sequence ...
>
> Er ... you just want to bump the sequence and throw away the actual
> value?  You don't want to store the value somewhere?

Correct

"Marc G. Fournier" <scrappy@hub.org> writes:
>> Er ... you just want to bump the sequence and throw away the actual
>> value?  You don't want to store the value somewhere?

> Correct

Then you need something like (untested)

CREATE FUNCTION mytrig() RETURNS TRIGGER AS '
begin perform nextval(''seq''); return new;
end' LANGUAGE plpgsql;
        regards, tom lane

Okay, so I do have to create the FUNCTION first, I can't do it without?
Can a TRIGGER pass an arg to the FUNCTION?

On Wed, 2 Apr 2003, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@hub.org> writes:
> >> Er ... you just want to bump the sequence and throw away the actual
> >> value?  You don't want to store the value somewhere?
>
> > Correct
>
> Then you need something like (untested)
>
> CREATE FUNCTION mytrig() RETURNS TRIGGER AS '
> begin
>   perform nextval(''seq'');
>   return new;
> end' LANGUAGE plpgsql;
>
>             regards, tom lane
>

"Marc G. Fournier" <scrappy@hub.org> writes:
> Okay, so I do have to create the FUNCTION first, I can't do it without?

Check.

> Can a TRIGGER pass an arg to the FUNCTION?

Yes, but only simple constant strings.  (In this context, it might
make sense for the CREATE TRIGGER command to tell the function the
name of the specific sequence to increment.)
        regards, tom lane

On Wed, 2 Apr 2003, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@hub.org> writes:
> > Okay, so I do have to create the FUNCTION first, I can't do it without?
>
> Check.
>
> > Can a TRIGGER pass an arg to the FUNCTION?
>
> Yes, but only simple constant strings.  (In this context, it might
> make sense for the CREATE TRIGGER command to tell the function the
> name of the specific sequence to increment.)

'K, that makes it workable ... final question (I hope!) ... RULEs vs
TRIGGERs?  I ended up doing it as a RULE, since I could do it without a
function ... but is there a reason why that is a Bad Idea?  Or in a case
like this, it doesn't really matter?

> 'K, that makes it workable ... final question (I hope!) ... RULEs vs
> TRIGGERs?  I ended up doing it as a RULE, since I could do it without a
> function ... but is there a reason why that is a Bad Idea?  Or in a case
> like this, it doesn't really matter?

Rules are per statement, not per row.

An INSERT INTO ... SELECT ... statement will cause a single execution of
the rule even if you insert several hundred rows.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

"Marc G. Fournier" <scrappy@hub.org> writes:
> 'K, that makes it workable ... final question (I hope!) ... RULEs vs
> TRIGGERs?  I ended up doing it as a RULE, since I could do it without a
> function ... but is there a reason why that is a Bad Idea?  Or in a case
> like this, it doesn't really matter?

A lot of people have found that rules don't behave quite the way they
want, particularly with regard to things like the number of times that
side-effects happen.  Better test it out.
        regards, tom lane

On Thu, 3 Apr 2003, Rod Taylor wrote:

> > 'K, that makes it workable ... final question (I hope!) ... RULEs vs
> > TRIGGERs?  I ended up doing it as a RULE, since I could do it without a
> > function ... but is there a reason why that is a Bad Idea?  Or in a case
> > like this, it doesn't really matter?
>
> Rules are per statement, not per row.
>
> An INSERT INTO ... SELECT ... statement will cause a single execution of
> the rule even if you insert several hundred rows.

Ah, okay, definitely not a good thing to use then ... thanks, will finish
what I started with TRIGGERs instead :)