Comment émuler un déclencheur BEFORE INSERT dans T-SQL / SQL Server pour des entités super / sous-type (Héritage)?
C'est sur Azure.
J'ai un supertype de l'entité et de plusieurs sous-type d'entités, dont le dernier doit obtenir leurs clés étrangères de la clé primaire de la super type d'entité sur chaque insertion. Dans Oracle, j'utilise un BEFORE INSERT
déclencheur pour effectuer cette opération. Comment pourrait-on faire cela de du Serveur SQL /T-SQL?
DDL
CREATE TABLE super (
super_id int IDENTITY(1,1)
,subtype_discriminator char(4) CHECK (subtype_discriminator IN ('SUB1', 'SUB2')
,CONSTRAINT super_id_pk PRIMARY KEY (super_id)
);
CREATE TABLE sub1 (
sub_id int IDENTITY(1,1)
,super_id int NOT NULL
,CONSTRAINT sub_id_pk PRIMARY KEY (sub_id)
,CONSTRAINT sub_super_id_fk FOREIGN KEY (super_id) REFERENCES super (super_id)
);
Je souhaite pour une insertion dans sub1
à feu un déclencheur qui fait insère une valeur dans super
et utilise le super_id
généré à mettre en sub1
.
Dans Oracle, ce serait accomplie par le suivant:
CREATE TRIGGER sub_trg
BEFORE INSERT ON sub1
FOR EACH ROW
DECLARE
v_super_id int; //Ignore the fact that I could have used super_id_seq.CURRVAL
BEGIN
INSERT INTO super (super_id, subtype_discriminator)
VALUES (super_id_seq.NEXTVAL, 'SUB1')
RETURNING super_id INTO v_super_id;
:NEW.super_id := v_super_id;
END;
Veuillez vous informer sur la façon dont je voudrais simuler cela en T-SQL, étant donné que le T-SQL manque de BEFORE INSERT
capacité?
source d'informationauteur Matthew Moisen
Vous devez vous connecter pour publier un commentaire.
Parfois un
BEFORE
déclencheur peut être remplacé par unAFTER
un, mais cela ne semble pas être le cas dans votre situation, de toute évidence, vous devez fournir une valeur avant de l'insérer. Donc, à cette fin, le plus proche de la fonctionnalité semble être laINSTEAD OF
déclencheur, comme @marc_s a suggéré dans son commentaire.Noter, cependant, que, comme les noms de ces deux types de déclenchement suggèrent, il y a une différence fondamentale entre un
BEFORE
déclencheur et unINSTEAD OF
. Alors que dans les deux cas, le déclencheur est exécuté au moment de l'action déterminée par la déclaration qui a appelé le déclencheur n'a pas eu lieu, dans le cas de laINSTEAD OF
déclencher l'action est jamais censé à prendre place. L'action réelle que vous devez faire doit être fait par le déclencheur lui-même. Ceci est très différent de laBEFORE
déclencher la fonctionnalité, où l'instruction est toujours en raison de l'exécuter, à moins, bien sûr, vous l'avez explicitement de rouler de nouveau.Mais il existe un autre problème à résoudre en fait. Que votre script Oracle révèle, le déclencheur vous avez besoin de convertir utilise une autre fonction non prise en charge par SQL Server, qui est celle de
FOR EACH ROW
. Il n'y a pas par ligne de déclencheurs dans SQL Server, uniquement par l'instruction. Cela signifie que vous devez toujours garder à l'esprit que les données insérées sont d'une rangée ensemblepas juste une seule ligne. Qui ajoute plus de complexité, bien que vais probablement conclure la liste des choses que vous devez prendre en compte.Donc, c'est vraiment deux choses à résoudre alors:
remplacer le
BEFORE
fonctionnalité;remplacer le
FOR EACH ROW
fonctionnalité.Ma tentative de résolution de ces ci-dessous:
C'est la façon dont les travaux ci-dessus:
Le même nombre de lignes que d'être inséré dans
sub1
est d'abord ajouté àsuper
. L'générésuper_id
les valeurs sont stockées dans une mémoire temporaire (une variable de table appelé@new_super
).Nouvellement inséré
super_id
s sont désormais insérées danssub1
.Rien de trop difficile, vraiment, mais le ci-dessus ne fonctionnera que si vous n'avez pas d'autres colonnes dans
sub1
que ceux que vous avez spécifié dans votre question. Si il y a d'autres colonnes, au-dessus de la gâchette doit être un peu plus complexe.Le problème consiste à attribuer à la nouvelle
super_id
s pour chaque ligne individuellement. Une façon de mettre en œuvre la cartographie pourrait être comme ci-dessous:Comme vous pouvez le voir, un
IDENTIY(1,1)
colonne est ajoutée à@new_user
de sorte que le insérée de façon temporairesuper_id
valeurs en outre être énumérées en commençant à partir de 1. Pour fournir la correspondance entre la nouvellesuper_id
s et les nouvelles lignes de données, laROW_NUMBER
fonction est utilisée pour énumérer lesINSERTED
lignes. Ainsi, chaque ligne de laINSERTED
jeu peut maintenant être liée à une seulesuper_id
et donc complété complète de la ligne de données à insérer danssub1
.Noter que l'ordre dans lequel les nouvelles
super_id
s sont insérés peut ne pas correspondre à l'ordre dans lequel ils sont affectés. J'ai considéré qu'un non-problème. Tous les nouveauxsuper
lignes générées sont identiques à épargner pour l'Id. Donc, tout ce dont vous avez besoin ici, c'est de prendre un nouveausuper_id
par de nouvellessub1
ligne.Si, toutefois, la logique de l'insertion dans
super
est plus complexe et, pour une raison quelconque vous avez besoin de se rappeler précisément les nouveauxsuper_id
a été généré pour laquelle de nouvellessub
ligne, vous voudrez sans doute envisager la méthode de cartographie de l'abordés dans ce Débordement de Pile question:Tout Andriy proposition va bien travailler pour les Insertions d'un petit nombre de dossiers, des analyses de tables complètes sera effectué lors de la finale de la rejoindre comme à la fois "énumérées" et "@new_super' ne sont pas indexés, entraînant de mauvaises performances pour les grands inserts.
Cela peut être résolu en spécifiant une clé primaire sur le @new_super tableau, comme suit:
Cela se traduira par l'optimiseur SQL de balayage à travers le "énumérées" sur table", mais de faire un indexée rejoindre sur @new_super pour obtenir la nouvelle clé.