triggered data change violation

Started by Cedar Coxover 25 years ago6 messageshackers
Jump to latest
#1Tom Lane
tgl@sss.pgh.pa.us
In reply to: Cedar Cox (#2)
Re: [HACKERS] triggered data change violation

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

#2Cedar Cox
cedarc@visionforisrael.com

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

#3Peter Eisentraut
peter_e@gmx.net
In reply to: Tom Lane (#1)
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/

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Peter Eisentraut (#3)
Re: [HACKERS] triggered data change violation

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

#5Bruce Momjian
pgman@candle.pha.pa.us
In reply to: Peter Eisentraut (#3)
Re: Re: [HACKERS] triggered data change violation

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
#6Cedar Cox
cedarc@visionforisrael.com
In reply to: Tom Lane (#1)
Re: [HACKERS] triggered data change violation

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