Listings (2)

7 : Script ActiveX pour la Type2 Refresh DDQ Task

‘ Type 2 Refresh Process

Function Main()

DTSDestination(“package”) = DTSSource(“package”)

DTSDestination(“productname”) = DTSSource(“productname”)

DTSDestination(“productid_app”) = DTSSource(“productid”)

DTSDestination(“effective_date”) = DTSSource(“log_date”)

Select Case DTSSource(“tran_type”)

Case “I”

Main = DTSTransformstat_InsertQuery

Case “D”

Main = DTSTransformstat_DeleteQuery

Case “U”

If DTSSource(“packagechg”) = False Then

DTSDestination(“package”) = -1

Main = DTSTransformstat_UpdateQuery

End Select

End Function

Listing 8 : Script ActiveX pour la procédure stockée usp_Update_Products_type2

CREATE PROC usp_Update_Products_type2

@productid_app AS INT,

@productname AS VARCHAR(25),

@package AS INT,

@effective_date AS DATETIME

AS

IF @package <> -1

BEGIN

UPDATE Products_type2

SET to_date = @effective_date

WHERE productid_key = (SELECT MAX(productid_key)

FROM Products_type2

WHERE productid_app = @productid_app)

INSERT INTO Products_type2(productid_app, productname, package, effective_

date)

VALUES(@productid_app, @productname, @package, @effective_date)

END

ELSE

BEGIN

UPDATE Products_type2

SET productname = @productname

WHERE productid_app = @productid_app

END

GO

Listing 9 : Script de création pour la procédure stockée usp_Update_Products_type3

CREATE PROC usp_Update_Products_type3

@productid_app AS INT,

@productname AS VARCHAR(25),

@package AS INT,

@effective_date AS DATETIME

AS

IF @package <> -1

BEGIN

UPDATE Products_type3

SET productname = @productname,

package2 = package1,

package1 = package,

package = @package,

effective_dat2 = effective_dat1,

effective_dat1 = effective_date,

effective_date = @effective_date

WHERE productid_app = @productid_app

END

ELSE

BEGIN

UPDATE Products_type3

SET productname = @productname

WHERE productid_app = @productid_app

END

GO