Suite

Ajout d'un déclencheur SQL pour mettre à jour le champ sur INSERT (plusieurs lignes)


J'ai une classe d'entités de point d'adresse qui a un champ addressID. J'essaie d'écrire un déclencheur qui incrémentera automatiquement ce champ lorsqu'une nouvelle adresse est ajoutée à la carte. Je ne peux pas modifier la table et je ne peux pas compter sur les utilisateurs pour remplir ce champ lorsqu'ils modifient.

Jusqu'à présent, j'ai écrit un déclencheur qui fonctionne très bien tant qu'une seule modification à la fois est apportée à la classe d'entités d'adresse.

Le déclencheur de travail compte le nombre d'enregistrements déjà dans la classe d'entités (cela inclut l'enregistrement inséré), puis met à jour le champ addressID du nouvel enregistrement inséré avec le nombre.

Cependant, s'il y a plusieurs enregistrements insérés (comme dans l'utilisateur qui crée plusieurs nouvelles adresses puis enregistre les modifications), tous ces enregistrements reçoivent le même addressID.

Donc, ce dont j'ai besoin, c'est d'un déclencheur modifié qui gère correctement les insertions de plusieurs lignes. Le problème est que je ne sais pas vraiment comment faire cela et après beaucoup de recherches et de lecture de nombreux messages concernant le même débordement de pile et d'autres forums, je n'arrive toujours pas à le comprendre!

Le déclencheur qui fonctionne pour les insertions à une seule ligne est ci-dessous… quelqu'un peut-il proposer des suggestions susceptibles de résoudre mon problème ?

CREATE TRIGGER trgNewAddressPointTestInsert ON [dbo].[AddressPointTest] FOR INSERT AS UPDATE [dbo].[AddressPointTest] SET addressID = (SELECT COUNT(*) FROM [dbo].[AddressPointTest]) WHERE OBJECTID in (SELECT OBJECTID FROM insert)

La table insérée est un ensemble de lignes temporaire qui peut contenir plus d'une ligne et cette requête DML attribue simplement une valeur à tous les enregistrements qui s'y trouvent. Peut-être que ces lignes aident à résoudre ce problème.

Déclarez @RowCount comme int Sélectionnez @RowCount=Count(*) À partir de [dbo].[AddressPointTest] Sélectionnez ObJECTID,ROW_NUMBER() sur (Order by OBJECTID) AS aID dans #T1 À partir de la mise à jour insérée #T1 Définissez [email protected] Update [dbo].[AddressPointTest] Définir addressID=#T1.aID De [dbo].[AddressPointTest] Une jointure interne #T1 Sur A.OBJECTID=#T1.OBJECTID

