Au lieu de déclencher dans SQL Server perd SCOPE_IDENTITY?

J'ai une table où j'ai créé un INSTEAD OF déclencheur pour faire respecter certaines règles de gestion.

Le problème est que lorsque j'insère des données dans cette table, SCOPE_IDENTITY() renvoie une NULL valeur, plutôt que de la réelle identité insérée.

Insert + Champ d'application du code

INSERT INTO [dbo].[Payment]([DateFrom], [DateTo], [CustomerId], [AdminId])
VALUES ('2009-01-20', '2009-01-31', 6, 1)

SELECT SCOPE_IDENTITY()

Déclencheur:

CREATE TRIGGER [dbo].[TR_Payments_Insert]
   ON  [dbo].[Payment]
   INSTEAD OF INSERT
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    IF NOT EXISTS(SELECT 1 FROM dbo.Payment p
              INNER JOIN Inserted i ON p.CustomerId = i.CustomerId
              WHERE (i.DateFrom >= p.DateFrom AND i.DateFrom <= p.DateTo) OR (i.DateTo >= p.DateFrom AND i.DateTo <= p.DateTo)
              ) AND NOT EXISTS (SELECT 1 FROM Inserted p
              INNER JOIN Inserted i ON p.CustomerId = i.CustomerId
              WHERE  (i.DateFrom <> p.DateFrom AND i.DateTo <> p.DateTo) AND 
              ((i.DateFrom >= p.DateFrom AND i.DateFrom <= p.DateTo) OR (i.DateTo >= p.DateFrom AND i.DateTo <= p.DateTo))
              )

    BEGIN
        INSERT INTO dbo.Payment (DateFrom, DateTo, CustomerId, AdminId)
        SELECT DateFrom, DateTo, CustomerId, AdminId
        FROM Inserted
    END
    ELSE
    BEGIN
            ROLLBACK TRANSACTION
    END


END

Le code a travaillé avant la création de ce déclencheur. Je suis à l'aide de LINQ to SQL en C#. Je ne vois pas un moyen de changer de SCOPE_IDENTITY à @@IDENTITY. Comment puis-je faire ce travail?

  • Je prends cette table de travail avant que vous avez inséré la PLACE DE l'état? Avez-vous vérifié votre champ de Clé Primaire pour s'assurer qu'il a une Identité cahier des charges?
  • Peut-on voir le SQL?
  • Oui et oui, c'est en effet (vous pouvez voir le code maintenant - il ne l'insertion d'une ligne et c'est une automatique d'identité lorsqu'elle est insérée).
  • Pourquoi ne pas utiliser un AVANT déclencheur d'INSERTION qui produit une erreur si les règles ne sont pas satisfaits, plutôt que au LIEU DE?
  • parce que pour ce que j'ai pu voir de SQL Server n'a pas une telle chose - même si je dois admettre que je n'ai pas directement en essayer un, mais que, hors d'une recherche sur Google, il peut être bien sûr que j'ai trouvé non valide ou périmée ressources - est-il en fait une telle chose?
  • Nous avons utilisé ces déclencheurs dans SQL Server 2000 qui semble être le mode par défaut, déclarant simplement le déclencheur "create trigger TG_xxx sur dbo.tablename pour insérer, mettre à jour aussi ....". Nous avons logique pour des conditions de test et d'appeler "raiserror()" puis faites "rollback transaction" si la validation échoue. Après avoir dit cela, nous utilisons un SP pour générer des Id plutôt que l'identité --- cela pourrait être la raison pour laquelle, le gars qui a insisté pour nous faire de cette façon est un peu vague comme pour le raisonnement. Mais alors qu'il avait pris l'habitude avant de scope_identity() a été mis en œuvre.
  • Veuillez considérer Aaron Alton suggestion pour l'utilisation de la sortie pour obtenir le indetity valeurs.
  • araqnid, votre méthode est beaucoup plus dangereux pour l'intégrité des données que d'utiliser des champs identité. Vous pouvez créer de réels problèmes avec insertions simultanées si vous n'êtes pas très prudent.
  • Oui, l'ID de génération SP fait un peu de travail en plus exclusivement de verrouillage de la table contenant le numéro suivant. Qui elle-même provoque des problèmes de contention. Je ne suis pas fan d'elle, soit (et je n'ai pas l'écrire et ne le recommande pas).

