How to tackle my PL/SQL triggers homework?

Question

I have the following assignment questions:

1. If inserting or updating a product and the product has multiple sizes [i.e. check product is Pita or Salad ONLY], then either:

   a. Insert a new row that has the same productTypeNO, productCode and productName and the other fields blank. You also need to assign the PK.

   b. Update the productTypeNO, productCode and productName if any of these fields is updated on ONE size for a Pita or Salad.

   create table product
   (
   
   productNO int, 
   productTypeNO int,
   productCode char(10),
   productName varchar2(60), 
   prodSize varchar2(20), 
   unitCost numeric(4,2)
   )
   /
   
   create table productType
   (
   
   productTypeNO int,
   prodType varchar2(5), 
   prodTypeDesc varchar2(60),
   prodSizeDefault varchar2(20),
   prodSizeSmall varchar2(20), 
   status char(1),
   constraint productTypePK_invalid PRIMARY KEY (productTypeNO)
   )
   /
   

The productType contains if a product has a multiple the prodSizeDefault and the prodSizeSmall. So if I'm going to insert a product in the product table, how can I add new row that has the same productTypeNO, productCode and productName. And how to assign primary key.

Answer 1

You can use :NEW or :OLD pseudo columns.

Create sequence to assign unique value to your primary key column.