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

[ignis666]

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!

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!

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"

Ce que je ne comprend pas c'est pourquoi.

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?

[Waldar]

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

[ignis666]

Je note, merci pour l'astuce.
Je la mettrais en place dès que ça fonctionnera...

[Pomalaix]

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 );

[Mohamed.Houri]

Envoyé par ignis666

Bonjours

Code :

01031. 00000 -  "insufficient privileges"

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é

[mnitu]

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.

[Pomalaix]

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

[Waldar]

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

[Pomalaix]

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...)