Re-write Trigger to Stored procedure in SQL Server

  • Hi,

    I have a requirement where i need to remove tigger which has business logic operation and referential integrity in table structure and convert it to Stored procedure using SQl Server.

    Please help me.

    Thanks,

    Gayathri

  • are you going to provide any more information?

    The more detail you give us, the more we can offer suggestions...it looks like your post is just a venting because you have a difficult task...

    show us the actual trigger. We may be able to offer suggestions after reviewing what it does.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi,

    Thanks for your response!

    Actually I have triggers which controls the business. Trigger performs the Business logic as well as the referential integrity is controlled through it. I am in process of enforcing referential integrity with the table structure. The procedure calls the table and when it tries to insert,delete , update the trigger is invoked. I have to rewrite the trigger into stored procedure. Below is the Producedure which calls the trigger when it tries to insert it. I have provided the procedure, table strucutre and Primary index key

    Procedure Name: - PTR_PARTNERMAINTENANCE_INS_SP

    /*******************************************************

    --1. File Name Ptr_PartnerMiantenance_Ins_SP.sql

    --2. Description This SP inserts a new Partner record.

    --*****************************************************************/

    CREATE PROCEDURE [dbo].Ptr_PartnerMaintenance_Ins_SP

    @pi_sPartnerCode VARCHAR(5)

    ,@pi_sDescription VARCHAR(50)

    ,@pi_sPartnerType VARCHAR(1)

    ,@pi_sAddressLine1 VARCHAR(30)

    ,@pi_sAddressLine2 VARCHAR(30)

    ,@pi_sAddressLine3 VARCHAR(30)

    ,@pi_sCity VARCHAR(20)

    ,@pi_sState VARCHAR(2)

    ,@pi_sZipCode VARCHAR(5)

    ,@pi_sZipExtend VARCHAR(4)

    ,@pi_sCountryID VARCHAR(3)

    ,@pi_bCodeSharedInd BIT

    ,@pi_sCodeSharedPrefix VARCHAR(3)

    ,@pi_bReceivedInd BIT

    ,@pi_iMilesPerSegment INTEGER

    ,@pi_bExcludeCPInd BIT

    ,@pi_bIncludeCpInd BIT

    ,@pi_bSendInd BIT

    ,@pi_iMinMiles INTEGER

    ,@pi_iMaxMiles INTEGER

    ,@pi_bCityPairInd BIT

    ,@pi_bReferenceOverride BIT

    ,@pi_bNameOverride BIT

    ,@pi_bFinalBillingInd BIT

    ,@pi_sBillingFrequency VARCHAR(1)

    ,@pi_dtNextBillDate DATETIME

    ,@pi_sUpdateUserId VARCHAR(8)

    AS

    /************************************************

    --1. Procedure Name Ptr_PartnerMiantenance_Ins_SP

    --2. Parameters

    -- Input

    -- @pi_sPartnerCode

    -- Partner Code which needs to be Inserted.

    -- @pi_sDescription

    -- This field specifies the description of the Partner.

    -- @pi_sPartnerType

    -- This field specifies the PartnerType of the Partner.

    -- @pi_sAddressLine1

    -- This field specifies the AddressLine1 of the Partner.

    -- @pi_sAddressLine2

    -- This field specifies the AddressLine2 of the Partner.

    -- @pi_sAddressLine3

    -- This field specifies the AddressLine3 of the Partner.

    -- @pi_sCity

    -- This field specifies the City of the Partner.

    -- @pi_sState

    -- This field specifies the State of the Partner.

    -- @pi_sZipCode

    -- This field specifies the ZipCode of the Partner.

    -- @pi_sZipExtend

    -- This field specifies the ZipExtend of the Partner.

    -- @pi_sCountryID

    -- This field specifies the Country of the Partner.

    -- @pi_bCodeSharedInd

    -- This field specifies the CodeSharedInd of the Partner.

    -- @pi_sCodeSharedPrefix

    -- This field specifies the CodeSharedPrefix of the Partner.

    -- @pi_bReceivedInd

    -- This field specifies the ReceivedInd of the Partner.

    -- @pi_iMilesPerSegment

    -- This field specifies the MilesPerSegment of the Partner.

    -- @pi_bExcludeCPInd

    -- This field specifies the ExcludeCPInd of the Partner.

    -- @pi_bIncludeCPInd

    -- This field specifies the IncludeCPInd of the Partner.

    -- @pi_bSendInd

    -- This field specifies the sendInd of the Partner.

    -- @pi_iMinMiles

    -- This field specifies the MinMiles of the Partner.

    -- @pi_iMaxMiles

    -- This field specifies the MaxMiles of the Partner.

    -- @pi_bCityPairInd

    -- This field specifies the CityPairInd of the Partner.

    -- @pi_bReferenceOverride

    -- This field specifies the ReferenceOverride of the Partner.

    -- @pi_bNameOverride

    -- This field specifies the NameOverride of the Partner.

    -- @pi_bFinalBillingInd

    -- This field specifies the FinalBillingInd of the Partner.

    -- @pi_sBillingFrequency

    -- This field specifies the BillingFrequency of the Partner.

    -- @pi_dtNextBillDate

    -- This field specifies the NextBillDate of the Partner.

    -- @pi_sUpdateUserID

    -- This stores the User ID of user who is Inserting the record.

    -- Output

    -- NA

    --5. Return Value(s) - 0 if Success

    -- Error # if Failure

    --6. Module Name Partner Tables

    --7. Description This SP inserts the Partner information.

    --**************************************************************/

    --Turn off rows affected messages

    SET NOCOUNT OFF --For insert\update\del SET NOCOUNT should be OFF

    --Declare and initialise local variables here

    DECLARE @li_errorNumber INTEGER

    DECLARE @lb_contextInfo VARBINARY(128)

    SET @lb_contextInfo =CONVERT(VARBINARY(128), @pi_sUpdateUserId + 'Ptr_PartnerMiantenance_Ins_SP' )

    SET CONTEXT_INFO @lb_contextInfo

    SET @li_errorNumber = 0

    -- Set the update user id.

    CREATE TABLE #USER

    (

    USERID VARCHAR(20)

    )

    INSERT INTO #USER (USERID)

    VALUES(@pi_sUpdateUserID)

    INSERT Partner

    (PartnerCode

    ,[Description]

    ,PartnerType

    ,AddressLine1

    ,AddressLine2

    ,AddressLine3

    ,City

    ,State

    ,ZipCode

    ,ZipExtend

    ,CountryID

    ,CodeSharedInd

    ,CodeSharedPrefix

    ,ReceiveInd

    ,MilesPerSegment

    ,ExcludeCpInd

    ,IncludeCpInd

    ,SendInd

    ,MinMiles

    ,MaxMiles

    ,CityPairInd

    ,ReferenceOverride

    ,NameOverride

    ,FinalBillingInd

    ,BillingFrequency

    ,NextBillDate

    ,LastUpdateDate

    ,LastUpdatedBy

    )

    VALUES ( @pi_sPartnerCode

    ,@pi_sDescription

    ,@pi_sPartnerType

    ,@pi_sAddressLine1

    ,@pi_sAddressLine2

    ,@pi_sAddressLine3

    ,@pi_sCity

    ,@pi_sState

    ,@pi_sZipCode

    ,@pi_sZipExtend

    ,@pi_sCountryID

    ,@pi_bCodeSharedInd

    ,@pi_sCodeSharedPrefix

    ,@pi_bReceivedInd

    ,@pi_iMilesPerSegment

    ,@pi_bExcludeCPInd

    ,@pi_bIncludeCpInd

    ,@pi_bSendInd

    ,@pi_iMinMiles

    ,@pi_iMaxMiles

    ,@pi_bCityPairInd

    ,@pi_bReferenceOverride

    ,@pi_bNameOverride

    ,@pi_bFinalBillingInd

    ,@pi_sBillingFrequency

    ,@pi_dtNextBillDate

    ,GetDate()

    ,@pi_sUpdateUserID

    )

    --Get the details regarding error

    SELECT @li_errorNumber=@@ERROR

    IF OBJECT_ID('TEMPDB..#USER') IS NOT NULL

    BEGIN

    DROP TABLE #USER

    END

    -- Failed to update the record? may be FK , unique key , PK violation.

    IF @li_errorNumber0

    BEGIN

    RETURN @li_errorNumber

    END

    RETURN 0

    In the above procedure the below trigger is fired for insert, update

    /****** Object: Trigger dbo.Partner_trg Script Date: 5/30/2004 2:31:39 AM ******/

    Create Trigger Partner_trg on Partner

    for insert, update, delete as

    declare

    @isInserted tinyint

    ,@isDeleted tinyint

    ,@isUpdated tinyint

    ,@deletecount smallint

    ,@insertcount smallint

    ,@oldMilesPerSegment INT

    ,@newMilesPerSegment INT

    ,@oldCheckDigitRoutine VARCHAR(50)

    ,@newCheckDigitRoutine VARCHAR(50)

    ,@oldReferenceOverride BIT

    ,@newReferenceOverride BIT

    ,@oldCodeSharedInd BIT

    ,@newCodeSharedInd BIT

    ,@oldCountryID VARCHAR(3)

    ,@newCountryID VARCHAR(3)

    ,@oldStoredProcedure VARCHAR(50)

    ,@newStoredProcedure VARCHAR(50)

    ,@oldFinalBillingInd BIT

    ,@newFinalBillingInd BIT

    ,@oldNameOverride BIT

    ,@newNameOverride BIT

    ,@oldZipExtend VARCHAR(4)

    ,@newZipExtend VARCHAR(4)

    ,@oldCityPairInd BIT

    ,@newCityPairInd BIT

    ,@oldExcludeCPInd BIT

    ,@newExcludeCPInd BIT

    ,@oldCity VARCHAR(20)

    ,@newCity VARCHAR(20)

    ,@oldPartnerType VARCHAR(1)

    ,@newPartnerType VARCHAR(1)

    ,@oldLastUpdateDate DATETIME

    ,@newLastUpdateDate DATETIME

    ,@oldNextBillDate DATETIME

    ,@newNextBillDate DATETIME

    ,@oldReceiveInd BIT

    ,@newReceiveInd BIT

    ,@oldCodeSharedPrefix VARCHAR(3)

    ,@newCodeSharedPrefix VARCHAR(3)

    ,@oldPartnerCode VARCHAR(5)

    ,@newPartnerCode VARCHAR(5)

    ,@oldLastUpdatedBy VARCHAR(8)

    ,@newLastUpdatedBy VARCHAR(8)

    ,@oldZipCode VARCHAR(5)

    ,@newZipCode VARCHAR(5)

    ,@oldNextActivityNo INT

    ,@newNextActivityNo INT

    ,@oldSendInd BIT

    ,@newSendInd BIT

    ,@oldAddressLine1 VARCHAR(30)

    ,@newAddressLine1 VARCHAR(30)

    ,@oldBillingFrequency VARCHAR(1)

    ,@newBillingFrequency VARCHAR(1)

    ,@oldMinMiles INT

    ,@newMinMiles INT

    ,@oldState VARCHAR(2)

    ,@newState VARCHAR(2)

    ,@oldAddressLine2 VARCHAR(30)

    ,@newAddressLine2 VARCHAR(30)

    ,@oldMaxMiles INT

    ,@newMaxMiles INT

    ,@oldIncludeCPInd BIT

    ,@newIncludeCPInd BIT

    ,@oldAddressLine3 VARCHAR(30)

    ,@newAddressLine3 VARCHAR(30)

    ,@oldDescription VARCHAR(50)

    ,@newDescription VARCHAR(50)

    ,@pr25_pKeyChg tinyint

    ,@pr25_pKeyDel tinyint

    ,@pr25_replChg tinyint

    ,@pr27_pKeyChg tinyint

    ,@pr27_pKeyDel tinyint

    ,@pr27_replChg tinyint

    ,@pr64_pKeyChg tinyint

    ,@pr64_pKeyDel tinyint

    ,@pr64_replChg tinyint

    ,@pr76_pKeyChg tinyint

    ,@pr76_pKeyDel tinyint

    ,@pr76_replChg tinyint

    ,@pr65_pKeyChg tinyint

    ,@pr65_pKeyDel tinyint

    ,@pr65_replChg tinyint

    ,@pr66_pKeyChg tinyint

    ,@pr66_pKeyDel tinyint

    ,@pr66_replChg tinyint

    ,@pr67_pKeyChg tinyint

    ,@pr67_pKeyDel tinyint

    ,@pr67_replChg tinyint

    ,@pr34_pKeyChg tinyint

    ,@pr34_pKeyDel tinyint

    ,@pr34_replChg tinyint

    ,@OnDiskPartnerCode VARCHAR(5)

    ,@next_fetch_stI int

    ,@next_fetch_stD int

    ,@normal_finish int

    ,@singleRowCase smallint

    ,@tmpCounter smallint

    ,@currentInsertPosition smallint

    ,@currentDeletePosition smallint

    ,@continueWhile smallint

    ,@vsvbCounter int

    ,@CurrentEvent varchar(100)

    ,@event_level int

    begin

    -- Set the User Id

    DECLARE @pi_sUpdateUserID VARCHAR (20)

    exec Hwn_User_View_SP @pi_sUpdateUserID out

    set ansi_nulls on

    set nocount on

    select @normal_finish = 0

    select @insertcount = count(*) from inserted

    select @deletecount = count(*) from deleted

    if ( @insertcount > 0 and @deletecount > 0 )

    begin

    select @isUpdated = 1

    select @isDeleted = 0

    select @isInserted = 0

    end

    else if ( @insertcount > 0 )

    begin

    select @isUpdated = 0

    select @isDeleted = 0

    select @isInserted = 1

    end

    else if ( @deletecount > 0 )

    begin

    select @isUpdated = 0

    select @isDeleted = 1

    select @isInserted = 0

    end

    else

    begin

    goto end_of_trigger

    end

    select @singleRowCase = 0

    if ( @insertcount = 1 or @deletecount = 1 )

    begin

    select @singleRowCase = 1

    if ( @isInserted = 1 or @isUpdated = 1 ) select

    @newMilesPerSegment = MilesPerSegment

    ,@newCheckDigitRoutine = CheckDigitRoutine

    ,@newReferenceOverride = ReferenceOverride

    ,@newCodeSharedInd = CodeSharedInd

    ,@newCountryID = CountryID

    ,@newStoredProcedure = StoredProcedure

    ,@newFinalBillingInd = FinalBillingInd

    ,@newNameOverride = NameOverride

    ,@newZipExtend = ZipExtend

    ,@newCityPairInd = CityPairInd

    ,@newExcludeCPInd = ExcludeCPInd

    ,@newCity = City

    ,@newPartnerType = PartnerType

    ,@newLastUpdateDate = LastUpdateDate

    ,@newNextBillDate = NextBillDate

    ,@newReceiveInd = ReceiveInd

    ,@newCodeSharedPrefix = CodeSharedPrefix

    ,@newPartnerCode = PartnerCode

    ,@newLastUpdatedBy = LastUpdatedBy

    ,@newZipCode = ZipCode

    ,@newNextActivityNo = NextActivityNo

    ,@newSendInd = SendInd

    ,@newAddressLine1 = AddressLine1

    ,@newBillingFrequency = BillingFrequency

    ,@newMinMiles = MinMiles

    ,@newState = State

    ,@newAddressLine2 = AddressLine2

    ,@newMaxMiles = MaxMiles

    ,@newIncludeCPInd = IncludeCPInd

    ,@newAddressLine3 = AddressLine3

    ,@newDescription = Description

    from inserted

    else

    begin

    select @newMilesPerSegment = NULL

    select @newCheckDigitRoutine = NULL

    select @newReferenceOverride = 0

    select @newCodeSharedInd = 0

    select @newCountryID = NULL

    select @newStoredProcedure = NULL

    select @newFinalBillingInd = 0

    select @newNameOverride = 0

    select @newZipExtend = NULL

    select @newCityPairInd = 0

    select @newExcludeCPInd = 0

    select @newCity = NULL

    select @newPartnerType = NULL

    select @newLastUpdateDate = NULL

    select @newNextBillDate = NULL

    select @newReceiveInd = 0

    select @newCodeSharedPrefix = NULL

    select @newPartnerCode = NULL

    select @newLastUpdatedBy = NULL

    select @newZipCode = NULL

    select @newNextActivityNo = NULL

    select @newSendInd = 0

    select @newAddressLine1 = NULL

    select @newBillingFrequency = NULL

    select @newMinMiles = NULL

    select @newState = NULL

    select @newAddressLine2 = NULL

    select @newMaxMiles = NULL

    select @newIncludeCPInd = 0

    select @newAddressLine3 = NULL

    select @newDescription = NULL

    end

    if ( @isDeleted = 1 or @isUpdated = 1 )

    select

    @oldMilesPerSegment = MilesPerSegment

    ,@oldCheckDigitRoutine = CheckDigitRoutine

    ,@oldReferenceOverride = ReferenceOverride

    ,@oldCodeSharedInd = CodeSharedInd

    ,@oldCountryID = CountryID

    ,@oldStoredProcedure = StoredProcedure

    ,@oldFinalBillingInd = FinalBillingInd

    ,@oldNameOverride = NameOverride

    ,@oldZipExtend = ZipExtend

    ,@oldCityPairInd = CityPairInd

    ,@oldExcludeCPInd = ExcludeCPInd

    ,@oldCity = City

    ,@oldPartnerType = PartnerType

    ,@oldLastUpdateDate = LastUpdateDate

    ,@oldNextBillDate = NextBillDate

    ,@oldReceiveInd = ReceiveInd

    ,@oldCodeSharedPrefix = CodeSharedPrefix

    ,@oldPartnerCode = PartnerCode

    ,@oldLastUpdatedBy = LastUpdatedBy

    ,@oldZipCode = ZipCode

    ,@oldNextActivityNo = NextActivityNo

    ,@oldSendInd = SendInd

    ,@oldAddressLine1 = AddressLine1

    ,@oldBillingFrequency = BillingFrequency

    ,@oldMinMiles = MinMiles

    ,@oldState = State

    ,@oldAddressLine2 = AddressLine2

    ,@oldMaxMiles = MaxMiles

    ,@oldIncludeCPInd = IncludeCPInd

    ,@oldAddressLine3 = AddressLine3

    ,@oldDescription = Description

    from deleted

    else

    begin

    select @oldMilesPerSegment = NULL

    select @oldCheckDigitRoutine = NULL

    select @oldReferenceOverride = 0

    select @oldCodeSharedInd = 0

    select @oldCountryID = NULL

    select @oldStoredProcedure = NULL

    select @oldFinalBillingInd = 0

    select @oldNameOverride = 0

    select @oldZipExtend = NULL

    select @oldCityPairInd = 0

    select @oldExcludeCPInd = 0

    select @oldCity = NULL

    select @oldPartnerType = NULL

    select @oldLastUpdateDate = NULL

    select @oldNextBillDate = NULL

    select @oldReceiveInd = 0

    select @oldCodeSharedPrefix = NULL

    select @oldPartnerCode = NULL

    select @oldLastUpdatedBy = NULL

    select @oldZipCode = NULL

    select @oldNextActivityNo = NULL

    select @oldSendInd = 0

    select @oldAddressLine1 = NULL

    select @oldBillingFrequency = NULL

    select @oldMinMiles = NULL

    select @oldState = NULL

    select @oldAddressLine2 = NULL

    select @oldMaxMiles = NULL

    select @oldIncludeCPInd = 0

    select @oldAddressLine3 = NULL

    select @oldDescription = NULL

    end

    end

    select @continueWhile = 1 /* We have to get into the while loop */

    select @currentInsertPosition = 1

    select @currentDeletePosition = 1

    if (@singleRowCase = 0 ) /* Multiple Row Case */

    begin

    if ( @isInserted = 1 or @isUpdated = 1 )

    begin

    declare InsertCursorPartner cursor for

    select

    convert(INT,MilesPerSegment)

    ,convert(VARCHAR(50),CheckDigitRoutine)

    ,convert(BIT,ReferenceOverride)

    ,convert(BIT,CodeSharedInd)

    ,convert(VARCHAR(3),CountryID)

    ,convert(VARCHAR(50),StoredProcedure)

    ,convert(BIT,FinalBillingInd)

    ,convert(BIT,NameOverride)

    ,convert(VARCHAR(4),ZipExtend)

    ,convert(BIT,CityPairInd)

    ,convert(BIT,ExcludeCPInd)

    ,convert(VARCHAR(20),City)

    ,convert(VARCHAR(1),PartnerType)

    ,convert(DATETIME,LastUpdateDate)

    ,convert(DATETIME,NextBillDate)

    ,convert(BIT,ReceiveInd)

    ,convert(VARCHAR(3),CodeSharedPrefix)

    ,convert(VARCHAR(5),PartnerCode)

    ,convert(VARCHAR(8),LastUpdatedBy)

    ,convert(VARCHAR(5),ZipCode)

    ,convert(INT,NextActivityNo)

    ,convert(BIT,SendInd)

    ,convert(VARCHAR(30),AddressLine1)

    ,convert(VARCHAR(1),BillingFrequency)

    ,convert(INT,MinMiles)

    ,convert(VARCHAR(2),State)

    ,convert(VARCHAR(30),AddressLine2)

    ,convert(INT,MaxMiles)

    ,convert(BIT,IncludeCPInd)

    ,convert(VARCHAR(30),AddressLine3)

    ,convert(VARCHAR(50),Description)

    from inserted

    open InsertCursorPartner

    end

    if ( @isDeleted = 1 or @isUpdated = 1 )

    begin

    declare DeleteCursorPartner cursor for

    select

    convert(INT,MilesPerSegment)

    ,convert(VARCHAR(50),CheckDigitRoutine)

    ,convert(BIT,ReferenceOverride)

    ,convert(BIT,CodeSharedInd)

    ,convert(VARCHAR(3),CountryID)

    ,convert(VARCHAR(50),StoredProcedure)

    ,convert(BIT,FinalBillingInd)

    ,convert(BIT,NameOverride)

    ,convert(VARCHAR(4),ZipExtend)

    ,convert(BIT,CityPairInd)

    ,convert(BIT,ExcludeCPInd)

    ,convert(VARCHAR(20),City)

    ,convert(VARCHAR(1),PartnerType)

    ,convert(DATETIME,LastUpdateDate)

    ,convert(DATETIME,NextBillDate)

    ,convert(BIT,ReceiveInd)

    ,convert(VARCHAR(3),CodeSharedPrefix)

    ,convert(VARCHAR(5),PartnerCode)

    ,convert(VARCHAR(8),LastUpdatedBy)

    ,convert(VARCHAR(5),ZipCode)

    ,convert(INT,NextActivityNo)

    ,convert(BIT,SendInd)

    ,convert(VARCHAR(30),AddressLine1)

    ,convert(VARCHAR(1),BillingFrequency)

    ,convert(INT,MinMiles)

    ,convert(VARCHAR(2),State)

    ,convert(VARCHAR(30),AddressLine2)

    ,convert(INT,MaxMiles)

    ,convert(BIT,IncludeCPInd)

    ,convert(VARCHAR(30),AddressLine3)

    ,convert(VARCHAR(50),Description)

    from deleted

    open DeleteCursorPartner

    end

    end

    while ( @continueWhile = 1 )

    begin

    if ( @singleRowCase = 0 ) /* Meaning it is a multi row case */

    begin

    if ( @isInserted = 1 or @isUpdated = 1 )

    begin

    fetch InsertCursorPartner into

    @newMilesPerSegment

    ,@newCheckDigitRoutine

    ,@newReferenceOverride

    ,@newCodeSharedInd

    ,@newCountryID

    ,@newStoredProcedure

    ,@newFinalBillingInd

    ,@newNameOverride

    ,@newZipExtend

    ,@newCityPairInd

    ,@newExcludeCPInd

    ,@newCity

    ,@newPartnerType

    ,@newLastUpdateDate

    ,@newNextBillDate

    ,@newReceiveInd

    ,@newCodeSharedPrefix

    ,@newPartnerCode

    ,@newLastUpdatedBy

    ,@newZipCode

    ,@newNextActivityNo

    ,@newSendInd

    ,@newAddressLine1

    ,@newBillingFrequency

    ,@newMinMiles

    ,@newState

    ,@newAddressLine2

    ,@newMaxMiles

    ,@newIncludeCPInd

    ,@newAddressLine3

    ,@newDescription

    select @currentInsertPosition = @currentInsertPosition + 1

    if ( @currentInsertPosition <= @insertcount )

    select @next_fetch_stI = 0

    else

    select @next_fetch_stI = -1

    end

    else

    begin

    select @newMilesPerSegment = NULL

    select @newCheckDigitRoutine = NULL

    select @newReferenceOverride = 0

    select @newCodeSharedInd = 0

    select @newCountryID = NULL

    select @newStoredProcedure = NULL

    select @newFinalBillingInd = 0

    select @newNameOverride = 0

    select @newZipExtend = NULL

    select @newCityPairInd = 0

    select @newExcludeCPInd = 0

    select @newCity = NULL

    select @newPartnerType = NULL

    select @newLastUpdateDate = NULL

    select @newNextBillDate = NULL

    select @newReceiveInd = 0

    select @newCodeSharedPrefix = NULL

    select @newPartnerCode = NULL

    select @newLastUpdatedBy = NULL

    select @newZipCode = NULL

    select @newNextActivityNo = NULL

    select @newSendInd = 0

    select @newAddressLine1 = NULL

    select @newBillingFrequency = NULL

    select @newMinMiles = NULL

    select @newState = NULL

    select @newAddressLine2 = NULL

    select @newMaxMiles = NULL

    select @newIncludeCPInd = 0

    select @newAddressLine3 = NULL

    select @newDescription = NULL

    select @next_fetch_stI = 0

    end

    if ( @isDeleted = 1 or @isUpdated = 1 )

    begin

    fetch DeleteCursorPartner into

    @oldMilesPerSegment

    ,@oldCheckDigitRoutine

    ,@oldReferenceOverride

    ,@oldCodeSharedInd

    ,@oldCountryID

    ,@oldStoredProcedure

    ,@oldFinalBillingInd

    ,@oldNameOverride

    ,@oldZipExtend

    ,@oldCityPairInd

    ,@oldExcludeCPInd

    ,@oldCity

    ,@oldPartnerType

    ,@oldLastUpdateDate

    ,@oldNextBillDate

    ,@oldReceiveInd

    ,@oldCodeSharedPrefix

    ,@oldPartnerCode

    ,@oldLastUpdatedBy

    ,@oldZipCode

    ,@oldNextActivityNo

    ,@oldSendInd

    ,@oldAddressLine1

    ,@oldBillingFrequency

    ,@oldMinMiles

    ,@oldState

    ,@oldAddressLine2

    ,@oldMaxMiles

    ,@oldIncludeCPInd

    ,@oldAddressLine3

    ,@oldDescription

    select @currentDeletePosition = @currentDeletePosition + 1

    if ( @currentDeletePosition = 0)

    begin

    RAISERROR('Column Validation Rule Violated. Error Column: .', 15, -1)

    goto ErrorHandler

    end

    IF NOT (@newPartnerType IN ( 'A' , 'B' , 'C' , 'H' , 'M' ) )

    begin

    RAISERROR('Valid types are ''A''irline, ''B''ank, ''C''ar rental, ''H''otel, and ''M''isc Error Column: .', 15, -1)

    goto ErrorHandler

    end

    IF NOT (@newBillingFrequency IN ( 'D' , 'W' , 'B' , 'M' , 'Q' , 'Y' ) )

    begin

    RAISERROR('Valid values are ''D''aily, ''W''eekly, ''B''iMonthly, ''M''onthly, ''Q''uarterly, and ''Y''early Error Column: .', 15, -1)

    goto ErrorHandler

    end

    IF NOT (@newMinMiles >= 0)

    begin

    RAISERROR('Column Validation Rule Violated. Error Column: .', 15, -1)

    goto ErrorHandler

    end

    IF NOT (@newMaxMiles >= 0)

    begin

    RAISERROR('Column Validation Rule Violated. Error Column: .', 15, -1)

    goto ErrorHandler

    end

    /* Table Constraints */

    IF (@newSendInd = 0 AND @newCityPairInd = 1 )

    begin

    RAISERROR( 'City Pair for Partner is not allowed Error Column: .' , 15, -1)

    goto ErrorHandler

    end

    IF ( (@isUpdated = 1) AND @oldPartnerCode != @newPartnerCode)

    begin

    RAISERROR( 'Cannot change Partner Code Error Column: .' , 15, -1)

    goto ErrorHandler

    end

    IF (@newCodeSharedInd = 0 AND @newCodeSharedPrefix IS NOT NULL )

    begin

    RAISERROR( 'Code Shared Prefix is only for Code Shared partner Error Column: .' , 15, -1)

    goto ErrorHandler

    end

    IF (@newReceiveInd = 0 AND ( @newExcludeCPInd = 1 OR @newIncludeCPInd = 1 ) )

    begin

    RAISERROR( 'Include/Exclude City Pair for Partner is not allowed Error Column: .' , 15, -1)

    goto ErrorHandler

    end

    /* Developer Supplied Code: setNextBillingDate */

    IF (@newNextBillDate IS NULL)

    exec sp_getFrequencyDate @newNextBillDate OUT, @newBillingFrequency

    /* Developer Supplied Code: setNameAddressToUpper */

    IF (@isInserted = 1) OR (@isUpdated = 1 AND (@oldDescription @newDescription OR @oldAddressLine1 @newAddressLine1 OR @oldAddressLine2 @newAddressLine2 OR @oldCity @newCity OR @oldState @newState))

    SELECT @newDescription = UPPER(@newDescription), @newAddressLine1 = UPPER(@newAddressLine1), @newAddressLine2 = UPPER(@newAddressLine2), @newAddressLine3 = UPPER(@newAddressLine3), @newCity = UPPER(@newCity), @newState = UPPER(@newState)

    /* Now verify that the Not nullable derived columns are not null. */

    if @newBillingFrequency is null and ( @isDeleted = 0 )

    begin

    RAISERROR('Column BillingFrequency does not allow null values. Error Column: .', 15, -1)

    goto ErrorHandler

    end

    /* Now update the row */

    if ( @isInserted = 1 or @isUpdated = 1 )

    update Partner

    SET

    MilesPerSegment = @newMilesPerSegment

    , CheckDigitRoutine = @newCheckDigitRoutine

    , ReferenceOverride = @newReferenceOverride

    , CodeSharedInd = @newCodeSharedInd

    , CountryID = @newCountryID

    , StoredProcedure = @newStoredProcedure

    , FinalBillingInd = @newFinalBillingInd

    , NameOverride = @newNameOverride

    , ZipExtend = @newZipExtend

    , CityPairInd = @newCityPairInd

    , ExcludeCPInd = @newExcludeCPInd

    , City = @newCity

    , PartnerType = @newPartnerType

    , LastUpdateDate = @newLastUpdateDate

    , NextBillDate = @newNextBillDate

    , ReceiveInd = @newReceiveInd

    , CodeSharedPrefix = @newCodeSharedPrefix

    , PartnerCode = @newPartnerCode

    , LastUpdatedBy = @newLastUpdatedBy

    , ZipCode = @newZipCode

    , NextActivityNo = @newNextActivityNo

    , SendInd = @newSendInd

    , AddressLine1 = @newAddressLine1

    , BillingFrequency = @newBillingFrequency

    , MinMiles = @newMinMiles

    , State = @newState

    , AddressLine2 = @newAddressLine2

    , MaxMiles = @newMaxMiles

    , IncludeCPInd = @newIncludeCPInd

    , AddressLine3 = @newAddressLine3

    , Description = @newDescription

    where

    PartnerCode = @OnDiskPartnerCode

    /* Conditional Action Events */

    /* Child Cascades */

    if ( @isUpdated = 1 and (

    ((@oldPartnerCode is not null and @newPartnerCode is not null

    and @oldPartnerCode @newPartnerCode ) or

    (@oldPartnerCode is null and @newPartnerCode

    is not null ) or

    (@oldPartnerCode is not null and @newPartnerCode

    is null ) )

    ))

    select @pr25_pKeyChg = 1

    if ( @isDeleted = 1 )

    select @pr25_pKeyDel = 1

    select @pr25_replChg = 0

    if ( @pr25_pKeyChg = 1 or @pr25_pKeyDel = 1 or @pr25_replChg = 1 )

    begin

    select @vsvbCounter = 0

    select @vsvbCounter = count(*) from PartnerBilling

    where

    PartnerCode=@oldPartnerCode

    if @vsvbCounter > 0

    begin

    if @pr25_pKeyDel = 1

    begin

    delete PartnerBilling

    where

    PartnerCode=@oldPartnerCode

    end

    else /* This is update */

    begin

    select @vsvbCounter = 0

    if @pr25_pKeyChg = 1

    RAISERROR('Update Rejected Because There are existing PartnerBilling found for old Partner. Error Column: .',15,-1)

    if @@error 0

    goto ErrorHandler

    end

    end

    end

    if ( @isUpdated = 1 and (

    ((@oldPartnerCode is not null and @newPartnerCode is not null

    and @oldPartnerCode @newPartnerCode ) or

    (@oldPartnerCode is null and @newPartnerCode

    is not null ) or

    (@oldPartnerCode is not null and @newPartnerCode

    is null ) )

    ))

    select @pr27_pKeyChg = 1

    if ( @isDeleted = 1 )

    select @pr27_pKeyDel = 1

    select @pr27_replChg = 0

    if ( @pr27_pKeyChg = 1 or @pr27_pKeyDel = 1 or @pr27_replChg = 1 )

    begin

    select @vsvbCounter = 0

    select @vsvbCounter = count(*) from PartnerBillingRate

    where

    PartnerCode=@oldPartnerCode

    if @vsvbCounter > 0

    begin

    if @pr27_pKeyDel = 1

    begin

    delete PartnerBillingRate

    where

    PartnerCode=@oldPartnerCode

    end

    else /* This is update */

    begin

    select @vsvbCounter = 0

    if @pr27_pKeyChg = 1

    RAISERROR('Update Rejected Because There are existing PartnerBillingRate found for old Partner. Error Column: .',15,-1)

    if @@error 0

    goto ErrorHandler

    end

    end

    end

    if ( @isUpdated = 1 and (

    ((@oldPartnerCode is not null and @newPartnerCode is not null

    and @oldPartnerCode @newPartnerCode ) or

    (@oldPartnerCode is null and @newPartnerCode

    is not null ) or

    (@oldPartnerCode is not null and @newPartnerCode

    is null ) )

    ))

    select @pr64_pKeyChg = 1

    if ( @isDeleted = 1 )

    select @pr64_pKeyDel = 1

    select @pr64_replChg = 0

    if ( @pr64_pKeyChg = 1 or @pr64_pKeyDel = 1 or @pr64_replChg = 1 )

    begin

    select @vsvbCounter = 0

    select @vsvbCounter = count(*) from PartnerExcludeCP

    where

    PartnerCode=@oldPartnerCode

    if @vsvbCounter > 0

    begin

    if @pr64_pKeyDel = 1

    begin

    delete PartnerExcludeCP

    where

    PartnerCode=@oldPartnerCode

    end

    else /* This is update */

    begin

    select @vsvbCounter = 0

    if @pr64_pKeyChg = 1

    RAISERROR('Update Rejected Because There are existing PartnerExcludeCP found for old Partner. Error Column: .',15,-1)

    if @@error 0

    goto ErrorHandler

    end

    end

    end

    if ( @isUpdated = 1 and (

    ((@oldPartnerCode is not null and @newPartnerCode is not null

    and @oldPartnerCode @newPartnerCode ) or

    (@oldPartnerCode is null and @newPartnerCode

    is not null ) or

    (@oldPartnerCode is not null and @newPartnerCode

    is null ) )

    ))

    select @pr76_pKeyChg = 1

    if ( @isDeleted = 1 )

    select @pr76_pKeyDel = 1

    select @pr76_replChg = 0

    if ( @pr76_pKeyChg = 1 or @pr76_pKeyDel = 1 or @pr76_replChg = 1 )

    begin

    select @vsvbCounter = 0

    select @vsvbCounter = count(*) from PartnerContact

    where

    PartnerCode=@oldPartnerCode

    if @vsvbCounter > 0

    begin

    if @pr76_pKeyDel = 1

    begin

    delete PartnerContact

    where

    PartnerCode=@oldPartnerCode

    end

    else /* This is update */

    begin

    select @vsvbCounter = 0

    if @pr76_pKeyChg = 1

    RAISERROR('Update Rejected Because There are existing PartnerContact found for old Partner. Error Column: .',15,-1)

    if @@error 0

    goto ErrorHandler

    end

    end

    end

    if ( @isUpdated = 1 and (

    ((@oldPartnerCode is not null and @newPartnerCode is not null

    and @oldPartnerCode @newPartnerCode ) or

    (@oldPartnerCode is null and @newPartnerCode

    is not null ) or

    (@oldPartnerCode is not null and @newPartnerCode

    is null ) )

    ))

    select @pr65_pKeyChg = 1

    if ( @isDeleted = 1 )

    select @pr65_pKeyDel = 1

    select @pr65_replChg = 0

    if ( @pr65_pKeyChg = 1 or @pr65_pKeyDel = 1 or @pr65_replChg = 1 )

    begin

    select @vsvbCounter = 0

    select @vsvbCounter = count(*) from PartnerIncludeCP

    where

    PartnerCode=@oldPartnerCode

    if @vsvbCounter > 0

    begin

    if @pr65_pKeyDel = 1

    begin

    delete PartnerIncludeCP

    where

    PartnerCode=@oldPartnerCode

    end

    else /* This is update */

    begin

    select @vsvbCounter = 0

    if @pr65_pKeyChg = 1

    RAISERROR('Update Rejected Because There are existing PartnerIncludeCP found for old Partner. Error Column: .',15,-1)

    if @@error 0

    goto ErrorHandler

    end

    end

    end

    if ( @isUpdated = 1 and (

    ((@oldPartnerCode is not null and @newPartnerCode is not null

    and @oldPartnerCode @newPartnerCode ) or

    (@oldPartnerCode is null and @newPartnerCode

    is not null ) or

    (@oldPartnerCode is not null and @newPartnerCode

    is null ) )

    ))

    select @pr66_pKeyChg = 1

    if ( @isDeleted = 1 )

    select @pr66_pKeyDel = 1

    select @pr66_replChg = 0

    if ( @pr66_pKeyChg = 1 or @pr66_pKeyDel = 1 or @pr66_replChg = 1 )

    begin

    select @vsvbCounter = 0

    select @vsvbCounter = count(*) from PartnerIncomingBC

    where

    PartnerCode=@oldPartnerCode

    if @vsvbCounter > 0

    begin

    if @pr66_pKeyDel = 1

    begin

    delete PartnerIncomingBC

    where

    PartnerCode=@oldPartnerCode

    end

    else /* This is update */

    begin

    select @vsvbCounter = 0

    if @pr66_pKeyChg = 1

    RAISERROR('Update Rejected Because There are existing PartnerIncomingBC found for old Partner. Error Column: .',15,-1)

    if @@error 0

    goto ErrorHandler

    end

    end

    end

    if ( @isUpdated = 1 and (

    ((@oldPartnerCode is not null and @newPartnerCode is not null

    and @oldPartnerCode @newPartnerCode ) or

    (@oldPartnerCode is null and @newPartnerCode

    is not null ) or

    (@oldPartnerCode is not null and @newPartnerCode

    is null ) )

    ))

    select @pr67_pKeyChg = 1

    if ( @isDeleted = 1 )

    select @pr67_pKeyDel = 1

    select @pr67_replChg = 0

    if ( @pr67_pKeyChg = 1 or @pr67_pKeyDel = 1 or @pr67_replChg = 1 )

    begin

    select @vsvbCounter = 0

    select @vsvbCounter = count(*) from PartnerOutgoingBC

    where

    PartnerCode=@oldPartnerCode

    if @vsvbCounter > 0

    begin

    if @pr67_pKeyDel = 1

    begin

    delete PartnerOutgoingBC

    where

    PartnerCode=@oldPartnerCode

    end

    else /* This is update */

    begin

    select @vsvbCounter = 0

    if @pr67_pKeyChg = 1

    RAISERROR('Update Rejected Because There are existing PartnerOutgoingBC found for old Partner. Error Column: .',15,-1)

    if @@error 0

    goto ErrorHandler

    end

    end

    end

    if ( @isUpdated = 1 and (

    ((@oldPartnerCode is not null and @newPartnerCode is not null

    and @oldPartnerCode @newPartnerCode ) or

    (@oldPartnerCode is null and @newPartnerCode

    is not null ) or

    (@oldPartnerCode is not null and @newPartnerCode

    is null ) )

    ))

    select @pr34_pKeyChg = 1

    if ( @isDeleted = 1 )

    select @pr34_pKeyDel = 1

    select @pr34_replChg = 0

    if ( @pr34_pKeyChg = 1 or @pr34_pKeyDel = 1 or @pr34_replChg = 1 )

    begin

    select @vsvbCounter = 0

    select @vsvbCounter = count(*) from PartnerCityPair

    where

    PartnerCode=@oldPartnerCode

    if @vsvbCounter > 0

    begin

    if @pr34_pKeyDel = 1

    begin

    delete PartnerCityPair

    where

    PartnerCode=@oldPartnerCode

    end

    else /* This is update */

    begin

    select @vsvbCounter = 0

    if @pr34_pKeyChg = 1

    RAISERROR('Update Rejected Because There are existing PartnerCityPair found for old Partner. Error Column: .',15,-1)

    if @@error 0

    goto ErrorHandler

    end

    end

    end

    /* Reinit the old values to the ones on the disk. */

    /* Parent Adjustment Init */

    end /* End of while loop */

    end_of_trigger:

    select @normal_finish = 1

    ErrorHandler:

    if ( @singleRowCase = 0 )

    begin

    if ( @isInserted = 1 or @isUpdated = 1 )

    begin

    close InsertCursorPartner

    deallocate InsertCursorPartner

    end

    if ( @isDeleted = 1 or @isUpdated = 1 )

    begin

    close DeleteCursorPartner

    deallocate DeleteCursorPartner

    end

    end

    if ( @normal_finish = 0 )

    begin

    ROLLBACK TRAN

    return

    end

    --Begin: Change required for refreshing cache objects in Hawaiian Miles Web Application.

    if ( @normal_finish = 1 )

    begin

    exec AspNet_SqlCacheUpdateChangeIdStoredProcedure 'Partner'

    end

    -- End

    end

    Indexes for the Partner table(above)

    Table NameIndex IdIndex NameColumn OrderColumn NamePrimary KeyUnique KeyMS Shipped

    Partner1PKEY_Partner1PartnerCodeyyn

    Partner2U_Description_Partner1Descriptionnyn

    Partner3CodeSharedPrefix_Partner1CodeSharedPrefixnnn

    Partner4ZipCode_Partner1ZipCodennn

    Table Structure

    PartnerPartnerCode

    Description

    PartnerType

    AddressLine1

    AddressLine2

    AddressLine3

    City

    State

    ZipCode

    ZipExtend

    CountryID

    CodeSharedInd

    CodeSharedPrefix

    ReceiveInd

    MilesPerSegment

    ExcludeCPInd

    IncludeCPInd

    StoredProcedure

    NextActivityNo

    NextBillDate

    LastUpdateDate

    LastUpdatedBy

    CheckDigitRoutine

    SendInd

    MinMiles

    MaxMiles

    CityPairInd

    ReferenceOverride

    NameOverride

    FinalBillingInd

    BillingFrequency

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply