SQL>
SQL> CREATE TABLE gifts (
2 gift_id INTEGER CONSTRAINT gifts_pk PRIMARY KEY,
3 gift_type_id INTEGER ,
4 name VARCHAR2(30) NOT NULL,
5 description VARCHAR2(50),
6 price NUMBER(5, 2)
7 );
Table created.
SQL>
SQL>
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (1, 1, 'Flower', 'Birthday', 19.95);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (2, 1, 'Computer', 'New Year', 30.00);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (3, 2, 'iPod', 'Birthday', 25.99);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (4, 2, 'iPhone', 'New Year', 13.95);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (5, 2, 'Book', 'Birthday', 49.99);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER before_gift_price_update
2 BEFORE UPDATE OF price
3 ON gifts
4 FOR EACH ROW WHEN (new.price < old.price * 0.75)
5 BEGIN
6 dbms_output.put_line('gift_id = ' || :old.gift_id);
7 dbms_output.put_line('Old price = ' || :old.price);
8 dbms_output.put_line('New price = ' || :new.price);
9 dbms_output.put_line('The price reduction is more than 25%');
10
11 INSERT INTO gift_price_audit (gift_id, old_price, new_price)VALUES(:old.gift_id, :old.price, :new.price);
12 END before_gift_price_update;
13 /
Warning: Trigger created with compilation errors.
SQL> show errors
Errors for TRIGGER BEFORE_GIFT_PRICE_UPDATE:
LINE/COL
---------------------------------------------------------------------------------
ERROR
------------------------------------------------------------------------------------------------------------------------------------------------------
7/3
PL/SQL: SQL Statement ignored
7/15
PL/SQL: ORA-00942: table or view does not exist
SQL>
SQL>
SQL> drop table gifts;
Table dropped.