Listing 1 : Script de création pour la base de données source et la table Products
CREATE DATABASE Source GO USE Source GO CREATE TABLE Products ( productid INT NOT NULL PRIMARY KEY, productname VARCHAR(25) NOT NULL, package INT NOT NULL, /* ... other columns ... */ ) GO
Listing
2 : Script de création pour la table Prod_log
CREATE TABLE Prod_log ( lsn INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, log_date DATETIME NOT NULL DEFAULT GETDATE(), tran_type CHAR(1) NOT NULL CHECK(tran_type IN(‘I', ‘U', ‘D')), productid INT NOT NULL, productname VARCHAR(25) NOT NULL, package INT NOT NULL, namechg BIT NULL, packagechg BIT NULL ) GO
Listing 3 : Script de création pour les triggers INSERT et DELETE sur Products
CREATE TRIGGER trg_products_i ON Products FOR INSERT AS INSERT INTO Prod_log(tran_type, productid, productname, package) SELECT ‘I' AS tran_type, productid, productname, package FROM inserted GO CREATE TRIGGER trg_products_d ON Products FOR DELETE AS INSERT INTO Prod_log(tran_type, productid, productname, package) SELECT ‘D' AS tran_type, productid, productname, package FROM deleted GO
Listing 4 : Script de création pour le trigger UPDATE sur Products
CREATE TRIGGER trg_products_u ON Products FOR UPDATE
AS
-- Updates to productid aren't allowed.
IF UPDATE(productid)
BEGIN
RAISERROR(‘Updates to productid are not allowed.', 1à˜, 1)
ROLLBACK TRAN
END
-- Record update transactions of modified products.
INSERT INTO Prod_log
(tran_type, productid, productname, package, namechg, packagechg)
SELECT
‘U' AS tran_type,
New.productid,
New.productname,
New.package,
CASE
WHEN New.productname <> Old.productname THEN 1
ELSE à˜
END AS namechg,
CASE
WHEN New.package <> Old.package THEN 1
ELSE à˜
END AS packagechg
FROM inserted AS New JOIN deleted AS Old
ON New.productid = Old.productid
WHERE New.productname <> Old.productname
OR New.package <> Old.package
GO
Listing 5 : Script de création pour la base de données de destination
et trois versions de la table de dimensions Products
CREATE DATABASE Destination
GO
USE Destination
GO
CREATE TABLE Products_type1
(
productid_key INT NOT NULL IDENTITY PRIMARY KEY,
productid_app INT NOT NULL UNIQUE,
productname VARCHAR(25) NOT NULL,
package INT NOT NULL,
discontinued BIT NOT NULL DEFAULT à˜,
/* ... other columns ... */
)
GO
CREATE TABLE Products_type2
(
productid_key INT NOT NULL IDENTITY PRIMARY KEY,
productid_app INT NOT NULL,
productname VARCHAR(25) NOT NULL,
package INT NOT NULL,
effective_date DATETIME NOT NULL,
to_date DATETIME NULL,
discontinued BIT NOT NULL DEFAULT à˜,
/* ... other columns ... */
)
GO
CREATE TABLE Products_type3
(
productid_key INT NOT NULL IDENTITY PRIMARY KEY,
productid_app INT NOT NULL UNIQUE,
productname VARCHAR(25) NOT NULL,
package INT NOT NULL,
effective_date DATETIME NOT NULL,
package1 INT NULL,
effective_dat1 DATETIME NULL,
package2 INT NULL,
effective_dat2 DATETIME NULL,
discontinued BIT NOT NULL DEFAULT à˜,
/* ... other columns ... */
)
GO
Découvrez toutes nos actualités à travers des interviews, avis, conseils d'experts, témoignages clients, ainsi que les dernières tendances et solutions IT autour de nos 4 univers produits : Poste de travail, Affichage et Collaboration, Impression et Infrastructure.
