#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2015
    Posts
    1
    Rep Power
    0

    How to get primary key value and insert into foreign key column


  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    11
    in firebird 2 you have RETURNING clause in INSERT command

    see help or faqs in FirebirdSQL.org
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    11
    Field "ID" of Customers will be AUTOINCREMENT by default in table create using Event BeforeInsert on table CUSTOMERS. LOOK AT

    CREATE TRIGGER nametrigger FOR nametable
    ACTIVE BEFORE INSERT POSITION 0
    AS
    BEGIN
    IF (NEW.ID IS NULL) THEN BEGIN
    NEW.ID = GEN_ID(GEN_PK_ID, 1);
    END

    END

    Now one new record in Customers

    INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
    VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

    Then ID will be automaticaly one sequencial number from 1 up to last integer or smallint or bigint as you defined in your create table (pay attencion that ID field is not include in FIELDS and VALUES) because TRIGGER

    now you can use the dataset (obj) options to link the table MATER and DETAIL see in help delphi

    or in SQL you can to use PARAMS FIELDS

    later insert one new record in table MASTER try...

    INSERT INTO xTable2 (IDcustomersField, ..., ..., ...., ....)
    VALUES ( :IDcustomersField, ..., ..., ...., ....);

    xTable2 may using one field ID (Primary Key) autoincrement too. this help when DELETING or UPDATING fileds in this table

    Then you can say the value to :IDcustomersField in table detail using
    xQuery.PARAM( 0 ).value or xQuery.PARAMBYNAME( IDcustomersField).value (here im using Query obj as example )

    you can to use example with DATASOURCE in code to say the value for IDcustomersField

    can to use
    Events in SQL

    can to use
    PROCEDURE IN SQL

    DONT FORGOT

    you have to create Relationship between two table ( REFERENCIAL INTEGRITY and PRIMARY KEY in mater table ) NOT NULL FOR TWO FIELDS ON TABLES

    I believe that understand me about my poor explanation (i dont speak english

IMN logo majestic logo threadwatch logo seochat tools logo