InformationsquelleAutor kastermester | 2009-05-25
  1. 17

    Utilisation @@identity au lieu de scope_identity().

    Tout scope_identity() retourne la dernière création de l'id dans le champ d'application actuel, @@identity retourne la dernière création de l'id de la session en cours.

    La scope_identity() fonction est normalement recommandé au cours de la @@identity champ, comme vous ne voulez pas des déclencheurs pour interférer avec l'id, mais dans ce cas, vous ne.

    • Après j'ai ajouté un peu plus de contenu à mon post, vous pouvez voir que j'ai utiliser LINQ to SQL dans mon .Net application, donc je n'ai vraiment pas beaucoup de choix ici, aussi loin que je peux voir, sauf peut-être à l'aide d'une procédure stockée pour insérer les données.
    • J'ai de sérieuses hésitations sur l'utilisation de @@identity, mais je suppose que dans ce cas étroite c'est pas un problème puisque les autres solutions ne sont pas vraiment mieux, et au moins vous savez qu'il va donner la juste valeur si le déclencheur n'a pas l'insérer dans une autre table dans l'intervalle.
    • Dans la plupart des cas, vous voulez que ce scope_identity() retourne, mais pas dans ce cas. Cette fois, c'est @@identity qui est le bon choix. La raison que les deux existent, c'est que parfois vous voulez vraiment le résultat inhabituel.
    • Guffa, vous ne souhaitez @@identity parce que scope_identity() n'est pas en exécution de la fonction, on pourrait s'attendre à: vous insérez une table qui a une colonne d'identité, et que vous souhaitez QUE l'identité de la colonne de la valeur de retour. Scope_Identity() est censé vous protéger d'avoir à les rechercher et de les étudier à fond tous les déclencheurs sur la table pour être sûr qu'ils ne sont pas de l'insérer dans une autre table avec une colonne d'identité. Mais la restriction de la recommandation que vous faites pour utiliser @@identity briser le moment il y a un autre après-trigger sur la table, ou plutôt de déclencher insère dans une seconde table.
    • Voir la réponse que j'ai fourni pour en savoir plus sur cette.
    • Je me rends compte que vous ne pouvez pas supposer que, lors de l'insertion d'une table avec un déclencheur INSTEAD OF sur elle, que les lignes qui vont même être ajouté à la table. Ils pourraient être mis dans une autre table, ou de nombreuses autres tables, ou pas inséré n'importe où. Cependant, le script d'appel ne savent pas cela et ne devrait pas besoin de le savoir. Le point d'un déclencheur INSTEAD OF est de faire le sale entrailles de données sous-jacentes opération transparente pour le client qui est en train de faire l'insertion. À mon avis, il devrait y avoir un moyen de définir explicitement scope_identity dans un déclencheur INSTEAD OF.

    InformationsquelleAutor Guffa
  2. 12

    Puisque vous êtes sur SQL 2008, je vous recommande fortement d'utiliser la SORTIE de la clause de la place de l'un de la coutume fonctions d'identité. SCOPE_IDENTITY a actuellement quelques problèmes avec les requêtes en parallèle qui me déconseille totalement. @@Identity n'est pas, mais il n'est toujours pas aussi explicite, et la souplesse de la SORTIE. Plus SORTIE gère de multiples insertions de lignes. Jetez un oeil à le BOL de l'article qui a quelques bons exemples.

    • quelles sont les questions avec les requêtes en parallèle? ils sont dans un autre champ, donc je ne vois pas où ils pourraient être un problème. ident_current a des problèmes avec des inserts et @@identity a des problèmes avec inserts à partir de déclencheurs qui est pourquoi scope_identity était le preferrered méthode avant que la sortie a été inventé. Toutefois, les conseils pour utiliser la Sortie est très bonne.
    • Désolé, je devrais avoir posté le lien connexion: connect.microsoft.com/SQLServer/feedback/...
    • Le point sur les requêtes parallèles est bon. Mais @@identity poignées multi-ligne insère parfaitement: INSERT TheTable ... | SELECT @LastID = Scope_Identity(), @Rows = @@RowCount | SELECT FROM TheTable WHERE ID BETWEEN @LastID - @Rows + 1 AND @LastID
    • Aussi, la SORTIE de la clause renvoie toujours 0 pour les colonnes d'identité si un déclencheur INSTEAD OF INSERT existe sur la table.
    • Pour quelqu'un qui n'a jamais entendu parler de ou de SORTIE de la clause, voici un thread qui explique comment l'utiliser: stackoverflow.com/questions/10999396/...
    InformationsquelleAutor Aaron Alton
  3. 8

    J'ai eu de sérieuses réserves au sujet de l'utilisation de @@identity, car il peut renvoyer la mauvaise réponse.

    Mais il y a une solution pour forcer @@identity pour avoir le scope_identity() valeur.

    Juste pour être complet, je vais d'abord la liste d'un couple d'autres solutions de contournement pour ce problème, je l'ai vu sur le web:

    1. Faire la gâchette renvoyer un ensemble de lignes. Puis, dans un emballage SP qui effectue l'insertion, ne INSERT Table1 EXEC sp_ExecuteSQL ... encore à une autre table. Puis scope_identity() fonctionne. Cela est ennuyeux, car il nécessite SQL dynamique qui est une douleur. Sachez également que le SQL dynamique s'exécute sous les autorisations de l'utilisateur appelant le SP plutôt que les autorisations du propriétaire de la SP. Si le client peut insérer à la table, il doit encore avoir cette autorisation, il suffit de savoir que vous pourriez rencontrer des problèmes si vous refuser l'autorisation d'insérer directement à la table.

    2. Si il y a un autre candidat à la clé, obtenir l'identité de la ligne insérée(s) à l'aide de ces touches. Par exemple, si le Nom a un index unique sur elle, alors vous pouvez insérer, puis sélectionnez le (max pour plusieurs lignes) ID de la table que vous venez d'insérer à l'aide de Nom. Tandis que ceci peut avoir des problèmes de concurrence d'accès si une autre session supprime la ligne que vous venez d'insérer, c'est pas pire que dans la situation d'origine si quelqu'un a supprimé de votre ligne avant l'application pourrait utiliser.

    Maintenant, voici comment faire pour définitivement faire de votre détente sans danger pour @@Identity pour renvoyer la valeur correcte, même si votre SP ou un autre déclencheur insère dans une identité de roulement de la table après le principal insérer.

    Aussi, merci de mettre des commentaires dans votre code sur ce que vous faites et pourquoi, afin que les futurs visiteurs à la détente n'est pas de casser des choses ou de perdre du temps à essayer de le comprendre.

    CREATE TRIGGER TR_MyTable_I ON MyTable INSTEAD OF INSERT