ces lignes créent une table temporaire (#T1) avec 2 colonnes, OBJECTID et aID. Les premières valeurs aID sont simples 1,2,… n où n est le nombre de lignes dans la table insérée. Puis mettez à jour ce champ avec la valeur appropriée et enfin mettez à jour la table principale avec les valeurs appropriées en le joignant à #T1 en utilisant OBJECTID en gardant à l'esprit que OBJECTID est unique dans cette table. Remplacez simplement cette expression de mise à jour par ce bloc de code.


Création et optimisation de vues dans SQL Server

Une vue peut être utilisée dans une requête qui met à jour des données, sous réserve de quelques restrictions. Gardez à l'esprit qu'une vue n'est pas une table et ne contient aucune donnée & la modification réelle a toujours lieu au niveau de la table. Les vues ne peuvent pas être utilisées comme mécanisme pour remplacer les contraintes, règles ou intégrité référentielle définies dans les tables de base.

Restrictions sur la mise à jour des données via des vues

Vous pouvez insérer, mettre à jour et supprimer des lignes dans une vue, sous réserve des limitations suivantes :

Si la vue contient des jointures entre plusieurs tables, vous ne pouvez insérer et mettre à jour qu'une seule table dans la vue et vous ne pouvez pas supprimer de lignes.

Vous ne pouvez pas modifier directement les données dans les vues basées sur des requêtes d'union. Vous ne pouvez pas modifier les données dans les vues qui utilisent PAR GROUPE ou alors DISTINCT déclarations.

Toutes les colonnes modifiées sont soumises aux mêmes restrictions que si les instructions étaient exécutées directement sur la table de base.

Les colonnes de texte et d'image ne peuvent pas être modifiées via les vues.

Il n'y a pas de vérification des critères de vue. Par exemple, si la vue sélectionne tous les clients qui vivent à Paris et que les données sont modifiées pour ajouter ou modifier une ligne qui n'a pas Ville = 'Paris', les données seront modifiées dans la table de base mais ne seront pas affichées dans la vue, sauf si AVEC OPTION DE CHÈQUE est utilisé lors de la définition de la vue.

Utilisation AVEC OPTION DE CONTRLE

Le AVEC OPTION DE CHÈQUE La clause force toutes les instructions de modification de données exécutées contre la vue à respecter les critères définis dans le clause de la SÉLECTIONNER déclaration définissant la vue. Les lignes ne peuvent pas être modifiées de manière à les faire disparaître de la vue. L'annonce 9.12 crée une vue montrant les clients de Paris en utilisant le AVEC OPTION DE CHÈQUE déclaration.

Listing 9.12 Créer une vue en utilisant AVEC OPTION DE CHÈQUE

L'instruction Transact-SQL suivante tentant de mettre à jour les données en déplaçant tout le monde de Paris vers Lyon échouera car Lyon ne répond pas aux critères définis dans la vue. Si vous n'aviez pas AVEC OPTION DE CHÈQUE défini, le METTRE À JOUR l'instruction réussirait et une interrogation de la vue ne renverrait aucune ligne. Voici la déclaration :

Vous avez peut-être remarqué dans Access que le fait de placer des critères sur la requête RecordSource d'un formulaire limite les enregistrements affichés dans le formulaire lorsque vous l'ouvrez, mais cela ne limite pas les enregistrements pouvant être ajoutés dans le formulaire. Utilisation d'un projet Access ou d'une base de données Access avec un formulaire lié à une vue qui contient AVEC OPTION DE CHÈQUE vous permettrait d'appliquer automatiquement les critères pour les enregistrements nouveaux ou mis à jour.

Mise à jour des vues avec des jointures

Une vue qui contient une jointure ne pourra être mise à jour que d'un côté de la jointure, à moins qu'un À LA PLACE DE le déclencheur est créé sur la vue. À LA PLACE DE les déclencheurs sont abordés dans la section suivante. Par exemple, la vue présentée dans l'extrait 9.13 est basée sur une jointure entre les tables Categories et Products.

Listing 9.13 Une vue avec une jointure

Les premières lignes de la sortie de cette vue sont illustrées à la Figure 9.10.

Graphique 9.10
Une vue sélectionnant les données des tables Catégories et Produits.

Le suivant METTRE À JOUR L'instruction fonctionnera car elle n'affecte que le côté de la table Products de la jointure :

Ce METTRE À JOUR L'instruction fonctionnera également car n'affecte que le côté de la table Catégories de la jointure :

Cependant, ce qui suit METTRE À JOUR l'instruction tentant de modifier des colonnes dans les tables Produits et Catégories ne fonctionnera pas (vous obtiendrez l'erreur "View ou la fonction 'vwCategoriesProducts' n'est pas modifiable car la DE la clause nomme plusieurs tables"):

Le problème avec les vues pouvant être mises à jour

En général, vous souhaiterez créer des vues utilisant des jointures en lecture seule. Autoriser les mises à jour dans les vues avec jointures est susceptible de dérouter les utilisateurs car il n'est pas intuitif qu'ils ne puissent pas mettre à jour différentes colonnes sur la même ligne. Après tout, cela ressemble à la même table pour eux.

Si vous souhaitez que les utilisateurs puissent utiliser des vues pour mettre à jour les données, basez la vue sur une seule table ou utilisez une procédure stockée pour effectuer la mise à jour.

Mise à jour des données à l'aide de fonctions en ligne définies par l'utilisateur

Les règles de mise à jour des fonctions en ligne définies par l'utilisateur sont les mêmes que pour les vues. Considérez ces trois déclarations :

La première instruction sélectionne les données du fnProductSelectInline() fonction, la deuxième instruction la met à jour et la troisième instruction sélectionne la nouvelle valeur. Voici la sortie pour les deux SÉLECTIONNER déclarations démontrant que la fonction a fonctionné :

Une fonction en ligne qui a un paramètre peut également être utilisée pour mettre à jour des données, le paramètre limitant la portée de la mise à jour. Voici une fonction où un paramètre est utilisé pour renvoyer une seule ligne de la table Products :

Appelez la fonction comme suit pour mettre à jour le prix du ProductID 5 à 29,95 :

Seul le seul produit avec le ID de produit de 5 verra son prix modifié.

Utilisation des déclencheurs INSTEAD OF pour mettre à jour les vues non actualisables

Une À LA PLACE DE trigger sur une vue vous permet de contourner de nombreuses restrictions sur la mise à jour des vues. Par exemple, une seule table dans une vue avec plusieurs tables jointes peut être mise à jour. Une À LA PLACE DE le déclencheur peut prendre en charge les insertions, les mises à jour et les suppressions de ces données de référence dans plusieurs tables. À LA PLACE DE les déclencheurs vous permettent également de coder une logique plus complexe que celle normalement prise en charge dans les vues et ils vous permettent de travailler avec des données d'horodatage, des colonnes calculées et des colonnes d'identité.

La vue suivante sélectionne le Nom de l'entreprise valeurs dans un SYNDICAT requête entre les tables Clients et Fournisseurs :

