23
janvier
2012
Mettre à jour des tables via une vue
janvier
2012
Un article de zinzineti
On désire mettre à jour plusieurs tables via une vue.
--> La vue
CREATE VIEW V_Clients_Commandes (id_client,nom_client,tel_client,mobile_client,date_commande,etat_commande,date_livraison) AS
SELECT cli.IDClient,cli.NomClient,cli.Tel,cli.Mobilephone,com.DateCommande,com.EtatCommande,com.DateLivraison
FROM Clients cli inner join Commandes com
ON cli.IDClient = com.IDClient
--> Les tables membres de la vue
CREATE TABLE dbo.Clients(
IDClient int IDENTITY(1,1) NOT NULL PRIMARY KEY,
NomClient varchar(20) NULL,
Tel varchar(20) NULL,
Mobilephone [varchar](20) NULL
)
CREATE TABLE dbo.Commandes(
IDCommande int IDENTITY(1,1) NOT NULL PRIMARY KEY,
IDClient int NOT NULL,
EtatCommande varchar(20) NULL,
DateCommande datetime NULL,
DateLivraison datetime NULL,
CONSTRAINT FK_IDCLI FOREIGN KEY (IDClient) REFERENCES Clients(IDClient)
)
--> essaie de MAJ de la vue
UPDATE V_Clients_Commandes
SET tel_client = '0320XXXXXX',
etat_commande ='Livrée',
date_livraison = GETDATE()
WHERE nom_client = 'ZINZINDOHOUE'
CREATE VIEW V_Clients_Commandes (id_client,nom_client,tel_client,mobile_client,date_commande,etat_commande,date_livraison) AS
SELECT cli.IDClient,cli.NomClient,cli.Tel,cli.Mobilephone,com.DateCommande,com.EtatCommande,com.DateLivraison
FROM Clients cli inner join Commandes com
ON cli.IDClient = com.IDClient
--> Les tables membres de la vue
CREATE TABLE dbo.Clients(
IDClient int IDENTITY(1,1) NOT NULL PRIMARY KEY,
NomClient varchar(20) NULL,
Tel varchar(20) NULL,
Mobilephone [varchar](20) NULL
)
CREATE TABLE dbo.Commandes(
IDCommande int IDENTITY(1,1) NOT NULL PRIMARY KEY,
IDClient int NOT NULL,
EtatCommande varchar(20) NULL,
DateCommande datetime NULL,
DateLivraison datetime NULL,
CONSTRAINT FK_IDCLI FOREIGN KEY (IDClient) REFERENCES Clients(IDClient)
)
--> essaie de MAJ de la vue
UPDATE V_Clients_Commandes
SET tel_client = '0320XXXXXX',
etat_commande ='Livrée',
date_livraison = GETDATE()
WHERE nom_client = 'ZINZINDOHOUE'
–> échec du UPDATE.
/*
Msg 4405, Niveau 16, État 1, Ligne 1
La vue ou la fonction ‘V_Clients_Commandes’ ne peut pas être mise à jour car la modification porte sur plusieurs tables de base.
*/
–> Solution : trigger INSTEAD OF UPDATE sur la vue
CREATE TRIGGER TR_INSTEAD_UPDATE_ON_V_Clients_Commandes ON V_Clients_Commandes INSTEAD OF UPDATE NOT FOR REPLICATION
AS
BEGIN
-- s'assurer qu'au moins une ligne est affectée
IF (@@ROWCOUNT > 0)
BEGIN
SET NOCOUNT ON;
BEGIN
-- MAJ des colonnes concernées dans la table Clients
UPDATE Clients
SET NomClient = i.nom_client,
Tel = i.tel_client,
Mobilephone = i.mobile_client
FROM inserted i
WHERE i.id_client = Clients.IDClient;
END;
-- MAJ des colonnes concernées dans la table Commandes
BEGIN
-- Update columns in the base table
UPDATE Commandes
SET EtatCommande = i.etat_commande,
DateCommande = i.date_commande,
DateLivraison = i.date_livraison
FROM inserted i
WHERE i.id_client = Commandes.IDClient
END
END
END
GO
--> MAJ des tables via la vue
UPDATE V_Clients_Commandes
SET tel_client = '0320XXXXXX',
etat_commande ='Livrée',
date_livraison = GETDATE()
WHERE nom_client = 'ZINZINDOHOUE'
--> MAJ OK : vérification
SELECT * FROM Clients
SELECT * FROM Commandes
AS
BEGIN
-- s'assurer qu'au moins une ligne est affectée
IF (@@ROWCOUNT > 0)
BEGIN
SET NOCOUNT ON;
BEGIN
-- MAJ des colonnes concernées dans la table Clients
UPDATE Clients
SET NomClient = i.nom_client,
Tel = i.tel_client,
Mobilephone = i.mobile_client
FROM inserted i
WHERE i.id_client = Clients.IDClient;
END;
-- MAJ des colonnes concernées dans la table Commandes
BEGIN
-- Update columns in the base table
UPDATE Commandes
SET EtatCommande = i.etat_commande,
DateCommande = i.date_commande,
DateLivraison = i.date_livraison
FROM inserted i
WHERE i.id_client = Commandes.IDClient
END
END
END
GO
--> MAJ des tables via la vue
UPDATE V_Clients_Commandes
SET tel_client = '0320XXXXXX',
etat_commande ='Livrée',
date_livraison = GETDATE()
WHERE nom_client = 'ZINZINDOHOUE'
--> MAJ OK : vérification
SELECT * FROM Clients
SELECT * FROM Commandes
Les déclencheurs INSTEAD OF permettent de mettre à jour une vue multitable qui ne pouvaient être modifiée directement par UPDATE
Happy query !
———————-
Etienne ZINZINDOHOUE
———————-