AS
SET NOCOUNT ON

DECLARE @MyTableID int
INSERT MyTable (Name, SystemUser)
SELECT I.Name, System_User
FROM Inserted

SET @MyTableID = Scope_Identity()

INSERT AuditTable (SystemUser, Notes)
SELECT SystemUser, 'Added Name ' + I.Name
FROM Inserted

-- The following statement MUST be last in this trigger. It resets @@Identity
-- to be the same as the earlier Scope_Identity() value.
SELECT MyTableID INTO #Trash FROM MyTable WHERE MyTableID = @MyTableID

    Normalement, l'insertion supplémentaire à la table d'audit serait tout casser, parce que depuis qu'il a une colonne d'identité, puis @@Identity permet de retourner la valeur plutôt que sur celui de l'insertion de MyTable. Cependant, la dernière sélection crée une nouvelle valeur de @@Identity qui est correcte, basée sur le Scope_Identity() que nous avons sauvé plus tôt. Cela a également des preuves contre d'éventuelles supplémentaires APRÈS le déclenchement sur la table MyTable.

    Mise à jour:

    Je viens de remarquer qu'un déclencheur INSTEAD OF n'est pas nécessaire ici. C'est tout ce que vous cherchez:

    CREATE TRIGGER dbo.TR_Payments_Insert ON dbo.Payment FOR INSERT
