Oracle 11g Lab 5-trigger usage

Lab requirements:

Use of trigger in Experiment 5

Lab Purpose

1. Understand the concepts, functions, and categories of triggers;

2. Master trigger creation and use;

 

Lab content

1. Create a table orders: used to store the order list information; Table order_items: used to store the details of a single order. The structure is as follows:

Table 1 orders table structure

Field name	Field Type	Field width	Description
Id	NUMBER	20	Order Number (primary key)
Order_date	Date		Order Date (not empty)
User_name	VARCHAR2	10	Customer name (not blank)
City	VARCHAR2	20	Customer's city (Shenyang by default)

Table 2 order_items table structure

Field name	Field Type	Field width	Description
Order_id	NUMBER	20	Order Number (primary key)
Product_NAME	VARCHAR2	20	Product Name (not blank)
Quantity	NUMBER	4	Quantity (greater than 0, less than 100)
Unit_price	NUMBER	(10, 2)	Unit Price

2. Create a trigger to save your modifications to the orders table to the log table (created by yourself.

3. Test the trigger.

4. Create a trigger. Only scott users can modify the table orders.

5. Test the trigger.

6. delete a trigger.

7. Create view_user_order in Order Details View, including user name, order number, product name, quantity, and unit price.

8. Create a trigger to update the view, update, and table data.

9. Test the trigger.

10. delete a trigger.

 

Lab answer:

 

-- Create an orders table create table orders (id NUMBER (20) primary key not null, order_date date not null, user_name varchar2 (10) not null, city varchar2 (20) default 'shenyang '); -- create order_items table create table order_items (order_id number (20) primary key, product_NAME varchar2 (20) not null, quantity number (4) check (quantity BETWEEN 0 AND 100), unit_price number (); -- 2. Create a trigger to save the user's modifications to the orders table to the log table (created by yourself. -- Create table T_LOG (RID VARCHAR2 (32), NAME VARCHAR2 (1000), rq date default sysdate, OLDVALUE VARCHAR2 (255), NEWVALUE VARCHAR2 (255 )); -- target table create table orders (id NUMBER (20) primary key not null, order_date date not null, user_name varchar2 (10) not null, city varchar2 (20) default 'shenyang '); create or replace trigger order_loggin after update on orders referencing new as New old as Old FOR EACH ROW Declare begin if: new. city <>: old. city then insert into T_LOG (rid, name, newvalue, oldvalue) values (: new. id, 'city',: new. city,: old. city); end if; if: new. user_name <>: old. user_name then insert into t_log (rid, name, newvalue, oldvalue) values (: new. id, 'name',: new. user_name,: old. user_name); end if; if: new. order_date <>: old. order_date then insert into t_log (rid, name, newvalue, oldvalue) values (: new. id, 'order date ',: New. order_date,: old. order_date); end if; END order_loggin; -- 3. Test the trigger. Update orders set user_name = '000000' where id = '1'; SELECT * from t_log; -- 4. Create a trigger. Only scott users can modify the table orders. Create or replaceTRIGGER alter_orderBEFORE INSERT or UPDATE or delete on orders declare user_name VARCHAR2 (30); BEGINselect user into user_name FROM dual; if (lower (user_name) <> 'Scott ') THEN RAISE_APPLICATION_ERROR (-20001, 'user cannot modify orders table'); end if; END alter_order; -- 5. Test trigger. Update orders set user_name = '000000' where id = '1'; -- 6. delete a trigger. Drop trigger alter_order; create table orders (id NUMBER (20) identified (1,1) primary key not null, order_date date not null, user_name varchar2 (10) not null, city varchar2 (20) default 'shenyang '); create table order_items (order_id number (20) primary key, product_NAME varchar2 (20) not null, quantity number (4) check (quantity BETWEEN 0 AND 100 ), unit_price number (); -- 7. Create a user to sign the order Details View view_user_order, including Account name, order number, product name, quantity, and unit price. Create or replace VIEW view_user_orderas select user_name, order_id, product_NAME, quantity, unit_price from order_items join orders on (order_items.order_id = orders. id); -- Test SELECT * FROM view_user_order; -- 8. Create a trigger to update the view and table data. Create or replaceTRIGGER view_user_order_trigger instead of insert on view_user_order for each row begin insert into orders (id, user_name) values (: new. order_id,: new. user_name); insert into order_items (order_id, product_NAME, quantity, unit_price) VALUES (: new. order_id,: new. product_NAME,: new. quantity,: new. unit_price); END view_user_order_trigger; -- 9. Test the trigger. Insert into view_user_order values ('000000', 4, 'phone', 12310); insert into view_user_order values ('000000', 5, 'phone', 12311 ); -- 10. delete a trigger. Drop trigger view_user_order_trigger;