2.2.3. Secondary Tables

The script for creating the INVOICE table:

  1. CREATE GENERATOR GEN_INVOICE_ID;
  3. CREATE TABLE INVOICE (
  4.   INVOICE_ID INTEGER NOT NULL,
  5.   CUSTOMER_ID INTEGER NOT NULL,
  6.   INVOICE_DATE TIMESTAMP,
  7.   TOTAL_SALE D_MONEY,
  8.   PAID D_BOOLEAN DEFAULT 0 NOT NULL,
  9.   CONSTRAINT PK_INVOICE PRIMARY KEY (INVOICE_ID)
  10. );
  12. ALTER TABLE INVOICE ADD CONSTRAINT FK_INVOCE_CUSTOMER
  13. FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID);
  15. CREATE INDEX INVOICE_IDX_DATE ON INVOICE (INVOICE_DATE);
  17. SET TERM ^;
  19. CREATE OR ALTER TRIGGER INVOICE_BI FOR INVOICE
  20. ACTIVE BEFORE INSERT POSITION 0
  21. AS
  22. BEGIN
  23.   IF (NEW.INVOICE_ID IS NULL) THEN
  24.     NEW.INVOICE_ID = GEN_ID(GEN_INVOICE_ID,1);
  25. END
  26. ^
  28. SET TERM ;^
  30. COMMENT ON TABLE INVOICE IS
  31. 'Invoices';
  33. COMMENT ON COLUMN INVOICE.INVOICE_ID IS
  34. 'Invoice number';
  36. COMMENT ON COLUMN INVOICE.CUSTOMER_ID IS
  37. 'Customer Id';
  39. COMMENT ON COLUMN INVOICE.INVOICE_DATE IS
  40. 'The date of issuance invoices';
  42. COMMENT ON COLUMN INVOICE.TOTAL_SALE IS
  43. 'Total sum';
  45. COMMENT ON COLUMN INVOICE.PAID IS
  46. 'Paid';

The INVOICE_DATE column is indexed because we will be filtering invoices by date to enable the records to be selected by a work period that will be application-defined by a start date and an end date.

In Firebird 3.0, you need to add the command for granting the USAGE privilege for a sequence (generator) to the script:

  1. GRANT USAGE ON SEQUENCE GEN_INVOICE_ID TO TRIGGER INVOICE_BI;

The script for creating the INVOICE_LINE table:

  1. CREATE GENERATOR GEN_INVOICE_LINE_ID;
  3. CREATE TABLE INVOICE_LINE (
  4.   INVOICE_LINE_ID INTEGER NOT NULL,
  5.   INVOICE_ID INTEGER NOT NULL,
  6.   PRODUCT_ID INTEGER NOT NULL,
  7.   QUANTITY NUMERIC(15,0) NOT NULL,
  8.   SALE_PRICE D_MONEY NOT NULL,
  9.   CONSTRAINT PK_INVOICE_LINE PRIMARY KEY (INVOICE_LINE_ID)
  10. );
  12. ALTER TABLE INVOICE_LINE ADD CONSTRAINT FK_INVOICE_LINE_INVOICE
  13. FOREIGN KEY (INVOICE_ID) REFERENCES INVOICE (INVOICE_ID);
  15. ALTER TABLE INVOICE_LINE ADD CONSTRAINT FK_INVOICE_LINE_PRODUCT
  16. FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID);
  18. SET TERM ^;
  20. CREATE OR ALTER TRIGGER INVOICE_LINE_BI FOR INVOICE_LINE
  21. ACTIVE BEFORE INSERT POSITION 0
  22. AS
  23. BEGIN
  24.   IF (NEW.INVOICE_LINE_ID IS NULL) THEN
  25.     NEW.INVOICE_LINE_ID = NEXT VALUE FOR GEN_INVOICE_LINE_ID;
  26. END
  27. ^
  29. SET TERM ;^
  31. COMMENT ON TABLE INVOICE_LINE IS
  32. 'Invoice lines';
  34. COMMENT ON COLUMN INVOICE_LINE.INVOICE_LINE_ID IS
  35. 'Invoice line Id';
  37. COMMENT ON COLUMN INVOICE_LINE.INVOICE_ID IS
  38. 'Invoice number';
  40. COMMENT ON COLUMN INVOICE_LINE.PRODUCT_ID IS
  41. 'Product Id';
  43. COMMENT ON COLUMN INVOICE_LINE.QUANTITY IS
  44. 'Quantity';
  46. COMMENT ON COLUMN INVOICE_LINE.SALE_PRICE IS
  47. 'Price';

In Firebird 3.0, you need to add the command for granting the USAGE privilege for a sequence (generator) to the script:

  1. GRANT USAGE ON SEQUENCE GEN_INVOICE_LINE_ID TO TRIGGER INVOICE_LINE_BI;