Hello World,
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
ON PROPERTY
BEFORE INSERT
AS
BEGIN
PROPERTY.PROPERTY_ID=property_id_incrementer.nextv al // I am not sure if this statement is correct!
END
GO;

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?

How will handle this case?

Thanks.

First: you should have posted this in the Oracle forum.

If you want to change a column's value in the trigger you need to use

And please use [code] tags in the future to make your SQL code readable.

No. The value of the primary key does not have any meaning whatsoever.

Those IDs could just as well be 213264,35485,34,68757646