############ 테이블 생성 ###########
REM Script name : CreateTables.sql

connect scott/tiger

-- example table create

drop table stock;
create table stock (
        stock_id        number,
        stock_name      varchar2(10),
        stock_price    number,
        stock_quantity  number );

drop table shipment;
create table shipment (
        ship_id        number,
        stock_id        number,
        sale_price      number,
        sale_quantity  number,
        ship_date      date,
        ship_charge    number );

insert into stock values (1, '건포도', 5200, 30);
insert into stock values (2, '사과', 1000, 30);
insert into stock values (3, '배', 300, 30);
insert into stock values (4, '감', 2700, 30);
insert into stock values (5, '딸기', 1500, 30);

insert into shipment
values (1,6,2000,30,to_date('08-JUN-98', 'DD-MON-YY'), 500);



########### 트리거 생성 #################

create or replace trigger sales_record after
update on stock
for each row

declare
sales_quantity number;
sales_subtotal number;

not_sales      exception;
begin

        DBMS_OUTPUT.ENABLE;

        IF :new.stock_quantity > :old.stock_quantity then
        raise not_sales;
        end if;

        sales_quantity := :old.stock_quantity - :new.stock_quantity;
        sales_subtotal := :old.stock_price * sales_quantity;

        insert into sales_output
        values (:new.stock_id,
                sales_quantity,
                sales_subtotal );

        exception
                when not_sales then
                        DBMS_OUTPUT.PUT_LINE ('Number of stock added');
END;
/


######## sqlplus에서 입력 ##########

SQL> set serveroutput on
SQL> update stock
  2  set stock_quantity = 50
  3  where stock_id =1;
Number of stock added

1 row updated.