Re: Triggered data change violation

(Apologies if this is appears three times ...)

I think the problem we're having is the same as described here ...

http://www.postgresql.org/docs/pgsql/doc/TODO.detail/foreign

regards, Alasdair.

Peter Vazsonyi wrote:


Alasdair I MacLeod wrote:

> 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)