April 23, 2009 at 6:08 pm
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
April 23, 2009 at 6:44 pm
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
April 28, 2009 at 8:45 pm
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