db-derby-user mailing list archives

On 10/10/2011 22:05, AmFreak@web.de wrote:
>
> Hi,
>
> i need a table with 1 or 0 rows in it. So that after 1 row is inserted
> the table can only be updated or the row deleted, but there can never be
> a row inserted.
> I came as far as this:
>
> CREATE TABLE test
> (field1 INTEGER NOT NULL
> CHECK (field1 = 1) UNIQUE)
>
> but that would (obviously) mean that field1 could only have the value 1.
>
> I would need something like check(COUNT(*) FROM test <= 1).
>
> Thanks for any suggestions / answers
Use a trigger:
CREATE TRIGGER remove_test
AFTER INSERT ON test
REFERENCING NEW AS ins
FOR EACH ROW MODE DB2SQL
DELETE FROM test
WHERE field1=ins.field1
AND (SELECT COUNT(*) FROM test) > 1;
This will delete the inserted row if there is more than one row in the
table.
HTH,
------------------------------------------------------------------------
John English | My old University of Brighton home page is still here:
| http://www.cem.brighton.ac.uk/staff/je/
------------------------------------------------------------------------