ALTER TRIGGER [dbo].[trPkgDPRBidSubmissionDetails]
   ON  [dbo].[tblPkgDPRBidSubmissionDetails]
   AFTER insert, update, delete
AS

BEGIN

 DECLARE @Action as varchar(50);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


SET @Action = 'Insert'; -- Set Action to Insert by default.
IF EXISTS(SELECT * FROM DELETED)
BEGIN
    SET @Action =
        CASE
           WHEN EXISTS(SELECT * FROM INSERTED) THEN 'UPDATE'
           ELSE 'DELETE'
        END
END


if( @Action = 'DELETE')
begin
    insert into tblPkgDPRBidSubmissionDetailsAT(iUserId,cAction, dtAction,iActionOwner,iPackage,dtInitialDesignReport,dtDPR, dtBidDocuments,dtExternalReview1,dtComplianceToPWD1,dtComplianceToExtAgency1,dtExternalReview2,dtComplianceToPWD2,dtComplianceToExtAgency2, dtApprovalFromExternalAgency,dtIFB,dtUpToIssue,dtPreMeeting,dtBidOpeningMeetingOriginal, dtBidOpeningMeetingExtension1,dtBidOpeningMeetingExtension2,dtBidOpeningMeetingExtension3, iNumberOfBidsSold,iNumberOfBidsReceived)

select iUserId,@Action, getdate(), 1,iPackage,dtInitialDesignReport, dtDPR, dtBidDocuments,dtExternalReview1, dtComplianceToPWD1,dtComplianceToExtAgency1,dtExternalReview2,dtComplianceToPWD2,dtComplianceToExtAgency2,dtApprovalFromExternalAgency, dtIFB,dtUpToIssue,dtPreMeeting,dtBidOpeningMeetingOriginal,dtBidOpeningMeetingExtension1,dtBidOpeningMeetingExtension2,dtBidOpeningMeetingExtension3, iNumberOfBidsSold,iNumberOfBidsReceived
from deleted;
end

else
begin

insert into tblPkgDPRBidSubmissionDetailsAT(iUserId,cAction, dtAction,iActionOwner,iPackage,dtInitialDesignReport,dtDPR, dtBidDocuments,dtExternalReview1,dtComplianceToPWD1,dtComplianceToExtAgency1,dtExternalReview2,dtComplianceToPWD2,dtComplianceToExtAgency2, dtApprovalFromExternalAgency,dtIFB,dtUpToIssue,dtPreMeeting,dtBidOpeningMeetingOriginal, dtBidOpeningMeetingExtension1,dtBidOpeningMeetingExtension2, dtBidOpeningMeetingExtension3,iNumberOfBidsSold,iNumberOfBidsReceived)

select iUserId,@Action, getdate(), 1,iPackage,dtInitialDesignReport, dtDPR, dtBidDocuments,dtExternalReview1, dtComplianceToPWD1,dtComplianceToExtAgency1,dtExternalReview2,dtComplianceToPWD2,dtComplianceToExtAgency2,dtApprovalFromExternalAgency, dtIFB,dtUpToIssue,dtPreMeeting,dtBidOpeningMeetingOriginal,dtBidOpeningMeetingExtension1,dtBidOpeningMeetingExtension2,dtBidOpeningMeetingExtension3, iNumberOfBidsSold,iNumberOfBidsReceived
from inserted;
end
-- Insert statements for trigger here
-- action owner field to be udpated

END

if( @Action = 'DELETE')
begin
    insert into tblPkgDPRBidSubmissionDetailsAT(iUserId,cAction, dtAction,iActionOwner,iPackage,dtInitialDesignReport,dtDPR, dtBidDocuments,dtExternalReview1,dtComplianceToPWD1,dtComplianceToExtAgency1,dtExternalReview2,dtComplianceToPWD2,dtComplianceToExtAgency2, dtApprovalFromExternalAgency,dtIFB,dtUpToIssue,dtPreMeeting,dtBidOpeningMeetingOriginal, dtBidOpeningMeetingExtension1,dtBidOpeningMeetingExtension2,dtBidOpeningMeetingExtension3, iNumberOfBidsSold,iNumberOfBidsReceived)
    select iUserId,@Action, getdate(), 1,iPackage,dtInitialDesignReport, dtDPR, dtBidDocuments,dtExternalReview1, dtComplianceToPWD1,dtComplianceToExtAgency1,dtExternalReview2,dtComplianceToPWD2,dtComplianceToExtAgency2,dtApprovalFromExternalAgency, dtIFB,dtUpToIssue,dtPreMeeting,dtBidOpeningMeetingOriginal,dtBidOpeningMeetingExtension1,dtBidOpeningMeetingExtension2,dtBidOpeningMeetingExtension3, iNumberOfBidsSold,iNumberOfBidsReceived
    from deleted;
end

else
begin
    if @Action = 'INSERT'
        insert into tblPkgDPRBidSubmissionDetailsAT(iUserId,cAction, dtAction,iActionOwner,iPackage,dtInitialDesignReport,dtDPR, dtBidDocuments,dtExternalReview1,dtComplianceToPWD1,dtComplianceToExtAgency1,dtExternalReview2,dtComplianceToPWD2,dtComplianceToExtAgency2, dtApprovalFromExternalAgency,dtIFB,dtUpToIssue,dtPreMeeting,dtBidOpeningMeetingOriginal, dtBidOpeningMeetingExtension1,dtBidOpeningMeetingExtension2, dtBidOpeningMeetingExtension3,iNumberOfBidsSold,iNumberOfBidsReceived)
        select iUserId,@Action, getdate(), 1,iPackage,dtInitialDesignReport, dtDPR, dtBidDocuments,dtExternalReview1, dtComplianceToPWD1,dtComplianceToExtAgency1,dtExternalReview2,dtComplianceToPWD2,dtComplianceToExtAgency2,dtApprovalFromExternalAgency, dtIFB,dtUpToIssue,dtPreMeeting,dtBidOpeningMeetingOriginal,dtBidOpeningMeetingExtension1,dtBidOpeningMeetingExtension2,dtBidOpeningMeetingExtension3, iNumberOfBidsSold,iNumberOfBidsReceived
        from inserted;
end