Normalement un SYNDICAT la requête n'est pas modifiable. Cependant, un À LA PLACE DE trigger vous permet de mettre à jour les tables impliquées car il peut exécuter du code au lieu de l'action par défaut (METTRE À JOUR). Le déclencheur utilise la table insérée, qui contient la nouvelle valeur, pour insérer des données dans la table appropriée en fonction de la Taper valeur. Il utilise également la table supprimée, qui contient l'ancienne valeur, pour trouver l'enregistrement correct dans la table de base.

Cela permet ce qui suit METTRE À JOUR pour mettre à jour la table Customers avec un nouveau nom de société :

Comme tu peux le voir, À LA PLACE DE les déclencheurs sur les vues peuvent les rendre très puissants, permettant des actions qui ne seraient normalement pas autorisées. Vous pouvez également utiliser À LA PLACE DE déclencheurs pour appeler des procédures stockées pour effectuer la modification de données demandée.


Exemples de déclencheurs SQLite

Créons une nouvelle table appelée leads pour stocker tous les leads commerciaux de l'entreprise.

1) Exemple de déclencheur SQLite AVANT INSERT

Supposons que vous souhaitiez valider l'adresse e-mail avant d'insérer un nouveau prospect dans le tableau des prospects. Dans ce cas, vous pouvez utiliser un déclencheur BEFORE INSERT.

Tout d'abord, créez un déclencheur BEFORE INSERT comme suit :

Nous avons utilisé la référence NEW pour accéder à la colonne email de la ligne en cours d'insertion.

Pour valider l'e-mail, nous avons utilisé l'opérateur LIKE pour déterminer si l'e-mail est valide ou non en fonction du modèle d'e-mail. Si l'email n'est pas valide, la fonction RAISE interrompt l'insertion et émet un message d'erreur.

Deuxièmement, insérez une ligne avec un e-mail non valide dans le tableau des prospects.

SQLite a émis une erreur : “Adresse e-mail invalide” et a interrompu l'exécution de l'insertion.

Troisièmement, insérez une ligne avec un e-mail valide.

Étant donné que l'e-mail est valide, l'instruction insert s'est exécutée avec succès.

2) Exemple de déclencheur SQLite APRÈS MISE À JOUR

Les téléphones et les e-mails des prospects sont si importants que vous ne pouvez pas vous permettre de perdre ces informations. Par exemple, quelqu'un met accidentellement à jour l'e-mail ou le téléphone avec les mauvais ou même le supprime.

Pour protéger ces précieuses données, vous utilisez un déclencheur pour enregistrer toutes les modifications apportées au téléphone et à l'e-mail.

Tout d'abord, créez une nouvelle table appelée lead_logs pour stocker les données historiques.

Deuxièmement, créez un déclencheur AFTER UPDATE pour consigner les données dans la table lead_logs chaque fois qu'il y a une mise à jour dans la colonne e-mail ou téléphone.

Vous remarquez que dans la condition de la clause WHEN spécifie que le déclencheur est invoqué uniquement lorsqu'il y a un changement dans la colonne email ou phone.

Troisièmement, mettez à jour le nom de famille de John de Doe à Smith .

Le déclencheur log_contact_after_update n'a pas été invoqué car il n'y a eu aucun changement d'e-mail ou de téléphone.

Quatrièmement, mettez à jour l'e-mail et le téléphone de John avec les nouveaux.

Si vous vérifiez la table de journal, vous verrez qu'il y a une nouvelle entrée là-bas.

Vous pouvez développer les déclencheurs AFTER INSERT et AFTER DELETE pour consigner les données dans la table lead_logs en tant qu'exercice.


RBAR dans le code frontal

Lorsque j'ai rejoint une entreprise pour la première fois, mon tout premier travail consistait à faire du travail de programmation sur des projets qui utilisaient Access comme front-end et SQL Server comme back-end. J'ai trouvé plusieurs exemples de traitement RBAR sur le front-end du code Il n'y avait presque pas de code back-end sur le serveur, à l'exception des vues et des procédures stockées qui ne faisaient que

L'équipe de programmeurs était tous des débutants, et ils pensaient avoir programmé un système qui fonctionnait correctement. Il a fait le travail qu'ils voulaient qu'il fasse correctement en temps opportun. Il a donné la sortie dont ils avaient besoin. C'était pourtant quelques années avant mon arrivée 500 000 encarts plus tard. La logique de base dans chaque fonction était quelque chose comme ceci :

Ouvrez toutes les tables où nous pouvons ou non avoir besoin de faire quoi que ce soit vers ou depuis, téléchargez toutes les colonnes et toutes les lignes, puis parcourez tout cela pour faire le travail.

