Le déclencheur "AFTER INSERT" du serveur SQL ne voit pas la ligne qui vient d'être insérée.

Question

Considérez ce déclencheur :

ALTER TRIGGER myTrigger 
   ON someTable 
   AFTER INSERT
AS BEGIN
  DELETE FROM someTable
         WHERE ISNUMERIC(someField) = 1
END

J'ai une table, someTable, et j'essaie d'empêcher les gens d'insérer de mauvais enregistrements. Pour les besoins de cette question, un mauvais enregistrement a un champ "someField" qui est entièrement numérique.

Bien sûr, la bonne façon de faire cela n'est PAS avec un trigger, mais je ne contrôle pas le code source... juste la base de données SQL. Je ne peux donc pas vraiment empêcher l'insertion de la mauvaise ligne, mais je peux la supprimer immédiatement, ce qui est suffisant pour mes besoins.

Le déclencheur fonctionne, avec un problème... quand il se déclenche, il ne semble jamais supprimer l'enregistrement défectueux qui vient d'être inséré... il supprime tous les ANCIENS enregistrements défectueux, mais pas l'enregistrement défectueux qui vient d'être inséré. Il y a donc souvent un mauvais enregistrement qui flotte et qui n'est pas supprimé jusqu'à ce que quelqu'un d'autre vienne faire un autre INSERT.

S'agit-il d'un problème dans ma compréhension des déclencheurs ? Les lignes nouvellement insérées ne sont-elles pas encore validées pendant l'exécution du déclencheur ?

Answer 1

Les déclencheurs ne peuvent pas modifier les données modifiées ( Inserted o Deleted ), sinon vous risquez d'avoir une récursion infinie car les changements invoquent à nouveau le déclencheur. Une option serait que le déclencheur annule la transaction.

Edit : La raison en est que la norme SQL est que les lignes insérées et supprimées ne peuvent pas être modifiées par le déclencheur. La raison sous-jacente est que les modifications pourraient provoquer une récursion infinie. Dans le cas général, cette évaluation pourrait impliquer plusieurs déclencheurs dans une cascade mutuellement récursive. Faire en sorte qu'un système décide intelligemment d'autoriser ou non de telles mises à jour est une tâche difficile du point de vue informatique, essentiellement une variation sur le principe de la problème de halte.

La solution acceptée est de ne pas autoriser le déclencheur à modifier les données en cours de modification, bien qu'il puisse annuler la transaction.

create table Foo (
       FooID int
      ,SomeField varchar (10)
)
go

create trigger FooInsert
    on Foo after insert as
    begin
        delete inserted
         where isnumeric (SomeField) = 1
    end
go

Msg 286, Level 16, State 1, Procedure FooInsert, Line 5
The logical tables INSERTED and DELETED cannot be updated.

Quelque chose comme ça va annuler la transaction.

create table Foo (
       FooID int
      ,SomeField varchar (10)
)
go

create trigger FooInsert
    on Foo for insert as
    if exists (
       select 1
         from inserted 
        where isnumeric (SomeField) = 1) begin
              rollback transaction
    end
go

insert Foo values (1, '1')

Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Answer 2

Vous pouvez inverser la logique. Au lieu de supprimer une ligne invalide après qu'elle ait été insérée, écrivez un fichier de type INSTEAD OF déclencher l'insertion uniquement si vous vérifiez que la ligne est valide.

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  DECLARE @isnum TINYINT;

  SELECT @isnum = ISNUMERIC(somefield) FROM inserted;

  IF (@isnum = 1)
    INSERT INTO sometable SELECT * FROM inserted;
  ELSE
    RAISERROR('somefield must be numeric', 16, 1)
      WITH SETERROR;
END

Si votre application ne veut pas gérer les erreurs (comme c'est le cas dans l'application de Joel), ne faites pas de RAISERROR . Il suffit de rendre la gâchette silencieuse no faire une insertion qui n'est pas valide.

