Thread: insert trigger

insert trigger

From
"Roberto Benitez"
Date:
I have the following situation:
 
    when a new record is inserted in to a psql table, i want to be able to update certain fields.  the way i'm doing it right now..to find the last record inserted...is by sorting the primary key (serial) in descending order and taking the first item in the list...the one at the top should be the one i just entered...and of course, i then procede to update the fieldsd i need to update..
So, basically would like to know if this will always work.  will the one at the top (the one w/ the highest primary key [serial type]) always be the one i just entered. AND does psql ALWAYS execute the trigger(s) after EVERY single record that is inerted. what would happen if [ n ] different users try to insert records simultaniously? will psql insert one-execute the trigger, insert the next-execute the trigger..and so on? or will it insert ALL [n] records AND THEN  execute the trigger?
 
i would appreciate any advice--if this would work, or if there is a better way.
 
thanks
 
 
 
 
 

Re: insert trigger

From
"Marc Mitchell"
Date:
It would appear to me that you are really doing this the hard way.

If you are trying to set/change fields in the same row that you are
inserting (say to force an add date or userid) you could use an INSTEAD
rule to transform the insert passing through the fields you don't wish to
change and transforming the ones that you do.  You turn what would be 3
statements into one.  I think an alternative to this would also be a BEFORE
INSERT trigger and function as I believe the function can simply change the
current values of the row before the insert takes place.

If you are trying to set/change fields in another table based on the values
of the row inserted, then the rule should have access to NEW. attributes
for the columns and their inserted values and there is no need for you to
go out searching (SELECTing) for them.

Hope this helps,

Marc Mitchell - Senior Application Architect
Enterprise Information Solutions, Inc.
Downers Grove, IL 60515
marcm@eisolution.com


----- Original Message -----
From: "Roberto Benitez" <RBENITEZ@houston.rr.com>
To: <pgsql-admin@postgresql.org>
Sent: Monday, November 04, 2002 9:05 PM
Subject: [ADMIN] insert trigger


I have the following situation:

    when a new record is inserted in to a psql table, i want to be able to
update certain fields.  the way i'm doing it right now..to find the last
record inserted...is by sorting the primary key (serial) in descending
order and taking the first item in the list...the one at the top should be
the one i just entered...and of course, i then procede to update the
fieldsd i need to update..
So, basically would like to know if this will always work.  will the one at
the top (the one w/ the highest primary key [serial type]) always be the
one i just entered. AND does psql ALWAYS execute the trigger(s) after EVERY
single record that is inerted. what would happen if [ n ] different users
try to insert records simultaniously? will psql insert one-execute the
trigger, insert the next-execute the trigger..and so on? or will it insert
ALL [n] records AND THEN  execute the trigger?

i would appreciate any advice--if this would work, or if there is a better
way.

thanks
rbenitez22@yahoo.com








Re: insert trigger

From
Date:
Yes Roberto ,
Trigger are very relaible and are fired in intutive manner.

(trigger is fired for every insert )

read documentation of create trigger


regds
mallah.


regds
mallah.


> I have the following situation:
>
>    when a new record is inserted in to a psql table, i want to be able to update certain
>    fields.  the way i'm doing it right now..to find the last record inserted...is by sorting
>    the primary key (serial) in descending order and taking the first item in the list...the one
>    at the top should be the one i just entered...and of course, i then procede to update the
>    fieldsd i need to update..
> So, basically would like to know if this will always work.  will the one at the top (the one w/
> the highest primary key [serial type]) always be the one i just entered. AND does psql ALWAYS
> execute the trigger(s) after EVERY single record that is inerted. what would happen if [ n ]
> different users try to insert records simultaniously? will psql insert one-execute the trigger,
> insert the next-execute the trigger..and so on? or will it insert ALL [n] records AND THEN
> execute the trigger?
>
> i would appreciate any advice--if this would work, or if there is a better way.
>
> thanks
> rbenitez22@yahoo.com



-----------------------------------------
Get your free web based email at trade-india.com.
   "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/



Re: insert trigger

From
Bruno Wolff III
Date:
On Mon, Nov 04, 2002 at 21:05:17 -0600,
  Roberto Benitez <RBENITEZ@houston.rr.com> wrote:
> I have the following situation:
>
>     when a new record is inserted in to a psql table, i want to be able to update certain fields.  the way i'm doing
itright now..to find the last record inserted...is by sorting the primary key (serial) in descending order and taking
thefirst item in the list...the one at the top should be the one i just entered...and of course, i then procede to
updatethe fieldsd i need to update.. 
> So, basically would like to know if this will always work.  will the one at the top (the one w/ the highest primary
key[serial type]) always be the one i just entered. AND does psql ALWAYS execute the trigger(s) after EVERY single
recordthat is inerted. what would happen if [ n ] different users try to insert records simultaniously? will psql
insertone-execute the trigger, insert the next-execute the trigger..and so on? or will it insert ALL [n] records AND
THEN execute the trigger? 

If you are writing an insert trigger the "new" record should have the
value being inserted into serial column. You don't want to do a select
for the highest value to get this value. Besides being less efficient,
you have a more complicated locking situation to worry about.

Re: insert trigger

From
Stephan Szabo
Date:
On Mon, 4 Nov 2002, Roberto Benitez wrote:

>     when a new record is inserted in to a psql table, i want to be
> able to update certain fields.  the way i'm doing it right now..to
> find the last record inserted...is by sorting the primary key (serial)
> in descending order and taking the first item in the list...the one at
> the top should be the one i just entered...and of course, i then
> procede to update the fieldsd i need to update..

As was said, if you're modifying the same row you can use a before trigger
and directly change the values by assigning them.  This happens before
the row is inserted so any changes made there are in the row that's
actually written.  You can also make the system ignore some row by
returning NULL.  Rules have a problem that you need to be careful to
set things up so that you don't end up with a recursive rule.

> So, basically would like to know if this will always work.  will the
> one at the top (the one w/ the highest primary key [serial type])
> always be the one i just entered. AND does psql ALWAYS execute the
> trigger(s) after EVERY single record that is inerted. what would
> happen if [ n ] different users try to insert records simultaniously?
> will psql insert one-execute the trigger, insert the next-execute the
> trigger..and so on? or will it insert ALL [n] records AND THEN execute
> the trigger?
It may interleave them.  However, I think in an after trigger, the value
of the key given to this row might be set, so you probably want to use
that in any case (esp since you can have a statement that inserts multiple
rows and you'll want to do each row individually).