Création dynamique de trigger dans une procédure stockée

Version imprimable

Bonjours

J'ai la table
Code:

1 2 3 4 5 6 7 CREATE TABLE matable ( "CODE_POSTAL" CHAR(5 BYTE), "NOM" VARCHAR2(38 BYTE), "INSERT_DATE" DATE, "UPDATE_DATE" DATE )
sur la quelle je veux créer un trigger dynamiquement.

Dans un 1er temps,

Quand je fais

Code 1
Code:

1 2 3 4 5 6 7 create or replace TRIGGER UPDATE_matable_TRIGGER BEFORE UPDATE ON matable FOR EACH ROW BEGIN SELECT SYSDATE INTO :new.UPDATE_DATE FROM DUAL; END;
ça marche! :D

Quand je fais

Code 2
Code:

1 2 3 4 begin EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER UPDATE_matable_TRIGGER BEFORE UPDATE ON matable FOR EACH ROW BEGIN SELECT SYSDATE INTO :new.UPDATE_DATE FROM DUAL;END;'; end;/
ça marche! :D

Mais que je met ce code dans une procédure stocké d'un package
Code:

1 2 3 4 5 6 7 8 9 PROCEDURE create_update_trigger(tableName IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER UPDATE_' || tableName || '_TRIGGER BEFORE UPDATE ON ' || tableName || ' FOR EACH ROW BEGIN SELECT SYSDATE INTO :new.UPDATE_DATE FROM DUAL; END;'; EXCEPTION WHEN OTHERS THEN RAISE; END create_update_trigger;
et que je fais appel à cette procédure
Code:

1 2 3 4 begin UPDATER.create_update_trigger('matable'); end; /
on me réponds:

Code:

01031. 00000 - "insufficient privileges"

:cry:

Ce que je ne comprend pas c'est pourquoi.8O

Car j'arrive très bien a créer un trigger sur cette table
que ce soit avec le code 1 ou le code 2;

Mais dès que je veux passé par une procédure stocké,
ça ne marche plus, et ne je vois pas pourquoi le problème viendrais des droits
Car ce sont tous des objets d'un même schéma (MON schéma)

Quelqu'un peut-il me dire où est la faute?

Pour votre problème je ne sais pas, par contre vous pouvez remplacer :

Code:

SELECT SYSDATE INTO :new.UPDATE_DATE FROM DUAL;

Par :

Code:

:new.UPDATE_DATE := SYSDATE;

Vous économisez les changements de contexte entre les moteurs SQL et PL/SQL, et c'est nettement plus performant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
declare
    v$_date date;
    v$_time pls_integer;
    v$_loop constant pls_integer default 1e6;
 
begin
    -- Test 1
    v$_time := dbms_utility.get_time();
    for i in 1..v$_loop
    loop
      select sysdate into v$_date from dual;
    end loop;
    dbms_output.put_line('Temps 1 : ' || to_char(dbms_utility.get_time() - v$_time));
 
    -- Test 2
    v$_time := dbms_utility.get_time();
    for i in 1..v$_loop
    loop
       v$_date := sysdate;
    end loop;
    dbms_output.put_line('Temps 2 : ' || to_char(dbms_utility.get_time() - v$_time));
 
end;
 
Temps 1 : 1685
Temps 2 : 45

16/09/2011, 14h52
ignis666

Je note, merci pour l'astuce.
Je la mettrais en place dès que ça fonctionnera...
16/09/2011, 14h57
Pomalaix
Citation:

Envoyé par ignis666

Quelqu'un peut-il me dire où est la faute?

Avant même de parler de syntaxe :
- pourquoi créer un déclencheur (ou n'importe quoi d'autre) dynamiquement si ça peut être fait normalement ?
- pourquoi créer un déclencheur alors qu'un simple attribut DEFAULT sur la colonne produira le même résultat ?
- et pourquoi mettre vos noms de colonnes entre guillemets, ce qui ne vous apporte aucun avantage, mais des inconvénients ?
Code:

1 2 3 4 5 CREATE TABLE matable ( CODE_POSTAL CHAR(5 BYTE), NOM VARCHAR2(38 BYTE), INSERT_DATE DATE, UPDATE_DATE DATE DEFAULT SYSDATE );
16/09/2011, 15h55
Mohamed.Houri

Citation:

Envoyé par ignis666

Bonjours

Code:

01031. 00000 - "insufficient privileges"

:cry:

Pomalaix a résumé ce que je voulais vous dire. A quoi bon vous donner la solution à ce problème si ce n'est vous aider à persister dans la mauvaise voie.

Quelle est cette raison qui fait en sorte que vous ayez opté pour le dynamique SQL au lieu et place du SQL statique?

Pour en revenir à votre problème, il faut savoir que dans une procédure les grants attribués via un role ne sont pas valables. Par exemple si vous êtes capable de créer une table/trigger en ligne de commande via un role, vous ne pouvez pas créér cette table/trigger au sein d'une procédure jusqu'à ce que ce privilège vous soit directement attribué
16/09/2011, 16h58
mnitu

Citation:

Envoyé par Pomalaix

...
- pourquoi créer un déclencheur alors qu'un simple attribut DEFAULT sur la colonne produira le même résultat ?
...

Ce n'est pas tout à fait vrai. Si l'update fournit nul pour la colonne en question alors ça sera nul et non pas sysdate.
16/09/2011, 17h20
Pomalaix

Citation:

Envoyé par mnitu

Ce n'est pas tout à fait vrai. Si l'update fournit nul pour la colonne en question alors ça sera nul et non pas sysdate.

Vivement qu'Oracle invente les contraintes NOT NULL alors :mouarf:

J'ajouterai que l'attribut DEFAULT ne sert que pour les INSERT et en aucun cas pour les UPDATE, ce que l'auteur cherche à automatiser, mais comme il a les deux dates (insert et update), on peut parfaitement l'utiliser pour la date d'insertion.

S'il veut générer automatiquement les triggers de mise à jour j'imagine qu'il a beaucoup de tables et qu'il ne souhaite pas les faire une à une.

Au lieu de faire une procédure et de l'appeler pour chaque table, autant tout faire dans le même bloc :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
create table t1 (col int, insert_date date default sysdate, update_date date);
create table t2 (col int, insert_date date default sysdate, update_date date);
 
declare
    v$_requete    varchar2(150);
begin
    for c in
    (
    select table_name
      from user_tables
     where table_name like 'T%'
    )
    loop
        v$_requete := 'CREATE OR REPLACE TRIGGER UPDATE_' || c.table_name ||
                      '_TRIGGER BEFORE UPDATE ON ' || c.table_name ||
                      ' FOR EACH ROW BEGIN :new.update_date := SYSDATE; END;';
        EXECUTE IMMEDIATE v$_requete;
    end loop;
end;
 
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';
-- Session altered.
 
insert into t1 (col) values (1);
-- 1 row created.
 
select * from t1;
       COL INSERT_DATE           UPDATE_DATE          
---------- --------------------- ---------------------
         1 16/09/2011 17:36:53                        
 
-- Attendre quelques secondes
 
update t1 set col = 2;
-- 1 row updated.
 
select * from t1;
       COL INSERT_DATE           UPDATE_DATE          
---------- --------------------- ---------------------
         2 16/09/2011 17:36:53   16/09/2011 17:37:19

16/09/2011, 19h35
Pomalaix

Citation:

Envoyé par Waldar

... pour les UPDATE, ce que l'auteur cherche à automatiser

Gloups, faut que je trouve du destop pour les yeux, tellement j'étais convaincu qu'il s'agissait d'un INSERT ! (Je reviens vous voir après ma séance de flagellation...)