7. Create a trigger named Check_Availability. The trigger raises an application error when a product is out of stock or not enough to fulfil an order. It should display a message on the estimated date when the product will be available (use expected delivery date). Include the following test statement in your script file: Insert Into order_lines values (388,1023,100,20,2,160);

SQL

-- Use an anonymous PL/SQL script to -- drop all tables and sequences in the current schema and -- suppress any error messages that may displayed -- if these objects don't exist BEGIN EXECUTE IMMEDIATE 'DROP TABLE Order_Lines'; EXECUTE IMMEDIATE 'DROP TABLE Products'; EXECUTE IMMEDIATE 'DROP TABLE Orders'; EXECUTE IMMEDIATE 'DROP TABLE Payment_method'; EXECUTE IMMEDIATE 'DROP TABLE Shipment_method'; EXECUTE IMMEDIATE 'DROP TABLE CATEGORIES'; EXECUTE IMMEDIATE 'DROP TABLE Customers'; EXECUTE IMMEDIATE 'DROP TABLE Employees'; EXECUTE IMMEDIATE 'DROP TABLE Jobs'; EXECUTE IMMEDIATE 'DROP TABLE Departments'; EXECUTE IMMEDIATE 'DROP TABLE Product_Inventory'; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(''); END; /

CREATE TABLE DEPARTMENTS ( DEPARTMENT_ID NUMBER(2) NOT NULL, DEPARTMENT_NAME VARCHAR2(20) NULL, CITY VARCHAR2(30) NULL, STATE CHAR(2) NULL );

ALTER TABLE DEPARTMENTS ADD CONSTRAINT Departments_PK PRIMARY KEY (DEPARTMENT_ID);