Le code fonctionnait certainement lorsqu'il y avait 1000 lignes dans les tables de la base de données. Maintenant, avec près d'un million de lignes, les boucles à l'intérieur des boucles à l'intérieur des boucles - imbriquées 19 fois, ont commencé à prendre de plus en plus de temps, à la fois sur le serveur et l'application, et sur les nerfs des personnes qui l'ont utilisé. L'application était être obligé de traiter les données d'une manière particulière qui était bien pour un petit ensemble de données mais ridicule pour un grand. Ma solution à l'époque (il y a 4 à 5 ans) consistait à réduire au minimum le nombre de lignes téléchargées à partir des tables du serveur. Comme je ne voulais pas trop jouer avec cette partie critique de l'application, j'ai arrêté mon optimisation là-bas. Cette modification mineure a rendu l'application presque 10 fois plus rapide. Je pensais que ce n'était pas mal pour un junior et, comme tout le monde était content, je n'ai pas repoussé les limites.

Avance rapide de trois à quatre ans de plus et 400 000 insertions supplémentaires dans les différentes tables. Le problème de performances est aussi grave, sinon pire, qu'il ne l'était lorsque je me suis joint pour la première fois.

Un exemple simple peut vous donner une idée. Nous avons un système de ‘Bill of Work’ où quatre boutons sont présents sur un formulaire : : ‘Facturation des heures’, ‘Facturation du reste’, ‘Imprimer’ et ‘Annuler’. Le bouton ‘Annuler’ ne modifie que quelques colonnes de la table de base et supprime quelques lignes dans une poignée d'autres tables. Simple. Cependant, en raison de la façon dont le système a été conçu, il a fallu onze secondes pour exécuter ce processus sur l'une des plus grosses factures de travail, et environ six secondes pour le reste. C'était une partie de l'application que je n'avais jamais touchée du tout, mais je me souviens clairement que, lorsque je me suis inscrit pour la première fois, ce processus n'a pris pas plus d'une seconde. Je considérerais maintenant que même cela est extrêmement lent, mais personne à l'époque ne l'a vu comme un problème

Je suis revenu dans ce code la semaine dernière pour voir ce que je pouvais faire avec ce bouton. La première chose que j'ai remarquée était que six jeux d'enregistrements étaient ouverts au tout début de la fonction. Ces jeux d'enregistrements ont ensuite téléchargé quatre des plus grandes tables de la base de données. Cette partie, à elle seule, a pris environ cinq secondes.

Il y avait une logique à double sens dans cette fonction en fonction des indicateurs définis dans le projet de loi. Ainsi, selon les drapeaux, 3 et seulement 3 tables peuvent être utilisées lors de l'exécution de cette fonction. Vous n'auriez jamais besoin des 6 tables. J'ai donc déplacé les téléchargements de table vers leur instruction IF respective. Cela a réduit de quelques secondes le temps. J'ai alors commencé à analyser le code. Quatre des six tables ont été ouvertes simplement pour supprimer des lignes. Le code a parcouru toutes les lignes, une ligne à la fois, afin de vérifier si l'ID correspondait à l'ID du projet de loi de travail actuel. Quand il l'a fait, il a supprimé la ligne. Voyant cela, j'ai supprimé environ 100 lignes de code et inséré 4 lignes de mon propre code qui se lit comme ceci :

(la logique était plus complexe que ça mais vous voyez l'idée).

Après avoir supprimé le code qui a téléchargé ces 4 tables, j'ai fait un autre test et j'ai été ravi de voir que le code s'exécutait maintenant en moins de 2 secondes.
"C'est génial, c'est une augmentation de 5 fois de la vitesse et du temps. Allons-y 10 fois maintenant”.
J'ai modifié le reste du code pour que les mises à jour soient désormais effectuées en un seul lot plutôt que ligne par ligne. Cela a rasé environ 1,5 seconde du temps restant.

Cela fait, j'ai déplacé toute cette logique dans une procédure stockée sur le serveur et j'ai modifié le code frontal pour appeler cette procédure stockée. Après une vérification rapide des plans d'exécution et, par conséquent, l'ajout de deux index supplémentaires, cette procédure s'exécute désormais en moins de 0,10 seconde (cela inclut également l'audit et une nouvelle interrogation du devis pour afficher les modifications). Ma version finale du code prend environ 4 à 5 fois moins de lignes de code écrites pour faire exactement la même chose. Et aussi maintenant que le code est côté serveur, une nouvelle modification de cette procédure ne nécessitera pas de redéploiement de l'application

La conclusion est que SQL Server a été conçu pour fonctionner avec des ensembles de données. Cela signifie qu'il est plus efficace de supprimer toutes les lignes où FK = 10, que de les supprimer une par une (effacez d'abord la ligne qui a un PK = 1, puis celle où PK = 2). L'approche basée sur les ensembles sera plus rapide que le RBAR plus de 99,99 % du temps. Je ne connais que quelques exceptions où RBAR est au pire aussi rapide que basé sur des ensembles, et au mieux, dépasse l'enfer.


Hôpital pour enfants Mickey Mouse - Une étude de cas de base de données

Examinons ces triplets SQL un par un et comprenons comment ils fonctionnent.

