Conflit Trigger on Insert et Procédure

[ShaiLeTroll]

SQL AnyWhere 10
J'ai besoin d'une SEQUENCE, semble que cela n'existe pas en version 10.
J'ai donc fait une fonction qui va me trouver le Premier Numéro disponible (il y a une volonté de recycler les numéros supprimés), c'est un peu différent d'une SEQUENCE, je l'avoue !

Code la fonction

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
41
DROP FUNCTION GetCensureNumberSequenceNextValue;
CREATE FUNCTION GetCensureNumberSequenceNextValue (
  @TableName sysname,
  @SequenceFieldName sysname
)
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
  DECLARE @cpt INTEGER;
  DECLARE @found INTEGER;
  DECLARE @newid INTEGER;
  DECLARE @count INTEGER;
 
  SET @newid = 0;
  SET @found = 0;
  SET @cpt = 1;
 
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) INTO @count FROM '||@TableName; 
 
  WHILE @cpt <= @count AND @newid = 0 LOOP
 
    SET @found = 0;
    EXECUTE IMMEDIATE
      'SELECT 1 INTO @found FROM '||@TableName||' WHERE '||@SequenceFieldName||' = @cpt';
 
    IF @found = 0 THEN
      SET @newid = @cpt
    ELSE
      SET @newid = 0
    END IF;   
 
    SET @cpt = @cpt + 1;
  END LOOP;
 
  IF @newid = 0 THEN
    SET @newid = @cpt;
  END IF;
 
  RETURN @newid;
END;

je l'ai testé via

Code :

1
2
INSERT INTO Machin(NumeroBidule, NomMachin)
VALUES (GetCensureNumberSequenceNextValue ('Machin', 'NumeroBidule'), 'BOB')

C'est parfait, j'ai bien un NumeroBidule avec les valeurs que j'attendais, cela bouche les trous puis cela incrémente naturellement par la suite !
D'un point de vue performance, pas de soucis, la table restera très petite et des insertions très ponctuelles !

A savoir qu'un code similaire existe dans l'application que je maintiens mais coder directement en C++ Builder (il y a autant de fois le code dupliqué qu'il doit exister de Séquence, oui c'est vilain), je voudrais uniformiser et l'intégrer directement à la Base de données
au départ mon but était de l'intégrer à un Trigger comme ceci

Code :

1
2
3
4
5
6
7
8
9
10
DROP TRIGGER InsertMachinNumeroBidule; 
CREATE TRIGGER InsertMachinNumeroBidule 
AFTER INSERT ON Machin 
REFERENCING NEW AS NewNumeroBidule 
FOR EACH ROW 
BEGIN 
  IF NewNumeroBidule.NumeroBidule IS NULL THEN 
    SELECT GetCensureNumberSequenceNextValue('Machin', 'NumeroBidule') INTO NewNumeroBidule.NumeroBidule
  END IF; 
END;

Mais c'est la que les choses se gatent !

Erreur ! Impossible d'insérer la ligne.
COMMIT/ROLLBACK n'est pas autorisé dans l'opération atomique
[Sybase][ODBC Driver][SQL Anywhere]COMMIT/ROLLBACK n'est pas autorisé dans l'opération atomique
SQLCODE: -267
SQLSTATE: 42000

Faut-il utiliser un CURSOR et mettre le code de la fonction directement dans le TRIGGER, du coup, je n'ai plus un code uniformiser et donc retour à un code dupliqué (autant le laisser en C++)

Puis-je quand même utiliser une fonction et du SQL dynamique ?
Je n'ai pas bien compris comment récupérer le CURSOR d'un EXECUTE, j'ai trouvé un code Sybase V15 ou SQL Server 2008 qui ne fonctionne pas sous la version AnyWhere 10 !

[fadace]

Question stupide, mais pourquoi rechercher une séquence lorsque l'autincrément existe ? Quel est le gain de combler les trous ? C'est pas un peu dangereux en terme d'intégrité ?

[ShaiLeTroll]

Ne me pose pas la question, je reproduis le comportement actuel des 13 autres types d'objet qui utilise déjà ce système de numérotation recyclée !
Le Développement du produit doit avoir environ 10 ans, j'y suis depuis 8 mois, j'ajoute des fonctionnalités et je respecte les conventions existantes !
Moi je trouve cela assez moche, mais c'est vrai que pour l'utilisateur, semble que cela soit plus simple qu'il n'y ait pas de trou !

Pire, les jointures sont faites sur cette numérotation, et parfois sur le PK Auto Inc ! Il semble que mes prédécesseurs n'étaient pas des habitués de la DB ... mais plutôt de l'ASM sur 68000 ou du C sur ARM3, en ce moment ma collègue compte ses µv consommés en trop pour prolonger le temps de vie sur le mode crise sur batterie, on est loin du SQL !

Pour ma part, pour le nouveau 14eme type d'objet et le 13eme que j'ai réécrit, j'utilise uniquement la PK Auto Inc pour mes jointures, cette "séquence" n'est là qu'à titre informatif pour les utilisateurs habitués et quelques liens avec d'autres modules de l'appli qui ne savent causer qu'avec cette numérotation en séquence !

Je me suis fait une couche OR, je n'utilise que cette couche pour mes MAJ DB qui pour ces deux classes gèrent ce mode "DeviceNumberSequence" !

[Yanika_bzh]

J'avais posté une réponse il y a quelques temps sur la recherche de rupture dans une séquence, cela pourra vous aider : ici

Bon courage

[ShaiLeTroll]

hum, Yanika_bzh, j'ai tenté aussi de faire un seul SQL, je n'avais pas pensé à un troisième alias !
Joli formulation !
Cela donnerait

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
DROP FUNCTION GetCensureNumberSequenceNextValue;
CREATE FUNCTION GetCensureNumberSequenceNextValue (
  @TableName sysname,
  @SequenceFieldName sysname
)
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
  DECLARE @newid INTEGER;
 
  EXECUTE IMMEDIATE
    ' SELECT ref.'||@SequenceFieldName||' + 1 INTO @newid ' ||
    ' FROM '||@TableName||' ref ' ||
    ' INNER JOIN '||@TableName||' cmpr ON (ref.'||@SequenceFieldName||' + 1 <> cmpr.'||@SequenceFieldName||') ' ||
    ' WHERE cmpr.'||@SequenceFieldName||' = (SELECT MIN(minbase.'||@SequenceFieldName||') ' ||
    '                                       FROM '||@TableName||' minbase ' ||
    '                                       WHERE minbase.'||@SequenceFieldName||' > ref.'||@SequenceFieldName||')';
 
  IF @newid IS NULL THEN
    EXECUTE IMMEDIATE
      ' SELECT MAX('||@SequenceFieldName||') + 1 INTO @newid FROM '||@TableName;
 
    IF @newid IS NULL THEN
      SET @newid = 1;
    END IF;    
  END IF;
 
  RETURN @newid;
END;

Il est vrai que cela génère moins de requête, bon, je crois pas dépassé 200 lignes dans ma table (cela voudrait sinon dire que l'on a eu un client très riche, car chaque ligne représente un matériel physique sur un site, à 200 à 500€ l'unité sans compteur concentrateur et connectique ... )

je demanderais à mon responsable, quelle requête est lui semble préférable !