Results 1 to 2 of 2

Thread: SQL triggers

  1. #1
    Join Date
    May 2011

    Red face Unanswered: SQL triggers

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

    How will handle this case?


  2. #2
    Join Date
    Nov 2003
    Provided Answers: 8
    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

    :NEW.property_id := property_id_incrementer.nextval;
    And please use [code] tags in the future to make your SQL code readable.

    However, this should'nt happen this way, the IDs should be 1,2,3,4. Right?
    No. The value of the primary key does not have any meaning whatsoever.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts