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
