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 cette ressource
Phishing : Match PKI Versus MFA
Au-delà des technologies de protection, les entreprises doivent s’appuyer sur des plateformes qui englobent tous les défis cyber liés à l’authentification des personnes et des machines, quels sont les avantages d’une Infrastructure à Clé Publique (PKI) vis-à-vis de la MFA ?