AS 
SET NOCOUNT ON;
IF EXISTS (
   SELECT *
   FROM
      Inserted I
      INNER JOIN dbo.Payment P ON I.CustomerID = P.CustomerID
   WHERE
      I.DateFrom < P.DateTo
      AND P.DateFrom < I.DateTo
) ROLLBACK TRAN;

    Bien sûr, cela permet scope_identity() pour continuer à travailler. Le seul inconvénient est qu'un restaurée insert sur une table d'identité ne consomment de l'identité, les valeurs utilisées (l'identité de la valeur est incrémentée par le nombre de lignes dans l'insert tentative).

    J'ai été regarder cela pendant quelques minutes et de ne pas avoir de certitude absolue en ce moment, mais je pense cela préserve le sens d'une inclusif, l'heure de début et exclusif de fin des temps. Si l'heure de fin a été compris (ce qui serait bizarre pour moi), puis les comparaisons auraient besoin d'utiliser <= au lieu de <.

    • c'est très créatif
    • C'est vraiment génial - bien que je n'ai plus besoin d'une solution pour cela - pour les autres et, éventuellement, de moi-même dans le futur, c'est juste une pure merveilles 🙂
    • Merci, les gars! J'ai compris cela il y a quelques années après une agonie de plus de problèmes dans un Projet de Données Access (ADP), qui utilise @@Identity au lieu de Scope_Identity. Je voulais désespérément d'utiliser un déclencheur INSTEAD OF UPDATE donc j'ai pu lier des formulaires de vue et de faire la mise à jour. J'ai finalement obtenu de travail! (Pour une information complète, veuillez noter que cela nécessite AVEC VIEW_METADATA pour empêcher l'Accès à partir de l'interrogation de la table sous-jacente elle-même.)
    • C'est possible, il pourrait y avoir une amélioration des performances par pas de frapper la table d'origine, de nouveau, à la fin, à travers la création d' #Corbeille, puis de réglage identity_insert et faire l'insert avec @MyTableID.
    InformationsquelleAutor ErikE
  4. 2

    Problème principal : Déclencheur et Entity framework travaillent tous les deux dans diffrent portée.
    Le problème, c'est que si vous générez de nouveaux PK valeur de déclenchement, il est de portée différente. Donc cette commande ne retourne aucune ligne, et EF lève une exception.

    La solution est d'ajouter l'instruction SELECT suivante à la fin de votre Déclencheur:

    SELECT * FROM deleted UNION ALL
SELECT * FROM inserted;

    à la place de * vous pouvez parler de tous le nom de la colonne, y compris 

    SELECT IDENT_CURRENT(‘tablename’) AS <IdentityColumnname>
    InformationsquelleAutor Ashish Mishra
  5. 1

    Comme araqnid commenté, le déclencheur semble annuler la transaction lorsqu'une condition est remplie. Vous pouvez le faire plus facile avec un APRÈS avoir VIS de déclenchement:

    CREATE TRIGGER [dbo].[TR_Payments_Insert]
   ON  [dbo].[Payment]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    IF <Condition>
    BEGIN
        ROLLBACK TRANSACTION
    END
