关于建立表触发器问题?

wyq21973

触发器没有通过编译，你把编译出错提示贴出来看看。

SQL*Plus中用show errors。

msnch

最初由 wyq21973 发布
[B]

INSTEAD OF触发器只有在对VIEW进行更新时才会触发。 [/B]

  我对此VIEW的一个表进行更新,难道不会点火吗?

msnch

最初由 anzi_oracle 发布
[B]wyq21973 :你好！
    我按你提供的代码：

CREATE TRIGGER ZYGS.A_TRG BEFORE INSERT OR UPDATE OR DELETE
ON ZYGS.A
FOR EACH ROW
begin
if inserting then
insert into b values (:new.id);
end if;
if updating then
update b set id = :new.id where id = ld.id;
end if;
if deleting then
delete from b where id = ld.id;
end if;
end;

建立触发器后，执行insert into a values (1) 后，oracle报错:
ORA-04098:  触发器'A-TRG'无效且未通过重新验证。
请教如何解决？谢谢 [/B]

是不是你把：old 变成ld?????

wyq21973

最初由 msnch 发布
[B]

是不是你把：old 变成ld????? [/B]

这是系统的一个问题，正常你写一个：o会变成一个图标。
你这里是用：替了:。

wyq21973

最初由 msnch 发布
[B]

  我对此VIEW的一个表进行更新,难道不会点火吗? [/B]

关于这个问题文档中只找到下面的说明：

INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.

INSTEAD OF 触发器应该只适用于用户没有表的授权的时候给他创建一个视图同时创建INSTEAD OF触发器。

husthxd

Notes on Updatable Views

An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.

To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:

Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause (an unnested collection), then the view is not inherently updatable.


The view must not contain any of the following constructs:

A set operator
a DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions, as documented in Oracle Database Administrator's Guide


In addition, if an inherently updatable view contains pseudocolumns or expressions, then you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions.


If you want a join view to be updatable, then all of the following conditions must be true:

1.  The DML statement must affect only one table underlying the join.

2.  For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.

3.  For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view was created WITH CHECK OPTION, then join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.


For a DELETE statement, if the join results in more than one key-preserved table, then Oracle Database deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION.


参考:http://www.psoug.org/reference/instead_of_trigger.html

anzi_oracl

CREATE TRIGGER ZYGS.A_TRG BEFORE INSERT OR UPDATE OR DELETE
ON ZYGS.A
FOR EACH ROW
begin
if inserting then
insert into b values (:new.id);
end if;
if updating then
update b set id = :new.id where id =old.id;
end if;
if deleting then
delete from b where id =old.id;
end if;
end;   
   
    建立触发器后，执行insert into a values (1) 后，oracle报错:
ORA-04098: 触发器'A-TRG'无效且未通过重新验证。
请教如何解决？谢谢

rollingpig

show errors TRIGGER ZYGS.A_TRG
先，不让谁都帮不了你。

wyq21973

最初由 anzi_oracle 发布
[B]CREATE TRIGGER ZYGS.A_TRG BEFORE INSERT OR UPDATE OR DELETE
ON ZYGS.A
FOR EACH ROW
begin
if inserting then
insert into b values (:new.id);
end if;
if updating then
update b set id = :new.id where id =old.id;
end if;
if deleting then
delete from b where id =old.id;
end if;
end;   
   
    建立触发器后，执行insert into a values (1) 后，oracle报错:
ORA-04098: 触发器'A-TRG'无效且未通过重新验证。
请教如何解决？谢谢 [/B]

old前面再加上一个:符号。
上论坛讨论相关主题前多看看书中相关的基础知识。

anzi_oracl

old前面再加上一个:符号。
上论坛讨论相关主题前多看看书中相关的基础知识。

wyq21973,你好，你的批评是我前进的动力！！！