Triggers

USE [Akash_MSCRM]

GO

/****** Object: Trigger [dbo].[UpdateStateEndInShipStatusHistory] Script Date: 09/07/2010 15:54:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[UpdateStateEndInShipStatusHistory]

ON [dbo].[Akash_ShipStatusHistoryExtensionBase]

AFTER INSERT

AS

DECLARE @CurrentRecordStateStartDate datetime

DECLARE @CurrentRecordShipStatusHistoryId uniqueidentifier --RecordID

DECLARE @CurrentRecordShiphistoryShipid uniqueidentifier --MemberID

DECLARE @LastUpdatedShipStatusHistoryId uniqueidentifier

SELECT @CurrentRecordStateStartDate = Akash_StateStartDate ,

@CurrentRecordShipStatusHistoryId = Akash_ShipStatusHistoryId,

@CurrentRecordShiphistoryShipid = Akash_ShiphistoryShipid

FROM INSERTED

SELECT TOP 1 @LastUpdatedShipStatusHistoryId = Akash_ShipStatusHistoryId

FROM Akash_ShipStatusHistory

WHERE Akash_ShiphistoryShipid = @CurrentRecordShiphistoryShipid AND

Akash_ShipStatusHistoryId != @CurrentRecordShipStatusHistoryId AND

Akash_StateStartDate < GETDATE() AND

DeletionStateCode = '0' AND

Akash_StateEndDate IS NULL

ORDER BY Akash_StateStartDate DESC

IF (@LastUpdatedShipStatusHistoryId IS NOT NULL)

BEGIN

UPDATE Akash_ShipStatusHistoryExtensionBase SET Akash_StateEndDate =@CurrentRecordStateStartDate

WHERE Akash_ShipStatusHistoryId = @LastUpdatedShipStatusHistoryId

END

DECLARE @ShipStatus int --Ship Status

DECLARE @PreviousAwaitingFirstPaymentStatusHistory uniqueidentifier

SELECT @ShipStatus = Akash_ShipStatus ,

@CurrentRecordShiphistoryShipid = Akash_ShiphistoryShipid,

@CurrentRecordShipStatusHistoryId = Akash_ShipStatusHistoryid

FROM INSERTED--Akash_ShipStatusHistory where Akash_ShipStatusHistoryid = 'A6C1BFBB-1C9F-DF11-9AFD-0050569F7F2C'

IF (@ShipStatus IS NOT NULL)

BEGIN

IF (@ShipStatus = 5)

BEGIN

SELECT TOP 1 @PreviousAwaitingFirstPaymentStatusHistory = Akash_ShipStatusHistoryId

FROM Akash_ShipStatusHistory

WHERE Akash_ShiphistoryShipid = @CurrentRecordShiphistoryShipid

AND CreatedOn < GETDATE()

AND Akash_ShipStatusHistoryId != @CurrentRecordShipStatusHistoryId

AND Akash_ShipStatus = 5

ORDER BY CreatedOn DESC

IF(@PreviousAwaitingFirstPaymentStatusHistory IS NOT NULL)

BEGIN

DECLARE @CursorShipStatusHistoryId uniqueidentifier

DECLARE StatusHistoryDeleteCursor

CURSOR FOR SELECT Akash_ShipStatusHistoryid

FROM Akash_ShipStatusHistory

WHERE Akash_ShiphistoryShipid = @CurrentRecordShiphistoryShipid

AND CreatedOn < DATEADD(n,-3,GETDATE())

AND Akash_ShipStatusHistoryId != @PreviousAwaitingFirstPaymentStatusHistory

OPEN StatusHistoryDeleteCursor

FETCH NEXT FROM StatusHistoryDeleteCursor INTO @CursorShipStatusHistoryId

WHILE @@FETCH_STATUS = 0

BEGIN

DELETE FROM Akash_ShipStatusHistoryExtensionBase WHERE Akash_ShipStatusHistoryId = @CursorShipStatusHistoryId

DELETE FROM Akash_ShipStatusHistoryBase WHERE Akash_ShipStatusHistoryId = @CursorShipStatusHistoryId

FETCH NEXT FROM StatusHistoryDeleteCursor INTO @CursorShipStatusHistoryId

END

CLOSE StatusHistoryDeleteCursor

DEALLOCATE StatusHistoryDeleteCursor

UPDATE Akash_ShipStatusHistoryExtensionBase SET Akash_StateEndDate = NULL WHERE Akash_ShipStatusHistoryId = @PreviousAwaitingFirstPaymentStatusHistory

COMMIT TRANSACTION

DELETE FROM Akash_ShipStatusHistoryExtensionBase WHERE Akash_ShipStatusHistoryId = @CurrentRecordShipStatusHistoryId

DELETE FROM Akash_ShipStatusHistoryBase WHERE Akash_ShipStatusHistoryId = @CurrentRecordShipStatusHistoryId

END

END

END