Thread: trigger AFTER INSERT

trigger AFTER INSERT

From
"Josi Perez (3T Systems)"
Date:
Hi,

I think that I have a conceptual problem and I hope anyone could help me.

If I write a trigger AFTER INSERT and I have one error in this trigger, the record (that I think was already inserted) is not inserted.
Example:

INSERT INTO tableX <record1>
trigger sumRecords AFTER INSERT under tableX to accumulate the records inserted by hour
if there is an error in the commands executed for this record, <record1> is not in the tableX

If this occurs in a trigger BEFORE INSERT, I can understand, but, if it is AFTER INSERT, I believe that the record is already inserted.
What is wrong on it?

Thanks in advance
Josi Perez
#avg_ls_inline_popup { position:absolute; z-index:9999; padding: 0px 0px; margin-left: 0px; margin-top: 0px; width: 240px; overflow: hidden; word-wrap: break-word; color: black; font-size: 10px; text-align: left; line-height: 13px;}

Re: trigger AFTER INSERT

From
Tom Lane
Date:
"Josi Perez (3T Systems)" <josiperez3t@gmail.com> writes:
> If I write a trigger AFTER INSERT and I have one error in this trigger, the
> record (that I think was already inserted) is not inserted.

Sure, because the whole transaction is rolled back on error.

            regards, tom lane

Re: trigger AFTER INSERT

From
"Josi Perez (3T Systems)"
Date:
Thank you for your answer.
Without considering the errors, the transaction should not start after the AFTER INSERT?
Josi Perez


2010/8/12 Tom Lane <tgl@sss.pgh.pa.us>
"Josi Perez (3T Systems)" <josiperez3t@gmail.com> writes:
> If I write a trigger AFTER INSERT and I have one error in this trigger, the
> record (that I think was already inserted) is not inserted.

Sure, because the whole transaction is rolled back on error.

                       regards, tom lane

#avg_ls_inline_popup { position:absolute; z-index:9999; padding: 0px 0px; margin-left: 0px; margin-top: 0px; width: 240px; overflow: hidden; word-wrap: break-word; color: black; font-size: 10px; text-align: left; line-height: 13px;}

Re: trigger AFTER INSERT

From
"Kevin Grittner"
Date:
"Josi Perez (3T Systems)" <josiperez3t@gmail.com> wrote:

> Without considering the errors, the transaction should not start
> after the AFTER INSERT?

Anything happening in a trigger is always part of the same database
transaction as the database action which fired the trigger.

-Kevin