我有一个视图View_Emp,抓4个Table的资料,我想建立一个对应View_Emp的Trigger,只要Table变化,View_Emp跟着变,只要View_Emp一变,便触发Trigger,写入到另外一个Table一些日志资料,请问视图需要加什么参数,就是在视图上怎么触发
解决方案 »
- 如何将一列查询到的所有值添加到另一表的某个字段?
- Oracle的Java Stored Procedure
- Oracle的问题
- 主键列排序
- 对于医学信息管理专业,怎样提高自己竞争水平
- oracle中存储过程返回list,出现“子程序或游标'XXXX' 已在程序包说明中说明,必须在程序包体中对其进行定义。”
- select出重复的数据,有没办法只保留最近的一条记录
- TNSLSNR.exe应用程序错误(散高分,在线等待,急!急!急!)
- 联机日志损坏,怎么恢复?
- 请问Oracle9i在Windows2000 Server上双机热备的方案
- 关于materialized view的超奇怪问题,高手请进来看看
- 向数据库中增加CLOB字段的问题
因为Trigger比较耗资源(我们的数据都是上亿级别的,pm因此对Trigger深恶痛绝),所以一切都在存储过程,程序包里面搞定。
In this example, an oe.order_info view is created to display information about customers and their orders:CREATE VIEW order_info AS
SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
o.order_id, o.order_date, o.order_status
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;
Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view (the PL/SQL trigger implementation is shown in italics):CREATE OR REPLACE TRIGGER order_info_insert
INSTEAD OF INSERT ON order_info
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO customers
(customer_id, cust_last_name, cust_first_name)
VALUES (
:new.customer_id,
:new.cust_last_name,
:new.cust_first_name);
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (
:new.order_id,
:new.order_date,
:new.customer_id);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate customer or order ID');
END order_info_insert;
/