Discussion :

[ignis666]

Bonjours

J'ai la table

Code :

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

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 :

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

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 :

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

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 :

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

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 :

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

1
2
3
4
begin
  UPDATER.create_update_trigger('matable');
end;
/

on me réponds:

Code :

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

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 :

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

SELECT SYSDATE INTO :new.UPDATE_DATE FROM DUAL;

Par :

Code :

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

:new.UPDATE_DATE := SYSDATE;

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

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
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]

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 :

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

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]

Bonjours

Code :

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

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]

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

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 :

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
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]

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