Reference no: EM13333190

Write the following constraints using triggers.

1. An employee handles at most 5 orders.

2. The required shipping date of a suborder cannot be earlier than the order date of the order to which the suborder belongs.

3. Only a staff member or a manager can handle orders. Here, the classification of an employee is one of {secretary, staff, manager}.

4. First, create a table with five columns which can keep the following audit information. Whenever a suborder is shipped later than the required shipping date, enter the following entry in the above table: (1) empId of an employee who handled the order, (2) orderNo, (3) suborderNo, (4) required shipping date, and (5) actual shipping date.

For each of the first three constraints:

If an operation is attempted (after initial population) which would violate the constraint, the operation should not be performed and an appropriate error message must be issued. Write a trigger and two test sql statements. The first statement should succeed and the second statement should fail (i.e., the second statement violates the constraint and your trigger must block it and issue an error message). Include create trigger statement and two test sql statements in your
submission

For the fourth constraint:

Whenever an insert operation (into the suborder table) violates the constraint, the information will be entered into an audit table. Note that the operation itself is allowed. Write a create table statement for the audit table. Write two test sql statements that insert tuples into the suborder table. The first insert statement has the actual shipping date that is not later than the required shipping date (so this is not recorded in the audit table). The second insert statement has the actual shipping date that is later than the required shipping date (so this should be recorded in the audit table). Include the create table statement, create trigger statement, and two test sql statements in your submission.

2-SUBMISSION:

Scripts of all FOUR triggers along with their test SQL statements and the RESULTS after you run the test in SQL developer for Oracle 11g.
3-Important:

I will test the triggers on the database I created based on tables and tuples I send you. Tables and Tuples created in Oracle 11g SQL developer. I didn't SQL plus. Please test the triggers in SQL developer for Oracle 11g.