Nous utiliserons un exemple de base de données pour démontrer chacun des trois. En gardant le thème du bébé, disons que vous êtes l'administrateur des données d'un hôpital pour enfants, "Mickey Mouse Children's Hospital", pour être précis. L'hôpital tient des registres de tous ses petits patients, y compris leur âge, leur poids, les résultats des tests sanguins - vous obtenez l'image adorable.

Habituellement, les médecins saisissent et mettent à jour ces informations dans le système de l'hôpital chaque fois qu'ils voient leurs patients, et une grande partie de l'insertion, de la mise à jour et de la suppression des enregistrements dans la base de données est effectuée par le système. Mais il y a souvent des choses qui nécessitent une réparation manuelle, et en tant qu'administrateur de base de données résident, c'est le moment de briller !

Dans l'article ci-dessous, nous utilisons trois tableaux différents pour illustrer les trois déclarations différentes.

L'exemple de tableau numéro un montre les dossiers des patients et leurs informations de base :

Tableau des patients


ID du patient Nom de famille Prénom Date de naissance Sexe Poids Hauteur Vacciné
15223 Forgeron Deniz 12/31/2018 F 21.4 29.2 Oui
15224 Agarwal Arjun 08/29/2017 M 28.1 34.2 Oui
15225 Adams Coquelicot 02/14/2015 F 34.0 39.2 N
15226 Johnson Tierra 08/15/2019 F 14.6 24.5 Oui
15227 Khouri Mohammed 03/30/2014 M 41.5 44.1 Oui
15228 Jones Ben 04/04/2011 M 70.1 52.2 Oui
15229 Kowalczyk Alexandra 08/27/2019 F 15.2 23.9 Oui

Exemple de tableau numéro deux porte les différents départements de l'hôpital et les groupes et catégories de ces départements relèvent:

Tableau des départements


ID de département Nom du département Nom de groupe Nom de catégorie
1 Cardiologie Centre cardiaque Clinique
2 Unité de soins intensifs centrale Urgence Clinique
3 Urgence Urgence Clinique
4 Communication Administration Opérationnel
5 Oncologie Médecine interne Clinique
6 Neurologie Médecine interne Clinique
7 Ressources humaines Administration Opérationnel
8 Pathologie Service Technique
9 Radiologie Service Technique
10 Pharmacie Service Technique
11 Conseil exécutif Administration Opérationnel
12 Urologie Opération Clinique
13 Hématologie Médecine interne Clinique
14 Quartier Montana quartier Opérationnel
15 Quartier de Chicago quartier Opérationnel
16 Quartier Lincoln quartier Opérationnel
17 Quartier de Yellowstone quartier Opérationnel
18 Quartier de Brooklyn quartier Opérationnel

L'exemple de tableau numéro trois enregistre les visites des patients au fil des ans :

Tableau d'admission des patients


ID du patient Dernier Admis Dernier déchargé
33 12/29/1952 01/05/1953
34 - -
35 08/01/2004 08/04/2004
36 07/28/2011 07/30/2011
37 05/27/1950 05/30/1950
38 - -
39 10/11/1970 10/20/1970

Armés de ces exemples de tables, entrons dans les détails de INSERT , UPDATE et DELETE .

INSÉRER des données à l'aide de SQL

Tout comme les bébés, de nouvelles données naissent chaque minute. Avec de nombreux systèmes avertis en place, ces données sont souvent ajoutées de manière transparente aux bases de données prévues. Dans de nombreux cas, cependant, vous constaterez que vous devez l'ajouter manuellement, et c'est là qu'intervient l'instruction INSERT dans SQL.

INSERT est votre référence pour ajouter une ou plusieurs lignes à une table. INSERT peut également aider à ajouter des lignes d'une table dans une autre table.


AU LIEU DE Trigger

"INSTEAD OF trigger" est le type spécial de trigger. Il est utilisé uniquement dans les déclencheurs DML. Il est utilisé lorsqu'un événement DML va se produire sur la vue complexe.

Prenons un exemple dans lequel une vue est constituée de 3 tables de base. Lorsqu'un événement DML est émis sur cette vue, cela deviendra invalide car les données sont extraites de 3 tables différentes. Donc, dans ce déclencheur INSTEAD OF est utilisé. Le déclencheur INSTEAD OF est utilisé pour modifier directement les tables de base au lieu de modifier la vue pour l'événement donné.

  • Étape 1 : Création de la table 'emp' et 'dept' avec les colonnes appropriées
  • Étape 2 : Remplir le tableau avec des exemples de valeurs
  • Étape 3 : Création d'une vue pour la table créée ci-dessus
  • Étape 4 : mise à jour de la vue avant le déclencheur au lieu de
  • Étape 5 : Création du déclencheur au lieu de
  • Étape 6 : Mise à jour de la vue après le déclenchement au lieu du déclencheur

Étape 1) Création des tables 'emp' et 'dept' avec les colonnes appropriées

  • Ligne de code 1-7: Création de la table 'em'.
  • Ligne de code 8-12: Création de la table 'dept'.

