Oracle：是否可以在触发器中创建角色？

问题描述：

我有一个单独的表，其中包含一些数据，每次在表中插入新行时，我都想创建一个新角色。

I have a separate table with some data and I would like to a create a new role each time a new row is inserted in that table.

我创建了以下触发器：

create or replace 
TRIGGER TestTrigger
BEFORE INSERT ON TestTable
REFERENCING OLD AS OLD NEW AS NEW 

FOR EACH ROW BEGIN 
    create role :New.RoleName;
END;

但触发器未编译。
我有错误：PLS-00103：PLS-00103：遇到以下情况时遇到符号 CREATE ：（如果循环模式为null，则返回goto，如果使用< <继续关闭当前删除取回锁插入打开回滚保存点集sql执行提交forall合并管道清除

but the trigger doesn't compile. I have the error: PLS-00103: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

可能吗？

答

我们不能以任何形式的PL / SQL（包括触发器）本地执行DDL。为此，我们需要使用动态SQL。

We cannot natively execute DDL in any form of PL/SQL. including triggers. To do that we need to use dynamic SQL.

触发器有一个额外的折痕：它们作为事务的一部分被触发，并且有一个限制，即禁止我们在其内部进行提交。在Oracle中，任何DDL命令都会发出两次提交，一次提交之前，一次提交。因此，要在触发器中执行DDL，必须使用 autonomous_transaction编译指示，这意味着DDL在单独的嵌套事务中运行。

Triggers have an additional wrinkle: they are fired as part of the transaction, and they have a limitation that forbids us from issuing a commit inside their body. In Oracle, any DDL command issues two commits, one before and one after the DDL statement is executed. So, to execute DDL in a trigger we must use the autonomous_transaction pragma, which means the DDL runs in a separate, nested transaction.

create or replace TRIGGER TestTrigger
    BEFORE INSERT ON TestTable
    REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW 
declare
    pragma autonomous_transaction;
BEGIN 
    execute immediate 'create role '|| :New.RoleName;
END;

自主事务是那些容易让我们滥用和破坏我们自己的应用程序的结构之一。在您的方案中，缺点是CREATE ROLE可以成功完成其事务泡沫，而INSERTT进入 TestTable 失败；这就是自主交易的含义。因此，仍然不能保证表和oracle角色之间的一致性。

Autonomous transaction are one of those constructs which are easy for us to misuse and sabotage our own applications. In your scenario the snag is that the CREATE ROLE can succeed in its transaction bubble whilst the INSERTT into TestTable fails; such is the meaning of "autonomous transaction". So you are still not guaranteed "coherence between [your] table and oracle roles one".

更好的解决方案是将两个语句都包装到一个过程调用中，而不是

A better solution would be to wrap both statements into a procedural call, rather than attempting to trick DML into doing something it is not supposed to do.

create or replace procedure create_role
     ( p_role_name in user_roles.role%type
       , p_desc in testtable.description%type )
is
    pragma autonomous_transaction;
begin 
    insert into testtable
        ( id, creationdate, rolename, description) 
    values
        ( some_seq.nextval, sysdate, p_role_name, p_desc );
    execute immediate 'create role '|| p_role_name;
end;

Oracle：是否可以在触发器中创建角色？

相关推荐