1. Log on to your Oracle ApEx account.

If you dont have a CUSTOMERS table, create one.

Table:

DEMO_CUSTOMERS

Column	Data Type	Length	Precision	Scale	Nullable
CUSTOMER_ID	NUMBER	22	-	-	No
CUST_FIRST_NAME	VARCHAR2	20	-	-	No
CUST_LAST_NAME	VARCHAR2	20	-	-	No
CUST_STREET_ADDRESS1	VARCHAR2	60	-	-	Yes
CUST_STREET_ADDRESS2	VARCHAR2	60	-	-	Yes
CUST_CITY	VARCHAR2	30	-	-	Yes
CUST_STATE	VARCHAR2	2	-	-	Yes
CUST_POSTAL_CODE	VARCHAR2	10	-	-	Yes
CUST_EMAIL	VARCHAR2	30	-	-	Yes
PHONE_NUMBER1	VARCHAR2	25	-	-	Yes
PHONE_NUMBER2	VARCHAR2	25	-	-	Yes
URL	VARCHAR2	100	-	-	Yes
CREDIT_LIMIT	NUMBER	22	9	2	Yes
TAGS	VARCHAR2	4000	-	-	Yes

And the data:

CUST_FIRST_NAME CUST_LAST_NAME CUST_EMAIL DATE_OF_BIRTH CREDIT_LIMIT

Sally Edwards Sally.Edwards@TURNSTONE.COM 10/02/2080 50000

Bo Hitchcock Bo.Hitchcock@ANHINGA.COM 10/03/2054 25000

Charlotte Buckley Charlotte.Buckley@PINTAIL.COM 10/04/2049 10000

Create a SQL file called ps17a.sql. With this SQL file create a new table called cardholder with these columns:

card_number (this will be the primary key for this table)

customer_id (this will be the FOREIGN KEY referencing the customer table)

credit_limit

Here is the basic syntax for creating a foreign key in Oracle SQL:

constraint fk_foreign_key_name foreign key (column_from_this_table)

REFERENCES other_table_name(other_column_name)

http://www.w3schools.com/sql/sql_foreignkey.asp

2. Create a sequence for the card_holder table named seq_cardholder. This sequence will emulate the sixteen digits found on a credit card so START the sequence with 1.

This is a good reference for creating sequences:

https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6014.htm

3. Create a TRIGGER named tgr_customer_insert that will fire AFTER a row is inserted into the customers table.

The trigger can be created after you create the cardholder table, so it can be in the same ps17a.sql file just created. This trigger will insert a row into the cardholder table when a row is inserted into the temp_table_customers table. Here are the columns to insert:

card_number (this is inserted using the seq_cardholder sequence number)

customer_id (this is a bind variable from the temp_table_customer table using the :new.column_name syntax)

credit_limit (this is a bind variable from the temp_table_customer table using the :new.column_name syntax)

Here is a good link describing how to create triggers in Oracle SQL:

http://www.techonthenet.com/oracle/triggers/after_insert.php

3. Test out your trigger by inserting a new row into the customers table. This should create a new row in the cardholder table automatically. Include a screenshot of the SELECT from the cardholder table.