从一个侦听另一个用户的事件的用户在Oracle上创建触发器

问题描述：

我有两个Oracle模式A&B.

I have two Oracle schemas A & B.

我可以在架构A中创建一个触发器，以监听架构B上的创建表吗?如果是，我需要做哪些补助?

Can I create a trigger in schema A that will listen to Create Tables on Schema B? If yes, which grants do I need to do?

答

是的，可以.用户a需要具有创建任何触发器:

Yes, you can. The user a needs to have CREATE ANY TRIGGER:

作为DBA:

CREATE USER a IDENTIFIED BY a;
GRANT CREATE SESSION, CREATE ANY TRIGGER TO a;

CREATE USER b IDENTIFIED BY b;
GRANT CREATE SESSION TO b;

作为:

CREATE OR REPLACE TRIGGER a.create_trigger
  BEFORE CREATE ON b.SCHEMA
BEGIN
  raise_application_error(num => -20000,  msg=> 'create_trigger vetoes');
END;
/

为b:

CREATE TABLE t(i INT);
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: create_trigger vetoes
ORA-06512: at line 2

您必须微调此触发器，它不仅会针对 CREATE TABLE 触发，还会针对所有 CREATE 语句触发...

You'll have to fine-tune this trigger, it will fire not only for CREATE TABLE but for all CREATE statements...

从一个侦听另一个用户的事件的用户在Oracle上创建触发器

相关推荐