J'ai effectué cette opération sur SQL Server Express 2005 et cela fonctionne. Notez que INSTEAD OF déclencheurs ne pas provoquer une récursion si vous insérez dans la même table pour laquelle le déclencheur est défini.

Answer 3

Voici ma version modifiée du code de Bill :

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  INSERT INTO sometable SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 1 FROM inserted;
  INSERT INTO sometableRejects SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 0 FROM inserted;
END

Cela permet à l'insertion de toujours réussir, et tous les enregistrements erronés sont jetés dans votre sometableRejects où vous pouvez les traiter plus tard. Il est important que votre table de rejet utilise des champs nvarchar pour tout - pas d'ints, de tinyints, etc. - car s'ils sont rejetés, c'est que les données ne sont pas celles que vous attendiez.

Cela résout également le problème de l'insertion d'enregistrements multiples, qui fait échouer le déclencheur de Bill. Si vous insérez dix enregistrements simultanément (comme si vous faites un select-insert-into) et qu'un seul d'entre eux est faux, le déclencheur de Bill les aurait tous signalés comme mauvais. Cette méthode permet de gérer n'importe quel nombre d'enregistrements, bons ou mauvais.

J'ai utilisé cette astuce dans le cadre d'un projet d'entreposage de données où l'application d'insertion n'avait aucune idée de la qualité de la logique métier, et où la logique métier était intégrée dans des déclencheurs. C'est vraiment désagréable pour les performances, mais si vous ne pouvez pas laisser l'insertion échouer, cela fonctionne.

Answer 4

Je pense que vous pouvez utiliser la contrainte CHECK - c'est exactement ce pour quoi elle a été inventée.

ALTER TABLE someTable 
ADD CONSTRAINT someField_check CHECK (ISNUMERIC(someField) = 1) ;

Ma réponse précédente (également juste, mais peut-être un peu exagérée) :

Je pense que la bonne méthode consiste à utiliser le déclencheur INSTEAD OF pour empêcher l'insertion de données erronées (plutôt que de les supprimer a posteriori).

Answer 5

MISE À JOUR : DELETE à partir d'un déclencheur fonctionne à la fois sur MSSql 7 et MSSql 2008.

Je ne suis pas un gourou relationnel, ni un spécialiste des normes SQL. Cependant, contrairement à ce qui est admis, MSSQL s'accommode très bien des deux systèmes r évaluation des déclencheurs cursifs et imbriqués . Je ne connais pas d'autres SGBDR.

Les options pertinentes sont les "déclencheurs récursifs" et les "déclencheurs imbriqués". . Les déclencheurs imbriqués sont limités à 32 niveaux, et leur valeur par défaut est de 1. Les triggers récursifs sont désactivés par défaut, et il n'est pas question d'une limite - mais franchement, je ne les ai jamais activés, donc je ne sais pas ce qui se passe avec l'inévitable dépassement de pile. Je soupçonne MSSQL de simplement tuer votre spid (ou il y a une limite récursive).

Bien sûr, cela montre juste que la réponse acceptée n'a pas la bonne réponse. raison non pas que ce soit incorrect. Cependant, avant les déclencheurs INSTEAD OF, je me souviens avoir écrit des déclencheurs ON INSERT qui mettaient joyeusement à jour les lignes qui venaient d'être insérées. Tout cela fonctionnait bien, et comme prévu.

Un test rapide consistant à SUPPRIMER la ligne qui vient d'être insérée fonctionne également :

 CREATE TABLE Test ( Id int IDENTITY(1,1), Column1 varchar(10) )
 GO

 CREATE TRIGGER trTest ON Test 
 FOR INSERT 
 AS
    SET NOCOUNT ON
    DELETE FROM Test WHERE Column1 = 'ABCDEF'
 GO

 INSERT INTO Test (Column1) VALUES ('ABCDEF')
 --SCOPE_IDENTITY() should be the same, but doesn't exist in SQL 7
 PRINT @@IDENTITY --Will print 1. Run it again, and it'll print 2, 3, etc.
 GO

 SELECT * FROM Test --No rows
 GO

Vous avez quelque chose d'autre qui se passe ici.