Discussion :

[jacquesh]

hi

P'tit souci avec un trigger.

version : Oracle 8.1.7.4

soit une table A avec 2 champs C1 et C2.

A
---
C1 | C2
---------
08 | blabla
09 | blibli

je souhaiterai faire un trigger 'on update' dynamque.

une première tentative (qui ne fonctionne pas) mais qui résume bien mon besoin

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE TRIGGER TRIG_AFTUPD
AFTER UPDATE ON A
FOR EACH ROW
DECLARE
	cX VARCHAR2(2);
BEGIN
	cX := :old.c1 ;
	dbms_mview.refresh("MVIEW_".cX."_BTNN") ;
END;
/
SHOW ERRORS;

une idée ? pour réaliser correctement ce que ce code exprime mais qui ne fonctionne pas...

merci d'avance.

[pifor]

Essayer:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

dbms_mview.refresh('MVIEW_.' || cX || '._BTNN') ;

Mais on devrait pouvoir spécifier que la vue matérialisée soit mise à jour automatiquement (dans le DDL de la vue) sans avoir à utiliser un trigger.

[jacquesh]

voila un exemple de mon code (avec ta suggestion) et l'erreur retourné...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
SQL> CREATE OR REPLACE TRIGGER TRIG_BRANCARD_AFTUPD
AFTER UPDATE ON BRANCARD
FOR EACH ROW
DECLARE
        hop VARCHAR2(5);
BEGIN
        hop := "00087" ;
        dbms_mview.refresh("MV_"||hop||"_BRANCARD") ;
END;
/
SHOW ERRORS;  2    3    4    5    6    7    8    9   10
Warning: Trigger created with compilation errors.
 
SQL>
Errors for TRIGGER TRIG_BRANCARD_AFTUPD:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2      PL/SQL: Statement ignored
4/9      PLS-00201: identifier '00087' must be declared
5/2      PL/SQL: Statement ignored
5/21     PLS-00201: identifier 'MV_' must be declared
S

une idée ?

[pifor]

Avec Oracle, les chaînes de caractères doivent toujours avoir des simples quotes. Si on utilise des doubles quotes, Oracle pense qu'il s'agit d'un identificateur de variable.

[jacquesh]

oki !! merci.

j'ai un truc qui fonctionne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE OR REPLACE TRIGGER TRIG_BRANCARD_AFTUPD
AFTER UPDATE ON BRANCARD
DECLARE
	hop VARCHAR2(5) := '00087' ;
	dest VARCHAR2(55) := 'OPS$BRANCARD.MV_' || hop || '_BRANCARD' ;
BEGIN
	dbms_mview.refresh(dest) ;
END;
/
SHOW ERRORS;
No errors.

mais lors d'un update j'ai

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 671
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 654
ORA-06512: at "OPS$BRANCARD.TRIG_BRANCARD_AFTUPD", line 5
ORA-04088: error during execution of trigger 'OPS$BRANCARD.TRIG_BRANCARD_AFTUPD'

idée ?

[pifor]

cannot COMMIT IN a TRIGGER

Oracle n'accepte pas qu'un trigger exécute COMMIT. Il faut que le COMMIT soit fait en dehors du trigger => on ne peut pas appeler dbms_mview.refresh dans un trigger puisque cette procédure exécute COMMIT ...

[jacquesh]

FOUND !!

Doc ID: Note:208599.1 dans le METALINK

TIP: Click help for a detailed explanation of this page.
Bookmark Go to End

Subject: How to submit a job with DBMS_JOB.SUBMIT from a trigger
Doc ID: Note:208599.1 Type: HOWTO
Last Revision Date: 25-OCT-2005 Status: PUBLISHED



PURPOSE
-------
This article shows an example on how to submit a job from a
trigger.

SCOPE & APPLICATION
-------------------

This article applies to Oracle employees and customers alike.


HOW TO SUBMIT A JOB WITH DBMS_JOB.SUBMIT from a trigger
--------------------------------------------------------

Trying to submit a job with dbms_job.submit from a trigger may have
given you some errors.

Problems encountered:

- The jobs submit but do execute automatically
(solution: make sure you have a commit after the dbms_job.submit)

- ORA-04092: cannot COMMIT in a trigger
(solution: put the commit in a wrapper procedure and make the
trigger autonomous)

Here is a working example:

This script assumes that you have created the scott/tiger account
with utlsampl.sql.

The script submits the job with a delay of 5 minutes, so that you
have ample time to check on DBA_JOBS and see the job there. Once
executed, check emp_audit for the audit record to be written.

connect / as sysdba
alter system set job_queue_processes = 2;
connect scott/tiger
drop table Triggertab;
drop table emp_audit;
drop trigger triggertrig;
create table triggertab as select * from emp;
create table emp_audit (DT varchar(24));

Create or replace procedure run_job(ProcName Varchar2) as
Jobid Number := 0;
ProcNm Varchar2(30);
Begin
ProcNm := 'Begin '||ProcName||'; End;';
Dbms_job.Submit(Jobid,ProcNm,Sysdate+5/1440,null);
Commit;
End;
/


Create or Replace Procedure Update_audit As
Begin
Insert into emp_audit values (to_char(sysdate, 'HH:MI:SS'));
Commit;
End;
/

CREATE TRIGGER triggertrig
AFTER UPDATE OF sal ON triggertab
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
run_job ('update_audit');
END;
/

update triggertab set sal = sal+1 where rownum=1;

commit;


RELATED DOCUMENTS
-----------------

Note 65961.1 Oracle8i: Autonomous Transactions

.

[islamov2000]

excellente solution que je cherchais depuis longtemps

[skuatamad]

Pour ma part je trouve affligeant qu'un code de si mauvaise qualité puisse provenir de Metalink comme il semble que ce soit le cas...

Ci-dessous un exemple nettement plus propre de l'utilisation de dbms_job depuis un trigger :
Emailing after inserting into database tables