Étape 2) Maintenant que nous avons créé la table, nous allons remplir cette table avec des exemples de valeurs et Création de vues pour les tables ci-dessus.

  • Ligne de code 13-19: Insertion de données dans la table 'dept'.
  • Ligne de code 20-26 : Insertion de données dans la table 'emp'.

Procédure PL/SQL terminée

Étape 3) Création d'une vue pour la table créée ci-dessus.

  • Ligne de code 27-32 : Création de la vue 'guru99_emp_view'.
  • Ligne de code 33 : Interrogation de guru99_emp_view.

Vue créée

NOM DE L'EMPLOYÉDEPT_NAMELIEU
ZZZHEUREEtats-Unis
AAAAVENTESRoyaume-Uni
XXXFINANCIERJAPON

Étape 4) Mise à jour de la vue avant au lieu du déclencheur.

  • Ligne de code 34-38 : Mettez à jour l'emplacement de « XXX » en « FRANCE ». Il a déclenché l'exception car les instructions DML ne sont pas autorisées dans la vue complexe.

ORA-01779 : impossible de modifier une colonne qui correspond à une table sans clé conservée

Étape 5)Pour éviter l'erreur rencontrée lors de la mise à jour de la vue à l'étape précédente, dans cette étape, nous allons utiliser "au lieu de déclencheur".

  • Ligne de code 39 : Création du déclencheur INSTEAD OF pour l'événement 'UPDATE' sur la vue 'guru99_emp_view' au niveau ROW. Il contient l'instruction de mise à jour pour mettre à jour l'emplacement dans la table de base « dept ».
  • Ligne de code 44 : L'instruction de mise à jour utilise ':NEW' et ':OLD' pour rechercher la valeur des colonnes avant et après la mise à jour.

Étape 6) Mise à jour de la vue après le déclenchement au lieu de. Maintenant, l'erreur ne viendra pas car le "au lieu du déclencheur" gérera l'opération de mise à jour de cette vue complexe. Et lorsque le code s'est exécuté, l'emplacement de l'employé XXX sera mis à jour en "France" à partir de "Japon".

  • Ligne de code 49-53 : Mise à jour de la localisation de "XXX" vers 'FRANCE'. Il réussit car le déclencheur 'INSTEAD OF' a arrêté l'instruction de mise à jour réelle à l'affichage et effectué la mise à jour de la table de base.
  • Ligne de code 55 : Vérification de l'enregistrement mis à jour.

Procédure PL/SQL terminée avec succès

NOM DE L'EMPLOYÉDEPT_NAMELIEU
ZZZHEUREEtats-Unis
AAAAVENTESRoyaume-Uni
XXXFINANCIERLA FRANCE

Parties d'un déclencheur

Un déclencheur a trois parties de base :

Un événement ou une instruction déclencheur

La figure 22-3 représente chacune de ces parties d'un déclencheur et n'est pas censée montrer la syntaxe exacte. Les sections qui suivent expliquent chaque partie d'un déclencheur plus en détail.

Figure 22-3 Le déclencheur REORDER

L'événement ou la déclaration déclencheur

Un événement ou une instruction déclencheur est l'instruction SQL, l'événement de base de données ou l'événement utilisateur qui déclenche le déclenchement d'un déclencheur. Un événement déclencheur peut être un ou plusieurs des éléments suivants :

Une instruction INSERT , UPDATE ou DELETE sur une table (ou une vue, dans certains cas) spécifique

Une instruction CREATE , ALTER ou DROP sur n'importe quel objet de schéma

Un démarrage de base de données ou un arrêt d'instance

Un message d'erreur spécifique ou tout message d'erreur

Par exemple, dans la Figure 22-3, l'instruction de déclenchement est :

Cette instruction signifie que lorsque la colonne parts_on_hand d'une ligne de la table d'inventaire est mise à jour, déclenche le déclencheur. Lorsque l'événement déclencheur est une instruction UPDATE, vous pouvez inclure une liste de colonnes pour identifier les colonnes qui doivent être mises à jour pour déclencher le déclencheur. Vous ne pouvez pas spécifier une liste de colonnes pour les instructions INSERT et DELETE, car elles affectent des lignes entières d'informations.

Un événement déclencheur peut spécifier plusieurs instructions SQL :

Cette partie signifie que lorsqu'une instruction INSERT , UPDATE ou DELETE est émise sur la table d'inventaire, déclenche le déclencheur. Lorsque plusieurs types d'instructions SQL peuvent déclencher un déclencheur, vous pouvez utiliser des prédicats conditionnels pour détecter le type d'instruction de déclenchement. De cette façon, vous pouvez créer un seul déclencheur qui exécute un code différent en fonction du type d'instruction qui déclenche le déclencheur.

Restriction de déclenchement

