可以在触发器内动态创建作业吗? [英] Can a JOB be created dynamically inside a trigger?
问题描述
此触发器的执行失败(它可以编译,但是一旦我执行了指定的插入->错误)
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;
-----------------------
打印的SCHEDULER创建代码完全有效.
Printed SCHEDULER creation code is totally valid.
我正在收到ORA-04092'触发器无法执行...触发器试图提交或回滚.重写触发器,使其不提交或回滚.
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.
推荐答案
调用DBMS_SCHEDULER.CREATE_JOB
会隐式提交,因此您无法在触发器中创建DBMS_SCHEDULER
作业.这是仍然需要使用旧的DBMS_JOB
包的情况之一,因为DBMS_JOB.SUBMIT
不会隐式提交.
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.
此触发器应使用DBMS_JOB
软件包而不是DBMS_SCHEDULER
创建所需的作业.
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;
这篇关于可以在触发器内动态创建作业吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!