可以在触发器内动态创建 JOB 吗？

发布于 2024-12-19 22:45:25 字数 1085 浏览 5 评论 0原文

该触发器的执行失败（它可以编译，但一旦我执行了指定的插入 -> 错误）

create or replace
TRIGGER AFT_INSERT_TMP_TBL
AFTER INSERT ON TMP_TBL
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE 

    V_SQL VARCHAR2(1000);
    A_NAME VARCHAR2(100);

BEGIN
    A_NAME:='ANY_NAME';

    V_SQL:='BEGIN 
              DBMS_SCHEDULER.CREATE_JOB (
                  job_name => '''||A_NAME||''',
                  job_type => ''PLSQL_BLOCK'',
                  job_action => ''BEGIN DBMS_OUTPUT.PUT_LINE('||A_NAME||'); END;'',
                  start_date => TIMESTAMP''2011-12-4 10:30:00'',
                  repeat_interval => ''FREQ=MINUTELY;INTERVAL=2'',
                  auto_drop => FALSE,
                  comments => '''||A_NAME||''');
            END;';
     DBMS_OUTPUT.PUT_LINE('SCHEDULER :'||V_SQL);
     EXECUTE IMMEDIATE V_SQL;

END AFT_INSERT_TMP_TBL;
-----------------------

打印的 SCHEDULER 创建代码完全有效。

我收到 ORA-04092 '无法在触发器中...触发器尝试提交或回滚。重写触发器，使其不会提交或回滚”。

这是“承诺”吗？那么不能在触发器内创建 JOB 吗？

我知道我已经使用触发器插入到不同的表中，这也是一个“提交” 甲骨文并没有抱怨。

原文

The execution of this trigger fails (it compiles but once I do the specified insert -> error)

create or replace
TRIGGER AFT_INSERT_TMP_TBL
AFTER INSERT ON TMP_TBL
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE 

    V_SQL VARCHAR2(1000);
    A_NAME VARCHAR2(100);

BEGIN
    A_NAME:='ANY_NAME';

    V_SQL:='BEGIN 
              DBMS_SCHEDULER.CREATE_JOB (
                  job_name => '''||A_NAME||''',
                  job_type => ''PLSQL_BLOCK'',
                  job_action => ''BEGIN DBMS_OUTPUT.PUT_LINE('||A_NAME||'); END;'',
                  start_date => TIMESTAMP''2011-12-4 10:30:00'',
                  repeat_interval => ''FREQ=MINUTELY;INTERVAL=2'',
                  auto_drop => FALSE,
                  comments => '''||A_NAME||''');
            END;';
     DBMS_OUTPUT.PUT_LINE('SCHEDULER :'||V_SQL);
     EXECUTE IMMEDIATE V_SQL;

END AFT_INSERT_TMP_TBL;
-----------------------

Printed SCHEDULER creation code is totally valid.

I am getting a ORA-04092 'cannot in a trigger... A trigger attempted to commit or rollback. Rewrite the trigger so it doesn't commit or rollback'.

Is this a 'commit'? So a JOB cannot be created inside of a trigger?

I know I've used triggers with inserts into different tables, and that is also a "commit"
and Oracle didn't complaint.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

愚人国度 2024-12-26 22:45:25

调用DBMS_SCHEDULER.CREATE_JOB 会隐式提交，因此您无法在触发器中创建DBMS_SCHEDULER 作业。这是仍然需要使用旧的 DBMS_JOB 包的情况之一，因为 DBMS_JOB.SUBMIT 不会隐式提交。

此触发器应使用 DBMS_JOB 包而不是 DBMS_SCHEDULER 创建您想要的作业。

create or replace
TRIGGER AFT_INSERT_TMP_TBL
AFTER INSERT ON TMP_TBL
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE 

    V_SQL VARCHAR2(1000);
    A_NAME VARCHAR2(100);
    l_jobno NUMBER;
BEGIN
    A_NAME:='ANY_NAME';

    dbms_job.submit( l_jobno,
                     'BEGIN dbms_output.put_line( ''' || a_name || ''' ); END;',
                     sysdate + interval '2' minute,
                     'sysdate + interval ''2'' minute' );
     DBMS_OUTPUT.PUT_LINE('Job Number:'||l_jobno);

END AFT_INSERT_TMP_TBL;

Calling DBMS_SCHEDULER.CREATE_JOB implicitly commits so you cannot create a DBMS_SCHEDULER job in a trigger. This is one of the situations that still call for using the old DBMS_JOB package since DBMS_JOB.SUBMIT does not implicitly commit.

This trigger should create the job you want using the DBMS_JOB package rather than DBMS_SCHEDULER.

create or replace
TRIGGER AFT_INSERT_TMP_TBL
AFTER INSERT ON TMP_TBL
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE 

    V_SQL VARCHAR2(1000);
    A_NAME VARCHAR2(100);
    l_jobno NUMBER;
BEGIN
    A_NAME:='ANY_NAME';

    dbms_job.submit( l_jobno,
                     'BEGIN dbms_output.put_line( ''' || a_name || ''' ); END;',
                     sysdate + interval '2' minute,
                     'sysdate + interval ''2'' minute' );
     DBMS_OUTPUT.PUT_LINE('Job Number:'||l_jobno);

END AFT_INSERT_TMP_TBL;

回复收藏 0 原文