Trigger Before Insert on multiple tables

Question

I am fairly new to triggers and PL/SQL, so my question is mainly one of design.

I am creating a set of tables to represent ticket purchases. There are three types of tickets:

Ticket #1: Price, Quantity, Time
Ticket #2: Price, Quantity, Time, Seating, Refundable
Ticket #3: Price, Quantity, Time, Food , Drink

So I have created three tables: (I believe this is called, normalizing):
Table1 has columns Price, Quantity and Time
Table2 has Seating and Refundable
Table3 has Food and Drink

I have inserted a primary key column into Table1 and am using foreign keys in Table2 and Table3 which point to Table1's PK.

The Plan: When a user purchases a ticket, I insert a record into the appropriate table(s). For instance, if the user purchases:
Ticket #1, I insert a record into Table1
Ticket #2, I insert a record into Table1 and Table2
Ticket #3, I insert a record into Table1 and Table3

The Problem: How can I receive all the data for a ticket not of type 1, and then split the parameters to insert into the separate tables. For instance, when I try to create a trigger for Table2, that trigger can only receive the parameters that match what Table2 columns has. How can I receive the data for Table1?

An example of a complete purchase of ticket type 2.
User purchases ticket online -> web form stuff happends... -> dao sends ONE insert command to the database -> trigger for Table2 kicks off and validates info for Table1 and Table2.

Thanks!

And how does this procedure fire on insert without being called from a trigger? And how does it acquire all the parameters? — Seanimus
Don't start with an insert statement and then call triggers - call the procedure with the values and then get the procedure to issue the insert(s) to the correct tables depending on the values provided to the procedure. — MT0

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2016-02-07T01:47:52

You can design this in any way you want, but given that there's not much difference between the ticket types I'd have a single table:

CREATE TABLE TICKET
 (ID_TICKET        NUMBER
    CONSTRAINT PK_TICKET
      PRIMARY KEY
      USING INDEX,
  TICKET_TYPE      NUMBER
    NOT NULL
    CONSTRAINT TICKET_CK1
      CHECK(TICKET_TYPE IN (1, 2, 3)),
  PRICE            NUMBER
    NOT NULL,
  QUANTITY         NUMBER
    NOT NULL,
  DEPARTURE_TIME   DATE
    NOT NULL,
  SEATING          NUMBER
    CONSTRAINT TICKET_CK2
      CHECK(1 = CASE TICKET_TYPE
                  WHEN 1 THEN CASE
                                WHEN SEATING IS NULL
                                  THEN 1
                                  ELSE 0
                              END
                  WHEN 2 THEN CASE
                                WHEN SEATING IS NULL
                                  THEN 0
                                  ELSE 1
                              END
                  WHEN 3 THEN CASE
                                WHEN SEATING IS NULL
                                  THEN 1
                                  ELSE 0
                              END
                END),
  REFUNDABLE_INDC  CHAR(1)
    NOT NULL
    CONSTRAINT TICKET_CK3
      CHECK(REFUNDABLE_INDC = CASE TICKET_TYPE
                                WHEN 1 THEN 'N'
                                WHEN 2 THEN 'Y'
                                WHEN 3 THEN 'N'
                              END),
  FOOD             CHAR(1)
    NOT NULL
    CONSTRAINT TICKET_CK4
      CHECK(FOOD = CASE TICKET_TYPE
                     WHEN 1 THEN 'N'
                     WHEN 2 THEN 'N'
                     WHEN 3 THEN 'Y'
                   END),
  DRINK            CHAR(1)
    NOT NULL
    CONSTRAINT TICKET_CK5
      CHECK(DRINK = CASE TICKET_TYPE
                      WHEN 1 THEN 'N'
                      WHEN 2 THEN 'N'
                      WHEN 3 THEN 'Y'
                    END));

Here CHECK constraints are used to ensure that only the appropriate fields are filled in, based on the ticket type.

Best of luck.

Trigger Before Insert on multiple tables

1 Answers