Une restriction de déclencheur spécifie une expression booléenne qui doit être vraie pour que le déclencheur se déclenche. L'action du déclencheur n'est pas exécutée si la restriction du déclencheur est évaluée à faux ou inconnu. Dans l'exemple, la restriction de déclenchement est :

Par conséquent, le déclencheur ne se déclenche que si le nombre de pièces disponibles est inférieur à un montant de commande actuel.

Action de déclenchement

Une action de déclenchement est la procédure (bloc PL/SQL, programme Java ou appel C) qui contient les instructions SQL et le code à exécuter lorsque les événements suivants se produisent :

Une déclaration de déclenchement est émise.

La restriction de déclenchement est évaluée à true.

Comme les procédures stockées, une action de déclenchement peut :

Contient des instructions SQL, PL/SQL ou Java

Définir les constructions du langage PL/SQL telles que les variables, les constantes, les curseurs, les exceptions

Définir les constructions du langage Java

Si les déclencheurs sont des déclencheurs de ligne, les instructions d'une action de déclencheur ont accès aux valeurs de colonne de la ligne en cours de traitement par le déclencheur. Les noms de corrélation permettent d'accéder aux anciennes et aux nouvelles valeurs de chaque colonne.


INSERTION SQLite

SQLite INSERT est utilisé pour insérer des enregistrements dans une table spécifiée de la base de données. vous devez utiliser la clause 'INSERT'. La syntaxe de la clause INSERT est la suivante :

  • Après la clause INSERT, vous devez indiquer dans quelle table vous devez insérer les valeurs.
  • Après le nom de la table dans laquelle vous écrivez la liste des colonnes, vous souhaitez insérer les valeurs.
  • Vous pouvez ignorer le nom des colonnes et ne pas leur écrire.
  • Si vous n'écrivez pas le nom des colonnes, les valeurs seront insérées dans toutes les colonnes trouvées dans le tableau avec le même ordre, les colonnes sont définies dans le tableau.
  • Après la clause VALUES, vous devez lister les valeurs à insérer.
  • Chaque clause INSERT insère une seule ligne. Si vous souhaitez insérer plusieurs lignes, vous devez écrire plusieurs clauses INSERT, une pour chaque ligne.

Exemple d'insertion SQLite

Dans l'exemple suivant, nous allons insérer 2 lignes dans la table des étudiants, une pour chaque étudiant :

Cela devrait s'exécuter avec succès et il n'y a aucune sortie pour cela :

  • Le premier étudiant avec StudentId=11, StudentName = Ahmad, DepartmentId = 4 et DateOfBirth = 1997-10-12.
  • Le deuxième étudiant avec StudentId=12, StudentName = Aly, DepartmentId = 4 et DateOfBirth = 1996-10-12'.

Dans la première instruction, nous avons listé les noms de colonnes "StudentId, StudentName, DepartmentId, DateOfBirth"Cependant, dans la deuxième déclaration, nous ne l'avons pas fait.

Les quatre valeurs "12, 'Aly', 4, '1996-10-12'" sera inséré dans les quatre colonnes du tableau Étudiants dans le même ordre que les colonnes sont définies.

Vérifions maintenant que les deux étudiants ont été insérés dans la table Students en exécutant la requête suivante :

Ensuite, vous devriez voir les deux étudiants renvoyés de cette requête comme suit :


Commencer

Commençons à comprendre les concepts des opérations CRUD dans SQL Server

Créer

La première lettre de CRUD, « C », fait référence à CREATE aka add, insert. Dans cette opération, il est prévu d'insérer un nouvel enregistrement à l'aide de l'instruction SQL insert. SQL utilise l'instruction INSERT INTO pour créer de nouveaux enregistrements dans la table.

Créons une table simple nommée Démo pour cet exemple.

SQL Insert commence par le mot clé INSERT INTO puis spécifiez le nom de la table et les colonnes que nous voulons insérer. Les colonnes vont à l'intérieur des parenthèses, puis nous spécifions une clause VALUES.

On met le nom de la table démo après le insérer dans commander. Maintenant, fournissez les valeurs aux colonnes répertoriées id et name dans la clause VALUES.

Pour insérer plusieurs lignes, suivez la syntaxe ci-dessous

Dans l'exemple suivant, les valeurs multiples sont répertoriées entre parenthèses et chaque liste est séparée par une virgule délimiteur

Pour insérer des lignes à partir de la clause SQL Union, suivez la syntaxe ci-dessous

Dans l'exemple suivant, les valeurs multiples sont répertoriées à l'aide de l'instruction SELECT, puis ces valeurs sont combinées et transmises à la table à l'aide de l'opérateur SQL UNION ALL.

