Re: [HACKERS] triggered data change violation

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, Pennsylvania 19026

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 NULL REFERENCES 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