BEFORE Insert - Trigger problem

[zorrocrazy]

Hello,

My question is very simple. We have an application running both on oracle and db2. One of our table should only contain zero or one row at any point. When someone tries to insert a row, I want to delete the existing row and add the new one. I was able to write a trigger, BEFORE INSERT on <table name>
BEGIN ATOMIC
delete from <table name>
END

in Oracle. In DB2 that doesn't work. Does anyone have an alternative solution, the current solution i think is in the application, delete the row first and then do an insert.

I was wondering if there is a solution without changing the application code. Any help is appretiated.

Thnx.

-zorro

[frozenmist]

I think delete from table is not supported before insert.
You will have to have an after insert trigger. And to find the latest row a column with timestamp may also be required.
Then u can have:

create trigger test_trigger after insert on test_table
FOR EACH ROW MODE DB2SQL
begin atomic
delete from (select * from test_table where ttimestamp < (select max(ttimestamp) from test_table));
end

hope this is useful...

Cheers