> Tech > Listings

Listings

Tech - Par iTPro - Publié le 24 juin 2010
email

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

Téléchargez gratuitement cette ressource

HP Elite Slice G2 : optimisez la collaboration… et votre budget !

HP Elite Slice G2 : optimisez la collaboration… et votre budget !

Téléchargez cette étude Forrester et découvrez comment booster la collaboration tout en dégageant un excellent R.O.I grâce au système de vidéoconférence HP Elite Slice G2 avec Microsoft Teams !

Tech - Par iTPro - Publié le 24 juin 2010