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
Import Notes
Reply to msg id not found: Pine.LNX.4.21.0103202311430.27234-100000@nanu.visionforisrael.comReference msg id not found: Pine.LNX.4.21.0103202311430.27234-100000@nanu.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
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
Import Notes
Reply to msg id not found: Pine.LNX.4.21.0103202348570.27436-100000@nanu.visionforisrael.comReference msg id not found: Pine.LNX.4.21.0103202348570.27436-100000@nanu.visionforisrael.com | Resolved by subject fallback
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