-- TO RUN THIS SCRIPT:
-- 
-- 1) CONNECT to the database
-- 2) type :  db2 -td@ -f DB2_triggers.ddl
--
-- The following lines can be uncommented to drop the objects
--
-- drop trigger verify_credit@
-- drop trigger verify_state@
-- drop trigger restrict_delete@
-- drop trigger log_delete@
-- drop table customer_t@
-- drop table product_t@
-- drop table orders_t@
-- drop table delete_log_t@
-- drop sequence cust_seq restrict@
-- drop sequence prod_seq restrict@
-- drop sequence ord_seq restrict@

create table customer_t (
cust_id INT NOT NULL PRIMARY KEY,
company_name VARCHAR(100),
credit DECIMAL(10,2))@

create table product_t (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100))@

create table orders_t (
  order_id INT NOT NULL PRIMARY KEY, 
  cust_id INT NOT NULL, 
  product_id INT NOT NULL, 
  quantity INT NOT NULL,  
  price DECIMAL(10,2) NOT NULL,
  status CHAR(9) NOT NULL,
FOREIGN KEY (cust_id) REFERENCES customer_t,
FOREIGN KEY (product_id) REFERENCES product_t
)@
		
create table delete_log_t (
	Text	varchar(1000))@

create sequence cust_seq@
create sequence prod_seq@
create sequence ord_seq@

insert into customer_t values 
(NEXTVAL FOR cust_seq, 'Nancys Widgets', 100)@

insert into product_t values
(NEXTVAL FOR prod_seq, 'Blue Widgets')@


CREATE TRIGGER verify_credit 
NO CASCADE BEFORE INSERT ON orders_t
 REFERENCING NEW AS n 
 FOR EACH ROW MODE DB2SQL
 BEGIN ATOMIC
   DECLARE current_due DECIMAL(10,2) DEFAULT 0;
   DECLARE credit_line DECIMAL(10,2);
  /* 
   * get the customer's credit line 
   */
  SET credit_line = (SELECT credit 
			FROM customer_t c
			WHERE c.cust_id=n.cust_id);
			
  -- sum up the current amount currently due
  FOR ord_cursor AS 
  	SELECT quantity, price 
	FROM orders_t ord
	WHERE ord.cust_id=n.cust_id AND
      		status not IN ('COMPLETED','CANCELLED') DO
  	SET current_due = current_due + 
                          (ord_cursor.price * ord_cursor.quantity);
  END FOR;
  
  IF (current_due + n.price * n.quantity) > credit_line THEN
     SIGNAL SQLSTATE '80000' ('Order Exceeds credit line');
  END IF;
END@
 
 CREATE TRIGGER verify_state
 NO CASCADE BEFORE UPDATE ON orders_t
 REFERENCING OLD AS o NEW AS n 
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    IF o.status='PENDING' and n.status IN ('SHIPPED','CANCELLED') THEN
        -- valid state
    ELSEIF o.status='SHIPPED' and 
           n.status = 'DELIVERED' THEN
        -- valid state
    ELSEIF o.status='DELIVERED' and 
            n.status = 'COMPLETED' THEN
         -- valid state
    ELSE
         SIGNAL SQLSTATE '80001' ('Invalid State Transition');
    END IF;
 END@
 
 CREATE TRIGGER restrict_delete
 NO CASCADE BEFORE DELETE ON orders_t
 REFERENCING OLD AS o 
 FOR EACH ROW MODE DB2SQL
 WHEN (o.status <> 'CANCELLED')
  SIGNAL SQLSTATE '80003' ('Cannot Delete an order that has not been cancelled')@
  
CREATE TRIGGER log_delete
 AFTER DELETE ON orders_t
 REFERENCING OLD AS o
 FOR EACH ROW MODE DB2SQL
 	INSERT INTO delete_log_t VALUES (
		'Order #' || CHAR (o.order_id) ||
		'Was deleted on ' || CHAR(CURRENT TIMESTAMP))@