La sortie répertorie toutes les lignes insérées à partir des exemples ci-dessus.

  • Remarques:
    1. Il est obligatoire d'insérer au moins toutes les colonnes requises, mais vous n'avez pas besoin de mettre à jour une colonne si ces valeurs ne sont pas requises ou s'il existe une valeur par défaut pour cette colonne
    2. Une explication détaillée de l'insertion SQL peut être trouvée dans l'article suivant : Présentation de l'instruction SQL Insert
    3. L'instruction SQL Insert ne fonctionne que sur une seule table contrairement à select qui peut fonctionner sur plusieurs tables
    4. Une explication détaillée de la clause SQL Union est disponible dans l'article suivant : Présentation, utilisation et exemples de SQL Union

La deuxième lettre de CRUD , 'R', fait référence à l'opération SELECT (récupération de données). Le mot « read » récupère des données ou un jeu d'enregistrements à partir d'une ou de plusieurs tables répertoriées. SQL utilise la commande SELECT pour récupérer les données. Lorsqu'il s'agit d'exécuter des requêtes, vous pouvez utiliser SQL Server Management Studio ou SQL Server Data Tools ou sqlcmd, en fonction de vos préférences.

Par exemple, pour lire les données associées de la table spécifiée, reportez-vous à la syntaxe ci-dessous.

L'instruction SQL select vous permet d'interroger les tables. Il permet de récupérer des données spécifiques, une ou plusieurs lignes d'une ou plusieurs tables.

L'instruction SQL SELECT dans la grande majorité du temps contiendra des noms de colonnes de la ou des tables dont vous souhaitez obtenir des données. Une fois que vous avez des noms de colonnes, le nom de la table est requis dans la clause FROM. Maintenant, dans une liste SELECT, après chaque colonne de données, vous aurez besoin d'une virgule. Vous séparez donc chaque colonne par une virgule, sauf qu'il n'y a pas de virgule après la dernière colonne. Nous allons avoir le mot-clé SELECT, le nom de la colonne suivi d'une virgule, le nom de la colonne et le nom de la dernière colonne, pas de virgule, la clause FROM suivi du nom de la table.

Dans ce cas, celui qui renverra chaque ligne de la table d'adresses. Et il renverra uniquement les colonnes AddressID, AddressLine1, AddressLine2, City, StateProvinceID et PostalCode.

L'instruction SQL SELECT utilise un caractère générique (*) ou un astérisque pour remplir toutes les colonnes de la ou des tables. Il fournit un moyen de ne pas avoir à répertorier chaque table de colonnes. C'est en utilisant l'astérisque ou '*'.

La sortie répertorie toutes les colonnes du Adresse tableau. Le SQL suivant va me donner toutes les colonnes


Plusieurs options pour transposer les lignes en colonnes

L'une des principales fonctions d'un L'intelligence d'entreprise L'équipe est de permettre aux utilisateurs professionnels de comprendre les données créées et stockées par les systèmes de l'entreprise. La compréhension des données doit donner aux utilisateurs professionnels un aperçu des performances de l'entreprise. Une compréhension typique des données au sein d'une industrie de l'assurance pourrait se rapporter à la mesure de la nombre de réclamations reçues vs réclamations traitées avec succès. Ces données pourraient être stockées dans le système source selon la mise en page dans Tableau 1:

Tableau 1 : Exemples de données sur les réclamations de police

Clé d'enregistrementPolIDPolNumberPolTypeDate effectiveID de documentNomDocSoumis
1 2 Pol002 Couverture d'hôpital 01-oct-07 1 Doc A 0
2 2 Pol002 Couverture d'hôpital 01-oct-07 4 Doc B 0
3 2 Pol002 Couverture d'hôpital 01-oct-07 5 Doc C 1
4 2 Pol002 Couverture d'hôpital 01-oct-07 7 Doc D 1
5 2 Pol002 Couverture d'hôpital 01-oct-07 10 Doc E 1

Bien que chaque entrée de données dans Tableau 1 a un unique Clé d'enregistrement identifiant, tout se rapporte toujours à une seule réclamation de police (policy Pol002). Ainsi, une représentation correcte de ces données doit être dans une seule ligne qui contient une seule instance de politique Pol002 comme représenté sur la Tableau 2:

Tableau 2 Disposition transposée

PolNumberPolTypeDate effectiveDoc ADoc BDoc CDoc DDoc E
Pol002 Couverture d'hôpital 01-oct-07 0 0 1 1 1

L'objectif de cet article est de démontrer différentes options SQL Server T-SQL qui pourraient être utilisées pour transposer des lignes de données répétées en une seule ligne avec des colonnes répétées, comme illustré dans Tableau 2. Certaines des options T-SQL qui seront démontrées utiliseront très peu de lignes de code pour transposer avec succès Tableau 1 dans Tableau 2 mais peut ne pas être nécessairement optimal en termes d'exécution de requête. Par conséquent, le plan d'exécution et les statistiques d'E/S de chaque option T-SQL seront évalués et analysés à l'aide d'ApexSQL Plan.

Option #1 : PIVOT

L'utilisation d'une fonction T-SQL Pivot est l'une des méthodes les plus simples pour transposer des lignes en colonnes. Scénario 1 montre comment une fonction Pivot peut être utilisée.