- Tpc R Benchmark Database Part P Partkey Id P Name P Mfgr P Brand P Type P Size P Container P Retailprice P Comment Linei 1 (51.15 KiB) Viewed 32 times
- Tpc R Benchmark Database Part P Partkey Id P Name P Mfgr P Brand P Type P Size P Container P Retailprice P Comment Linei 2 (63.69 KiB) Viewed 32 times
TPC R benchmark database PART P_PARTKEY ID P_NAME P_MFGR P_BRAND P_TYPE P_SIZE P_CONTAINER P_RETAILPRICE P COMMENT LINEITEM L_QUANTITY L_EXTENDEDPRICE L_DISCOUNT L_TAX L_RETURNFLAG L_LINESTATUS L_SHIPDATE L_COMMITDATE L_RECEIPTDATE L_SHIPINSTRUCT L_SHIPMODE L_COMMENT 0..1 A Contains PART-SHIIPPED-BY PS_AVAILQTY PS SUPPLYCOST PS COMMENT Includes 1.. Supplies L_LINENUMBER ORDERS O ORDERKEY ID O_ORDERSTATUS O TOTAL PRICE O_ORDERDATE O_ORDERPRIORITY O_CLERK 0 O_SHIPPRIORITY O_COMMENT SUPPLIER S_SUPPKEY ID S_NAME S_ADDRESS S_PHONE S_ACCTBAL S_COMMENT 1..* Belongs to NATION N_NATIONKEY ID N_NAME N_COMMENT Is-in A Submits CUSTOMER C_CUSTKEY ID C_NAME C_ADDRESS C_PHONE C_ACCTBAL C_MKTSEGMENT C_COMMENT Belongs to REGION R_REGIONKEY ID R_NAME R_COMMENT
Task 3 (5.0 marks) Stored trigger Implement a row trigger that enforces the following consistency constraint. The column c_comment in the relational table CUSTOMER of the TPCHR benchmark database is defined as 'NOT NULL'. Create a row trigger that automatically updates the values in the column (C_comment) to 'New customer was created on <the system date >' if the comment of the newly inserted record is left as NULL when a new customer is inserted into the relational table CUSTOMER. Your trigger, once activated, will enforce the consistency constraint described. When ready, process the SQL script solution 2.sql and record the results of processing in a file solution 2.1st. Deliverables Hand in the SQL script and the report from execution of scripts. Remember to set ECHO option of SQL*Plus to ON!