Re: Triggered data change violation

I have a similiar problem.

I should first say I'm learning about databases and SQL as I go along so
what I'm trying to do may be just plain silly (but it seems OK to me).

My problem can best be explained by  this script ...

CREATE TABLE test (
id INTEGER PRIMARY KEY,
value VARCHAR(25)
);

CREATE TABLE test2 (
id INTEGER NOT NULL REFERENCES test(id) ON DELETE CASCADE
);

INSERT INTO test VALUES ( 2, 'TWO' );
INSERT INTO test2 VALUES ( 2 );
BEGIN;
INSERT INTO test VALUES ( 4, 'FOUR' );
INSERT INTO test2 VALUES ( 4 );
DELETE FROM test WHERE id = 2;                    - this delete occurs OK
DELETE FROM test WHERE id = 4;
                   - this delete causes "ERROR:  triggered data change
violation on relation "test"
COMMIT;

I'm adding then deleting the same rows within a transaction. Should I be able
to do this? It
works fine if I change test2 to

CREATE TABLE test2 (
id INTEGER
);

which suggests the problem is with triggers.

regards, Alasdair.


Peter Vazsonyi wrote:

> Hello!
>
> I have a table, with an update trigger (it change the value of
> 'last_modify' field to current timestamp)
> Sometimes if i try delete rows in this table (before delete, there are some
> inserts after a 'begin') i get this change violation error. I don't now
> why.
> Can somebody help me?
>
> Thank's
> --
>  nek.
>
> (postgresql 7.0.2)