I have table in my oracle database where store the data regarding property, the primary key is an ID that is an integer itself. So, whenever I want to insert a tuple in this table, I want its ID to be incremented before its inserted. So, I have made a table in my database using TOAD software. After this I wrote the following statements in the S.Q.L Editor.
CREATE SEQUENCE property_id_incrementer start with 0 increment by 1 minvalue 0;
CREATE TRIGGER increment_property_id
PROPERTY.PROPERTY_ID=property_id_incrementer.nextv al // I am not sure if this statement is correct!
The sequence is created successfully, but the trigger won't, I am sure the assignment is not correct. Should I use the 'INSERT INTO TABLE' statement here?
Another question, suppose I have five tuples in that table with ID's ranging from 1 to 5. Now, I delete the 3rd tuple with ID=3, the result would be four tuples with IDs=1,2,4,5. However, this should'nt happen this way, the IDs should be 1,2,3,4. Right?