MS SQL Server
  1. MS SQL Server
  3. Comment configurer le triggersur à l'aide de la table temporaire
Intereting Posts
Comment savoir combien de connections sont actuellement ouvertes par l'application asp.net MVC3 en utilisant Entity Framework 4.1 Comment joindre des fonctions de table? IDbCommand parameters nommés pour MySQL et SQL Server Capturez la charge de travail SQL Server de la production pour jouer contre le test db Supprimer les caractères spéciaux dans SQL sans boucle? Possible de INSERT avec aucune ligne dans VALUES? Renvoie le pourcentage de la sous-catégorie dans SQL Conversion d'Oracle ou Sql Server DDL à Derby Lignes de requête SQL vers les colonnes Comment déterminer les valeurs pour les mois manquants en fonction des données des mois précédents dans T-SQL Conception de database de produits Ce script T-SQL dépasse la taille maximale autorisée. Ajustez ce paramètre dans la page devises SQL Server en sélectionnant Options dans le menu devises Requête SQL Server pour find le numéro de semaine d'un mois en considérant le jeudi comme countur de la semaine Appel de la procédure stockée Oracle avec le paramètre de sortie de SQL Server Comment simuler count distinct dans une vue indexée?

Comment configurer le triggersur à l'aide de la table temporaire

J'essaie de configurer un triggersur pour la database (j'utilise Microsoft SQL Server).

J'ai 2 tables dedans 

create table atbv_Sales_Products ( ProductID integer, TotalQuantity integer ); insert into atbv_Sales_Products values (1, 1); insert into atbv_Sales_Products values (2, 2); insert into atbv_Sales_Products values (3, 20); insert into atbv_Sales_Products values (4, 10); insert into atbv_Sales_Products values (5, 20); insert into atbv_Sales_Products values (6, 10); insert into atbv_Sales_Products values (7, 5); insert into atbv_Sales_Products values (8, 50); insert into atbv_Sales_Products values (9, 1); create table atbv_Sales_OrdersLines ( OrderID integer, ProductID integer, Amount integer ); insert into atbv_Sales_OrdersLines values (6, 4, 1); insert into atbv_Sales_OrdersLines values (6, 6, 1); insert into atbv_Sales_OrdersLines values (6, 1, 1); insert into atbv_Sales_OrdersLines values (47, 4, 1); insert into atbv_Sales_OrdersLines values (6, 9, 1); insert into atbv_Sales_OrdersLines values (5, 7, 1); insert into atbv_Sales_OrdersLines values (6, 2, 2);

Et il y a une table d'insertion (elle est réellement générée automatiquement, mais mettons-la ici pour des raisons de clarté 

 create table Inserted ( OrderID integer, ProductID integer, Amount integer ); insert into Inserted values (48, 4, 9); insert into Inserted values (48, 1, 10); insert into Inserted values (48, 8, 100); insert into Inserted values (48, 2, 1);

Pour faciliter la compréhension voici comment ces arrays ressemblent graphiquement:

Table des produits

Table des produits

Table OrdersLines

Table OrdersLines

Insérer un tableau

Insérer un tableau

