Thread: triggered data change violation

Added note:  The trigger is a BEFORE trigger.

---------- Forwarded message ----------
To: pgsql-interfaces@postgresql.org
Date: Tue, 20 Mar 2001 20:43:59 +0200 (IST)
Subject: triggered data change violation


ERROR:  triggered data change violation on relation "tblstsc2options"

What is this?  It doesn't happen unless I'm in a transaction.  I'm
INSERTing a record and then DELETEing it (in the same transaction) and on
delete I get this error.  If I commit and begin a new transaction before
the delete everything is fine.  Is it something my trigger causing?  I
don't have any UPDATE, INSERT, or DELETE statements in my trigger (and I
am returning old on delete).

Thanks,
-Cedar

Cedar Cox <cedarc@visionforisrael.com> writes:
> Added note:  The trigger is a BEFORE trigger.

AFAIK the "triggered data change" message comes out of the AFTER trigger
code.  You sure you don't have any AFTER triggers on the table?  Perhaps
ones added implicitly by a foreign-key constraint?
        regards, tom lane

On Tue, 20 Mar 2001, Tom Lane wrote:
> Cedar Cox <cedarc@visionforisrael.com> writes:
> > Added note:  The trigger is a BEFORE trigger.
> 
> AFAIK the "triggered data change" message comes out of the AFTER trigger
> code.  You sure you don't have any AFTER triggers on the table?  Perhaps
> ones added implicitly by a foreign-key constraint?

Not any that I wrote.  Ok, the table def is:

CREATE TABLE tblStSC2Options (       SC2OptionID int4 NOT NULL,       SC2OptionName character varying(50) NOT NULL
CHECK(SC2OptionName<>''),       SC2OptionValue float4 CHECK (SC2OptionValue>0),       SurID character varying(50) NOT
NULLREFERENCES tblStSC2 ON UPDATE 
 
CASCADE ON DELETE CASCADE,       PRIMARY KEY (SC2OptionID)
);

And there is one other table, tblListRequestSentItems, which has a field:
       SC2OptionID int4 DEFAULT 0 NOT NULL REFERENCES tblStSC2Options,

Have I answered your question?  (I think so.)

-Cedar

Tom Lane writes:

> Cedar Cox <cedarc@visionforisrael.com> writes:
> > Added note:  The trigger is a BEFORE trigger.
>
> AFAIK the "triggered data change" message comes out of the AFTER trigger
> code.  You sure you don't have any AFTER triggers on the table?  Perhaps
> ones added implicitly by a foreign-key constraint?

A "triggered data change violation" happens everytime you change twice
within a transaction a value (column) that is part of a foreign key
constraint (don't recall exactly which part).

This error shouldn't really happen, but I recall there were some
implementation and definition problems with deferred constraints.

...FAQ alert...

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Cedar Cox <cedarc@visionforisrael.com> writes:
>> AFAIK the "triggered data change" message comes out of the AFTER trigger
>> code.  You sure you don't have any AFTER triggers on the table?  Perhaps
>> ones added implicitly by a foreign-key constraint?

> Not any that I wrote.  Ok, the table def is:

> CREATE TABLE tblStSC2Options (
>         SC2OptionID int4 NOT NULL,
>         SC2OptionName character varying(50) NOT NULL CHECK (SC2OptionName<>''),
>         SC2OptionValue float4 CHECK (SC2OptionValue>0),
>         SurID character varying(50) NOT NULL REFERENCES tblStSC2 ON UPDATE 
> CASCADE ON DELETE CASCADE,                                              ^^^^^^^^^^^^^^^^^^^

Sure looks like a foreign key to me.  If you dump the table definition
with pg_dump you'll see some AFTER triggers.
        regards, tom lane

> A "triggered data change violation" happens everytime you change twice
> within a transaction a value (column) that is part of a foreign key
> constraint (don't recall exactly which part).
> 
> This error shouldn't really happen, but I recall there were some
> implementation and definition problems with deferred constraints.
> 
> ...FAQ alert...

Yes, I just got it in the TODO list a few weeks ago:

* INSERT & UPDATE/DELETE in transaction of primary key fails with  deferredTriggerGetPreviousEvent or "change
violation"[foreign]
 

I personally think we could do better on the wording of that error
message, at least until we get it fixed.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026