END

    Ensuite, vous pouvez utiliser SCOPE_IDENTITY() de nouveau, car l'INSERTION ne se fait plus dans le déclencheur.

    L'état lui-même semble laisser deux lignes identiques passé, si ils sont dans la même insertion. Avec l'APRÈS déclencheur d'INSERTION, vous pouvez réécrire la condition comme:

    IF EXISTS(
    SELECT *
    FROM dbo.Payment a
    LEFT JOIN dbo.Payment b
        ON a.Id <> b.Id
        AND a.CustomerId = b.CustomerId
        AND (a.DateFrom BETWEEN b.DateFrom AND b.DateTo
        OR a.DateTo BETWEEN b.DateFrom AND b.DateTo)
    WHERE b.Id is NOT NULL)

    Et il va attraper des lignes en double, parce que maintenant on peut les différencier selon l'Id. Il fonctionne également si vous supprimez une ligne et de la remplacer par une autre ligne dans la même instruction.

    De toute façon, si vous voulez mon avis, éloignez-vous de les déclencheurs tout à fait. Comme vous pouvez le voir, même pour cet exemple, ils sont très complexes. Faire de l'insertion par le biais d'une procédure stockée. Ils sont plus simples et plus rapides que les déclencheurs:

    create procedure dbo.InsertPayment
    @DateFrom datetime, @DateTo datetime, @CustomerId int, @AdminId int
as
BEGIN TRANSACTION

IF NOT EXISTS (
    SELECT *
    FROM dbo.Payment
    WHERE CustomerId = @CustomerId
    AND (@DateFrom BETWEEN DateFrom AND DateTo
    OR @DateTo BETWEEN DateFrom AND DateTo))
    BEGIN

    INSERT into dbo.Payment 
    (DateFrom, DateTo, CustomerId, AdminId)
    VALUES (@DateFrom, @DateTo, @CustomerId, @AdminId)

    END