Maintenant, le triggersur doit vérifier s'il est inséré Valeurs de montant + Valeurs de montant avant d'aller sur TotalQuantity (qui est une valeur statique ou en d'autres termes, ça ne changera pas quand de nouvelles commands arrivent) et si c'est le cas

Pour le filtrage que j'utilise cette partie du code 

 IF EXISTS (select p.ProductID from atbv_Sales_Products p join Inserted i on p.ProductID = i.ProductID join atbv_Sales_OrdersLines ol on p.ProductID = i.ProductID group by i.ProductID, i.Amount, p.TotalQuantity having (SUM(ol.Amount) + i.Amount) > p.TotalQuantity)

Et puis j'ai essayé d'utiliser la partie de code suivante du code pour annuler les modifications et alerter l'erreur 

 BEGIN DECLARE @ProductID NVARCHAR(60) SET @ProductID = (SELECT p.ProductID FROM atbv_Sales_Products p JOIN inserted i ON i.ProductID = p.ProductID) RAISERROR ('----There is not enough items number (%s) left----', 18, 1, @ProductName) ROLLBACK TRANSACTION RETURN END

Ce qui fonctionne bien s'il n'y a qu'une seule ligne insérée, mais je ne sais pas quoi faire s'il y a plusieurs lignes comme dans l'exemple actuel. Je pense avoir lu quelque part que je pourrais créer une table temporaire, mais encore une fois, je ne sais pas comment insert les valeurs qui sont filtrées par la première partie du code, puis utiliser ces valeurs pour afficher dans le message d'erreur.

Vous pouvez utiliser une table temporaire si vous le souhaitez, mais je pense que cela introduit une étape inutile. Ce que je ferais, c'est juste sérialiser tous les identifiants de produits défectueux dans une variable, puis vérifier si cette variable est nulle (ceci replaceait votre instruction EXISTS . 

 declare @BadProducts varchar(max) select @BadProducts = stuff((select ',' + p.ProductId from atbv_Sales_Products p join Inserted i on p.ProductID = i.ProductID join atbv_Sales_OrdersLines ol on p.ProductID = i.ProductID group by i.ProductID, i.Amount, p.TotalQuantity having (SUM(ol.Amount) + i.Amount) > p.TotalQuantity for xml path('')), 1, 1, '') if @BadProducts is not null begin raiserror('These products are bad: %s', 16, 1, @BadProducts) return end

L'alternative serait d'insert les lignes dans une table temporaire ou une variable de table (comme vous le suggérez), puis faites une vérification d'existence ou une vérification @@rowcount rapport à la table temporaire, et si l'une correspond, sérialiser comme le premier exemple (sauf replace toute la grosse sous-requête avec juste votre table temporaire). Quelque chose comme ça: 

 declare @badProductsTable table ( ProductId int ) insert into @badProductsTable (ProductId) select p.ProductId from atbv_Sales_Products p join Inserted i on p.ProductID = i.ProductID join atbv_Sales_OrdersLines ol on p.ProductID = i.ProductID group by i.ProductID, i.Amount, p.TotalQuantity having (SUM(ol.Amount) + i.Amount) > p.TotalQuantity if @@rowcount > 0 -- or you could do -- if exists (select 1 from @badProductsTable) begin select @BadProducts = stuff((select ',' + ProductId from @badProductsTable for xml path('')), 1, 1, '') raiserror('These products are bad: %s', 16, 1, @BadProducts) return end

Votre instruction If Exists est vraiment ce dont vous avez besoin. Ce script a fonctionné pour moi … 

 -- DROP table atbv_Sales_Products create table atbv_Sales_Products ( ProductID integer, TotalQuantity integer ); insert into atbv_Sales_Products values (1, 10); -- DROP table atbv_Sales_OrdersLines create table atbv_Sales_OrdersLines ( OrderID integer, ProductID integer, Amount integer ); insert into atbv_Sales_OrdersLines values (100, 1, 4); CREATE TRIGGER MyTrigger ON atbv_Sales_OrdersLines AFTER INSERT, UPDATE AS IF EXISTS ( SELECT * FROM (SELECT ProductID, SUM(Amount) as [Amount] FROM Inserted GROUP BY ProductID) I LEFT JOIN (SELECT ProductID, SUM(Amount) as [Amount] FROM atbv_Sales_OrdersLines X WHERE NOT EXISTS (SELECT * FROM Inserted Y WHERE Y.OrderID = X.OrderID) GROUP BY ProductID) OL ON OL.ProductID = I.ProductID JOIN atbv_Sales_Products P ON P.ProductID = I.ProductID WHERE I.Amount + ISNULL(OL.Amount, 0) > P.TotalQuantity ) THROW 51000, 'My Error Message.', 1; GO ------------- SELECT * FROM atbv_Sales_Products SELECT * FROM atbv_Sales_OrdersLines insert into atbv_Sales_OrdersLines values (101, 1, 4); insert into atbv_Sales_OrdersLines values (102, 1, 2); insert into atbv_Sales_OrdersLines values (103, 1, 1);

En outre, envisagez de find un moyen d'utiliser une contrainte CHECK par opposition à un triggersur. Les triggersurs peuvent souvent causer des problèmes de maintenance et des problèmes de performance, sur la route.

Enfin, cet exemple est académique, seulement. Comme les commands arrivent sur plusieurs jours et que la quantité disponible fluctue, cette solution ne fonctionnera pas très bien. Cela fonctionne pour illustrer un point, mais j'espère que c'est juste à des fins académiques.