实验五 触发器的使用

　　实验目的

　　1、 理解触发器的概念、作用及分类;

　　2、 掌握触发器的创建、使用;

　　实验内容

　　1、 建立表orders：用于存储订单列表信息;表order_items：用于存储单个订单的详细信息。其结构分别为：

　　表1 orders表结构

　　表2 order_items表结构

　　2、 创建触发器，将用户对orders表的修改，保存到日志表(自己创建)中。

　　3、 测试触发器。

　　4、 创建触发器，限制只有scott用户可以修改表orders。

　　5、 测试触发器。

　　6、 删除触发器。

　　7、 创建用户签订订单详细信息视图view_user_order，包括用户名、订单号、商品名、数量、单价。

　　8、 创建触发器，实现更新视图，更新及表数据。

　　9、 测试触发器。

　　10、删除触发器。

　　实验答案：

　　--创建orders表

　　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 '沈阳'

　　);

　　--创建order_items表

　　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(10,2)

　　);

　　--2、 创建触发器，将用户对orders表的修改，保存到日志表(自己创建)中。

　　--日志表

　　CREATE TABLE T_LOG

　　(

　　RID VARCHAR2(32),

　　NAME VARCHAR2(1000),

　　RQ DATE DEFAULT sysdate,

　　OLDVALUE VARCHAR2(255),

　　NEWVALUE VARCHAR2(255)

　　);

　　--目标表

　　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 '沈阳'

　　);

　　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,'城市', :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,'姓名',: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,'订单日期',:new.order_date,:old.order_date);

　　end if;

　　END order_loggin;

--3、 测试触发器。

　　update orders set user_name='213456' where id='1';

　　SELECT * from t_log;

　　--4、 创建触发器，限制只有scott用户可以修改表orders。

　　create or replace

　　TRIGGER alter_order

　　BEFORE INSERT or UPDATE or DELETE

　　ON orders

　　declare

　　user_name VARCHAR2(30);

　　BEGIN

　　select user into user_name FROM dual;

　　if(lower(user_name)<>'scott') THEN

　　RAISE_APPLICATION_ERROR(-20001,'改用户不能修改orders表');

　　END IF;

　　END alter_order;

　　--5、 测试触发器。

　　update orders set user_name='111' where id='1';

　　--6、 删除触发器。

　　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 '沈阳'

　　);

　　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(10,2)

　　);

　　--7、 创建用户签订订单详细信息视图view_user_order，包括用户名、订单号、商品名、数量、单价。

　　create or replace VIEW view_user_order

　　as select user_name,order_id,product_NAME,quantity,unit_price from order_items join orders on(order_items.order_id=orders.id);

　　--测试

　　SELECT * FROM view_user_order;

　　--8、 创建触发器，实现更新视图，及更新表数据。

　　create or replace

　　TRIGGER 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、 测试触发器。

　　insert into view_user_order values('12310',4,'电话',20,1500);

　　insert into view_user_order values('12311',5,'电话',20,1500);

　　--10、 删除触发器。

　　DROP TRIGGER view_user_order_trigger;