COMMIT TRANSACTION
    • Merci pour les exemples et les conseils, j'ai pris un coup d'oeil, et je vais probablement l'utiliser certaines de vos idées - mais pas tous d'entre eux sont applicables dans ce cas. Aussi, oui, je ne vérifie pas sur la colonne Id, je laisse la DB poignée pour moi (avec un couple de d'autres contraintes) cependant, je vais probablement passer mon trigger after insert, j'ai complètement omis de voir la solution logique lorsque j'ai créé mon déclencheur 🙂
    • l'aide ENTRE signifie que le serveur doit vérifier quatre conditions. Vous pouvez vous en tirer avec la moitié de ce nombre. Voir stackoverflow.com/questions/325933/... pour plus de détails.
    • Aussi, pourrions-nous obtenir des références pour combien de procédures stockées sont à la fois plus SIMPLE et plus RAPIDE que les éléments déclencheurs? Un déclencheur est souvent plus simple car il ne peut absolument faire appliquer les règles d'intégrité de données, mais juste d'avoir une procédure ne garantit pas qu'il obtient toujours utilisée (ne me donnez pas que l'application n'utilise que les SP, un jour, quelqu'un insère des lignes quelque part à travers le back-end). Et je suis sceptique à propos de l'échantillonnage plus rapide. Pouvez-vous le prouver?
    • Le lien que vous donnez n'assume EndA > StartA, dont je ne voudrais pas compter sur si ce n'est pas appliquée par les contraintes de vérification. Un déclencheur qui applique integrety règles devrait être remplacé par une contrainte de vérification (éventuellement avec un UDF.) Les déclencheurs sont mauvais et doivent être éliminées.
    • c'est incorrect. Aucune hypothèse n'est posée que EndA > StartA. Merci de voir silentmatt.com/intersection.html pour faciliter la visualisation de la façon dont cela fonctionne pour TOUTES les gammes. Deuxièmement, Pourriez-vous fournir une source ou certains travaux de recherche sur la façon dont les déclencheurs sont toujours mauvais, et il devrait toujours être éliminé? Je suis d'accord ils sont souvent galvaudé, mal appliquée, ou mal écrite, et si un contrôle ou une contrainte de clé étrangère peut faire le travail qu'ils sont inappropriés. Toutefois, les déclencheurs sont PLUS fiables que les SPs. Tant de fois les gens disent que "l'application n'utilise que les SP" mais un jour, quelqu'un insère des données directement.
    • Par exemple StartA = 2006, EndA = 2004, StartB = 2005, EndB = 2007 ne serait pas de match, mais il ne se chevaucher. Les travaux de recherche est trop éloigné de la réalité commerciale de fournir un aperçu significatif. Le problème des déclencheurs est pas la fiabilité mais aussi la complexité. Aucune application va réussir l'insertion de données directement dans les tables de me maintenir.
    • Toutes mes excuses. J'ai manqué les deux dans vos comparaisons. Cependant, je vois maintenant un autre problème avec votre requête: si DateFrom et DateTo sont entièrement à l'intérieur de @DateFrom et @DateTo, votre requête échoue! Comme pour @DateFrom et DateTo être dans l'ordre inverse, le corriger avec un ENSEMBLE de déclarations d'abord (donc je pense toujours que votre requête nécessite une mise à jour). Sur l'autre point, j'ai dit à deux reprises qu'il n'était pas l'application qui permettrait d'insérer directement aux tables, mais un personne. Quant à l'allégation que les travaux de recherche est incapable de fournir un aperçu significatif... euh, ok. Je vais arrêter de casting perles maintenant. 🙂
    • Juste pour être clair: Faire certaines déclarations de cession de faire @DateFrom < @DateTo et puis vous pouvez utiliser deux conditions au lieu de 4 dans votre requête, qui devrait être un énorme gain de performance, surtout avec un index sur l'une des dates (et AUSSI obtenir le cas d'une gamme entièrement enfermant les autres qui votre requête est absent pour le moment).
    • Requêtes de mise à jour sont juste une solution rapide; ils ne l'empêchera pas de nouvelles entrées. Quant à votre commentaire sur la plage entièrement à l'intérieur de la gamme, je vous conseille de lire la question avant de faire des observations sur les réponses 😉
    • Requêtes de mise à jour? Je n'ai rien dit à leur sujet. Je voulais dire les instructions SET uniquement sur les variables @DateFrom et @DateTo. Si vous voulez dire que le colonnes DateFrom et DateTo peut être dans l'ordre chronologique inverse, alors votre requête échoue également. Dernier, voulez-vous dire que, depuis le posteur d'origine n'ont pas été entièrement clos des plages, c'est le vrai besoin? Je me demande si c'est une erreur.

    InformationsquelleAutor Andomar
  6. 1

    Un peu en retard à la fête, mais j'étais à la recherche sur cette question moi-même. Une solution de contournement consiste à créer une table temporaire dans la procédure d'appel où l'insert est en cours, insérez le champ d'application de l'identité dans la table temporaire de l'intérieur de l'au lieu de déclencher, puis lire la valeur d'identité de la temp de la table une fois que l'insertion est terminée.

    Dans la procédure:

    CREATE table #temp ( id int )

... insert statement ...

select id from #temp
-- (you can add sorting and top 1 selection for extra safety)

drop table #temp

    Au lieu de déclenchement:

    -- this check covers you for any inserts that don't want an identity value returned (and therefore don't provide a temp table)
IF OBJECT_ID('tempdb..#temp') is not null
begin
    insert into #temp(id)
    values
    (SCOPE_IDENTITY())
end

    Probablement vous voulez l'appeler autre chose que #temp pour la sécurité de saké (quelque chose de long et aléatoire assez que personne d'autre ne l'utiliser: #temp1234235234563785635).

    InformationsquelleAutor Ryan