Trigger help

Question

Post reply

Trigger help

K430

SSC Veteran

Points: 264
More actions
June 29, 2011 at 11:35 am

#98213

OK i have this trigger which is to populate all the fields when the date+approved field is initially entered, after that if of the already populated fields is updated the fields following it should alos be updated. What is wring that this is not working. UGHHHH

DROP TRIGGER Procurement_Update

GO

/****** Object: Trigger [dbo].[Procurement_Update] Script Date: 06/29/2011 10:19:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[Procurement_Update] ON [dbo].[proposals]

FOR UPDATE

AS

BEGIN

--** DECLARE VARIABLES **

declare

@sh_act_rfi_date datetime,

@date_approved datetime,

@sh_act_rfi_days int,

@proposal_type int,

@proposal_number char(15),

@sh_plan_rfi_date datetime,

@sh_act_bid_list_app_date datetime,

@sh_act_proposal_issue_date datetime,

@sh_act_bid_close_date datetime,

@sh_act_tech_eval_date datetime,

@sh_act_commercial_eval_date datetime,

@sh_act_management_eval_date datetime,

@sh_act_board_app_date datetime,

@sh_act_bid_list_app_days int,

@sh_act_proposal_issue_days int,

@sh_act_bid_close_days int,

@sh_act_tech_eval_days int,

@sh_act_commercial_eval_days int,

@sh_act_management_eval_days int,

@sh_act_board_app_days int,

@sh_plan_rfi_days int,

@sh_plan_bid_list_app_days int,

@sh_plan_proposal_issue_days int,

@sh_plan_bid_close_days int,

@sh_plan_tech_eval_days int,

@sh_plan_commercial_eval_days int,

@sh_plan_management_eval_days int,

@sh_plan_board_app_days int,

@sh_initial_rfi_date datetime,

@sh_plan_bid_list_app_date datetime,

@sh_plan_proposal_issue_date datetime,

@sh_plan_bid_close_date datetime,

@sh_plan_tech_eval_date datetime,

@sh_plan_commercial_eval_date datetime,

@sh_plan_management_eval_date datetime,

@sh_plan_board_app_date datetime,

@sh_act_itt_days int,

@sh_act_itt_bid_list_app_days int,

@sh_act_itt_proposal_issue_days int,

@sh_act_itt_bid_close_days int,

@sh_act_itt_tech_eval_days int,

@sh_act_itt_commercial_eval_days int,

@sh_act_itt_management_eval_days int,

@sh_act_itt_board_app_days int,

@sh_plan_itt_days int,

@sh_plan_itt_bid_list_app_days int,

@sh_plan_itt_proposal_issue_days int,

@sh_plan_itt_bid_close_days int,

@sh_plan_itt_tech_eval_days int,

@sh_plan_itt_commercial_eval_days int,

@sh_plan_itt_management_eval_days int,

@sh_plan_itt_board_app_days int,

@sh_act_itt_date datetime,

@sh_act_itt_bid_list_app_date datetime,

@sh_act_itt_proposal_issue_date datetime,

@sh_act_itt_bid_close_date datetime,

@sh_act_itt_tech_eval_date datetime,

@sh_act_itt_commercial_eval_date datetime,

@sh_act_itt_management_eval_date datetime,

@sh_act_itt_board_app_date datetime,

@fromDate datetime,

@daysToAdd int,

@sh_plan_itt_date datetime,

@sh_plan_itt_bid_list_app_date datetime,

@sh_plan_itt_proposal_issue_date datetime,

@sh_plan_itt_bid_close_date datetime,

@sh_plan_itt_tech_eval_date datetime,

@sh_plan_itt_commercial_eval_date datetime,

@sh_plan_itt_management_eval_date datetime,

@sh_plan_itt_board_app_date datetime

--** POPULATE THE VARIABLES **

set @sh_act_rfi_days = (select sh_act_rfi_days from sh_procurement_days);

set @sh_act_bid_list_app_days = (select sh_act_bid_list_app_days from sh_procurement_days);

set @sh_act_proposal_issue_days = (select sh_act_proposal_issue_days from sh_procurement_days);

set @sh_act_bid_close_days = (select sh_act_bid_close_days from sh_procurement_days);

set @sh_act_tech_eval_days = (select sh_act_tech_eval_days from sh_procurement_days);

set @sh_act_commercial_eval_days = (select sh_act_commercial_eval_days from sh_procurement_days);

set @sh_act_management_eval_days = (select sh_act_management_eval_days from sh_procurement_days);

set @sh_act_board_app_days = (select sh_act_board_app_days from sh_procurement_days);

set @sh_plan_rfi_days = (select sh_act_rfi_days from sh_procurement_days);

set @sh_plan_bid_list_app_days = (select sh_act_bid_list_app_days from sh_procurement_days);

set @sh_plan_proposal_issue_days = (select sh_act_proposal_issue_days from sh_procurement_days);

set @sh_plan_bid_close_days = (select sh_act_bid_close_days from sh_procurement_days);

set @sh_plan_tech_eval_days = (select sh_act_tech_eval_days from sh_procurement_days);

set @sh_plan_commercial_eval_days = (select sh_act_commercial_eval_days from sh_procurement_days);

set @sh_plan_management_eval_days = (select sh_act_management_eval_days from sh_procurement_days);

set @sh_plan_board_app_days = (select sh_act_board_app_days from sh_procurement_days);

set @date_approved = (select date_approved from inserted);

set @proposal_number = (select proposal_number from inserted);

set @proposal_type = (select proposal_type from inserted);

select @sh_act_rfi_date = DATEADD (day, CONVERT (INT, sh_act_rfi_days/5)*7 + sh_act_rfi_days%5, @date_approved) from sh_procurement_days;

select @sh_act_bid_list_app_date = DATEADD (day, CONVERT (INT, sh_act_bid_list_app_days/5)*7 + sh_act_bid_list_app_days%5, @sh_act_rfi_date) from sh_procurement_days;

select @sh_act_proposal_issue_date = DATEADD (day, CONVERT (INT, sh_act_proposal_issue_days/5)*7 + sh_act_proposal_issue_days%5,@sh_act_bid_list_app_date) from sh_procurement_days;

select @sh_act_bid_close_date = DATEADD (day, CONVERT (INT, sh_act_bid_close_days/5)*7 + sh_act_bid_close_days%5, @sh_act_proposal_issue_date) from sh_procurement_days;

select @sh_act_tech_eval_date = DATEADD (day, CONVERT (INT, sh_act_tech_eval_days/5)*7 + sh_act_tech_eval_days%5, @sh_act_bid_close_date) from sh_procurement_days;

select @sh_act_commercial_eval_date = DATEADD (day, CONVERT (INT, sh_act_commercial_eval_days/5)*7 + sh_act_commercial_eval_days%5, @sh_act_tech_eval_date) from sh_procurement_days;

select @sh_act_management_eval_date = DATEADD (day, CONVERT (INT, sh_act_management_eval_days/5)*7 + sh_act_management_eval_days%5, @sh_act_commercial_eval_date) from sh_procurement_days;

select @sh_act_board_app_date = DATEADD (day, CONVERT (INT, sh_act_board_app_days/5)*7 + sh_act_board_app_days%5, @sh_act_management_eval_date) from sh_procurement_days;

select @sh_plan_rfi_date = @sh_act_rfi_date;

select @sh_plan_bid_list_app_date = @sh_act_bid_list_app_date

select @sh_plan_proposal_issue_date = @sh_act_proposal_issue_date;

select @sh_plan_bid_close_date = @sh_act_bid_close_date;

select @sh_plan_tech_eval_date = @sh_act_tech_eval_date ;

select @sh_plan_commercial_eval_date = @sh_act_commercial_eval_date;

select @sh_plan_management_eval_date = @sh_act_management_eval_date;

select @sh_plan_board_app_date = @sh_act_board_app_date;

set @sh_act_itt_days =(select sh_act_itt_days from sh_procurement_days);

set @sh_act_itt_bid_list_app_days =(select sh_act_itt_bid_list_app_days from sh_procurement_days);

set @sh_act_itt_proposal_issue_days =(select sh_act_itt_proposal_issue_days from sh_procurement_days);

SET @sh_act_itt_bid_close_days =(select sh_act_itt_bid_close_days from sh_procurement_days);

SET @sh_act_itt_tech_eval_days =(select sh_act_itt_tech_eval_days from sh_procurement_days);

SET @sh_act_itt_commercial_eval_days =(select sh_act_itt_commercial_eval_days from sh_procurement_days);

SET @sh_act_itt_management_eval_days =(select sh_act_itt_management_eval_days from sh_procurement_days);

SET @sh_act_itt_board_app_days =(select sh_act_itt_board_app_days from sh_procurement_days);

set @sh_plan_itt_days =(select sh_act_itt_days from sh_procurement_days);

set @sh_plan_itt_bid_list_app_days =(select sh_act_itt_bid_list_app_days from sh_procurement_days);

set @sh_plan_itt_proposal_issue_days =(select sh_act_itt_proposal_issue_days from sh_procurement_days);

SET @sh_plan_itt_bid_close_days =(select sh_act_itt_bid_close_days from sh_procurement_days);

SET @sh_plan_itt_tech_eval_days =(select sh_act_itt_tech_eval_days from sh_procurement_days);

SET @sh_plan_itt_commercial_eval_days =(select sh_act_itt_commercial_eval_days from sh_procurement_days);

SET @sh_plan_itt_management_eval_days =(select sh_act_itt_management_eval_days from sh_procurement_days);

SET @sh_plan_itt_board_app_days =(select sh_act_itt_board_app_days from sh_procurement_days);

select @sh_act_itt_date = DATEADD (day,CONVERT (INT, sh_act_itt_days/5)*7 + sh_act_itt_days%5, @date_approved) from sh_procurement_days;

select @sh_act_itt_bid_list_app_date = DATEADD (day,CONVERT (INT, sh_act_itt_bid_list_app_days/5)*7 + sh_act_itt_bid_list_app_days%5, @sh_act_itt_date) from sh_procurement_days;

select @sh_act_itt_proposal_issue_date= DATEADD (day, CONVERT (INT, sh_act_itt_proposal_issue_days/5)*7 + sh_act_itt_proposal_issue_days%5,@sh_act_itt_bid_list_app_date) from sh_procurement_days;

select @sh_act_itt_bid_close_date = DATEADD (day, CONVERT (INT, sh_act_itt_bid_close_days/5)*7 + sh_act_itt_bid_close_days%5, @sh_act_itt_proposal_issue_date) from sh_procurement_days;

select @sh_act_itt_tech_eval_date = DATEADD (day, CONVERT (INT, sh_act_itt_tech_eval_days/5)*7 + sh_act_itt_tech_eval_days%5, @sh_act_itt_bid_close_date) from sh_procurement_days;

select @sh_act_itt_commercial_eval_date=DATEADD (day, CONVERT (INT, sh_act_itt_commercial_eval_days/5)*7 + sh_act_itt_commercial_eval_days%5, @sh_act_itt_tech_eval_date) from sh_procurement_days;

select @sh_act_itt_management_eval_date=DATEADD (day, CONVERT (INT, sh_act_itt_management_eval_days/5)*7 + sh_act_itt_management_eval_days%5,@sh_act_itt_commercial_eval_date)from sh_procurement_days;

select @sh_act_itt_board_app_date = DATEADD (day, CONVERT (INT, sh_act_itt_board_app_days/5)*7 + sh_act_itt_board_app_days%5, @sh_act_itt_management_eval_date) from sh_procurement_days;

set @sh_plan_itt_date = @sh_act_itt_date

set @sh_plan_itt_bid_list_app_date = @sh_act_itt_bid_list_app_date

set @sh_plan_itt_proposal_issue_date = @sh_act_itt_proposal_issue_date

set @sh_plan_itt_bid_close_date = @sh_act_itt_bid_close_date

set @sh_plan_itt_tech_eval_date = @sh_act_itt_tech_eval_date

set @sh_plan_itt_commercial_eval_date = @sh_act_itt_commercial_eval_date

set @sh_plan_itt_management_eval_date = @sh_act_itt_management_eval_date

set @sh_plan_itt_board_app_date = @sh_act_itt_board_app_date

IF @sh_act_commercial_eval_date IS NOT NULL AND @proposal_type = 855

BEGIN

UPDATE proposals SET sh_act_management_eval_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @sh_act_tech_eval_date IS NOT NULL AND @proposal_type = 855 --RFQ

BEGIN

UPDATE proposals SET sh_act_commercial_eval_date = @sh_act_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_management_eval_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @sh_act_bid_close_date IS NOT NULL AND @proposal_type = 855

BEGIN

UPDATE proposals SET sh_act_tech_eval_date = @sh_act_tech_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_commercial_eval_date = @sh_act_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_management_eval_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @sh_act_proposal_issue_date IS NOT NULL AND @proposal_type = 855

BEGIN

UPDATE proposals SET sh_act_bid_close_date = @sh_act_bid_close_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_tech_eval_date = @sh_act_tech_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_commercial_eval_date = @sh_act_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_management_eval_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @sh_act_bid_list_app_date IS NOT NULL AND @proposal_type = 855

BEGIN

UPDATE proposals SET sh_act_proposal_issue_date = @sh_act_proposal_issue_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_bid_close_date = @sh_act_bid_close_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_tech_eval_date = @sh_act_tech_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_commercial_eval_date = @sh_act_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_management_eval_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @sh_act_rfi_date IS NOT NULL AND @proposal_type = 855

BEGIN

UPDATE proposals SET sh_act_bid_list_app_date = @sh_act_bid_list_app_date WHERE proposal_number = @proposal_number;

UPDATE proposals SET sh_act_proposal_issue_date = @sh_act_proposal_issue_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_bid_close_date = @sh_act_bid_close_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_tech_eval_date = @sh_act_tech_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_commercial_eval_date = @sh_act_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_management_eval_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @date_approved IS NOT NULL AND @proposal_type = 855

BEGIN

UPDATE proposals SET sh_act_rfi_date = @sh_act_rfi_date WHERE proposal_number = @proposal_number;

UPDATE proposals SET sh_act_bid_list_app_date = @sh_act_bid_list_app_date WHERE proposal_number = @proposal_number;

UPDATE proposals SET sh_act_proposal_issue_date = @sh_act_proposal_issue_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_bid_close_date = @sh_act_bid_close_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_tech_eval_date = @sh_act_tech_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_commercial_eval_date = @sh_act_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_management_eval_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_rfi_date = @sh_plan_rfi_date WHERE proposal_number = @proposal_number;

UPDATE proposals SET sh_plan_bid_list_app_date = @sh_plan_bid_list_app_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_proposal_issue_date = @sh_plan_proposal_issue_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_bid_close_date = @sh_plan_bid_close_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_tech_eval_date = @sh_plan_tech_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_commercial_eval_date = @sh_plan_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_management_eval_date = @sh_plan_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_board_app_date = @sh_plan_management_eval_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @date_approved IS NOT NULL AND @proposal_type = 855

BEGIN

INSERT INTO SH_procurement_process

(proposal_number, sh_intial_rfi_enter_date, sh_act_rfi_date,sh_act_bid_list_app_date, sh_act_proposal_issue_date, sh_act_bid_close_date,

sh_act_tech_eval_date, sh_act_commercial_eval_date, sh_act_management_eval_date,

sh_plan_rfi_date, sh_plan_bid_list_app_date, sh_plan_proposal_issue_date, sh_plan_bid_close_date,

sh_plan_tech_eval_date, sh_plan_commercial_eval_date, sh_plan_management_eval_date,

sh_act_rfi_days, sh_act_bid_list_app_days, sh_act_proposal_issue_days,

sh_act_bid_close_days, sh_act_tech_eval_days, sh_act_commercial_eval_days, sh_act_management_eval_days,

sh_plan_rfi_days, sh_plan_bid_list_app_days, sh_plan_proposal_issue_days,

sh_plan_bid_close_days, sh_plan_tech_eval_days, sh_plan_commercial_eval_days, sh_plan_management_eval_days, proposal_type)

VALUES (

@proposal_number,@date_approved, @sh_act_rfi_date, @sh_act_bid_list_app_date, @sh_act_proposal_issue_date, @sh_act_bid_close_date,

@sh_act_tech_eval_date, @sh_act_commercial_eval_date, @sh_act_management_eval_date,

@sh_plan_rfi_date, @sh_plan_bid_list_app_date, @sh_plan_proposal_issue_date, @sh_plan_bid_close_date,

@sh_plan_tech_eval_date, @sh_plan_commercial_eval_date, @sh_plan_management_eval_date,

@sh_act_rfi_days, @sh_act_bid_list_app_days, @sh_act_proposal_issue_days,

@sh_act_bid_close_days, @sh_act_tech_eval_days, @sh_act_commercial_eval_days, @sh_act_management_eval_days,

@sh_plan_rfi_days, @sh_plan_bid_list_app_days, @sh_plan_proposal_issue_days,

@sh_plan_bid_close_days, @sh_plan_tech_eval_days, @sh_plan_commercial_eval_days, @sh_plan_management_eval_days, @proposal_type);

END

ELSE

IF @sh_act_itt_management_eval_date IS NOT NULL AND @proposal_type = 856 --ITT

BEGIN

UPDATE proposals SET sh_act_board_app_date = @sh_act_itt_board_app_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @sh_act_itt_commercial_eval_date IS NOT NULL AND @proposal_type = 856

BEGIN

UPDATE proposals SET sh_act_management_eval_date = @sh_act_itt_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_itt_board_app_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @sh_act_itt_tech_eval_date IS NOT NULL AND @proposal_type = 856

BEGIN

UPDATE proposals SET sh_act_commercial_eval_date = @sh_act_itt_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_management_eval_date = @sh_act_itt_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_itt_board_app_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @sh_act_itt_bid_close_date IS NOT NULL AND @proposal_type = 856

BEGIN

UPDATE proposals SET sh_act_tech_eval_date = @sh_act_itt_tech_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_commercial_eval_date = @sh_act_itt_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_management_eval_date = @sh_act_itt_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_itt_board_app_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @sh_act_itt_proposal_issue_date IS NOT NULL AND @proposal_type = 856

BEGIN

UPDATE proposals SET sh_act_bid_close_date = @sh_act_itt_bid_close_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_tech_eval_date = @sh_act_itt_tech_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_commercial_eval_date = @sh_act_itt_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_management_eval_date = @sh_act_itt_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_itt_board_app_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @sh_act_itt_bid_list_app_date IS NOT NULL AND @proposal_type = 856

BEGIN

UPDATE proposals SET sh_act_proposal_issue_date = @sh_act_itt_proposal_issue_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_bid_close_date = @sh_act_itt_bid_close_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_tech_eval_date = @sh_act_itt_tech_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_commercial_eval_date = @sh_act_itt_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_management_eval_date = @sh_act_itt_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_itt_board_app_date WHERE proposal_number=@proposal_number;

END

ELSE

IF @sh_act_itt_date IS NOT NULL AND @proposal_type = 856

BEGIN

UPDATE proposals SET sh_act_bid_list_app_date = @sh_act_itt_bid_list_app_date WHERE proposal_number = @proposal_number;

UPDATE proposals SET sh_act_proposal_issue_date = @sh_act_itt_proposal_issue_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_bid_close_date = @sh_act_itt_bid_close_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_tech_eval_date = @sh_act_itt_tech_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_commercial_eval_date = @sh_act_itt_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_management_eval_date = @sh_act_itt_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_itt_board_app_date WHERE proposal_number=@proposal_number;

END

ELSE

UPDATE proposals SET sh_act_rfi_date = @sh_act_itt_date WHERE proposal_number = @proposal_number;

UPDATE proposals SET sh_act_bid_list_app_date = @sh_act_itt_bid_list_app_date WHERE proposal_number = @proposal_number;

UPDATE proposals SET sh_act_proposal_issue_date = @sh_act_itt_proposal_issue_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_bid_close_date = @sh_act_itt_bid_close_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_tech_eval_date = @sh_act_itt_tech_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_commercial_eval_date = @sh_act_itt_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_management_eval_date = @sh_act_itt_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_board_app_date = @sh_act_itt_board_app_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_rfi_date = @sh_act_itt_date WHERE proposal_number = @proposal_number;

UPDATE proposals SET sh_plan_bid_list_app_date = @sh_act_itt_bid_list_app_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_proposal_issue_date = @sh_act_itt_proposal_issue_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_bid_close_date = @sh_act_itt_bid_close_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_tech_eval_date = @sh_act_itt_tech_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_commercial_eval_date = @sh_act_itt_commercial_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_management_eval_date = @sh_act_itt_management_eval_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_plan_board_app_date = @sh_act_itt_board_app_date WHERE proposal_number=@proposal_number;

INSERT INTO sh_procurement_process

(proposal_number, sh_intial_rfi_enter_date, sh_act_rfi_date,sh_act_bid_list_app_date, sh_act_proposal_issue_date,

sh_act_bid_close_date, sh_act_tech_eval_date, sh_act_commercial_eval_date, sh_act_management_eval_date,

sh_act_board_app_date, sh_plan_rfi_date, sh_plan_bid_list_app_date, sh_plan_proposal_issue_date,

sh_plan_bid_close_date, sh_plan_tech_eval_date, sh_plan_commercial_eval_date, sh_plan_management_eval_date,

sh_plan_board_app_date, sh_act_rfi_days, sh_act_bid_list_app_days, sh_act_proposal_issue_days,

sh_act_bid_close_days, sh_act_tech_eval_days, sh_act_commercial_eval_days, sh_act_management_eval_days,

sh_act_board_app_days, sh_plan_rfi_days, sh_plan_bid_list_app_days, sh_plan_proposal_issue_days,

sh_plan_bid_close_days, sh_plan_tech_eval_days, sh_plan_commercial_eval_days, sh_plan_management_eval_days,

sh_plan_board_app_days, proposal_type)

VALUES

(@proposal_number,@date_approved, @sh_act_itt_date, @sh_act_itt_bid_list_app_date, @sh_act_itt_proposal_issue_date,

@sh_act_itt_bid_close_date, @sh_act_itt_tech_eval_date, @sh_act_itt_commercial_eval_date, @sh_act_itt_management_eval_date,

@sh_act_itt_board_app_date, @sh_plan_itt_date, @sh_plan_itt_bid_list_app_date, @sh_plan_itt_proposal_issue_date,

@sh_plan_itt_bid_close_date, @sh_plan_itt_tech_eval_date, @sh_plan_itt_commercial_eval_date, @sh_plan_itt_management_eval_date,

@sh_plan_itt_board_app_date, @sh_act_itt_days, @sh_act_itt_bid_list_app_days, @sh_act_itt_proposal_issue_days,

@sh_act_itt_bid_close_days, @sh_act_itt_tech_eval_days, @sh_act_itt_commercial_eval_days, @sh_act_itt_management_eval_days,

@sh_act_itt_board_app_days, @sh_plan_itt_days, @sh_plan_itt_bid_list_app_days, @sh_plan_itt_proposal_issue_days,

@sh_plan_itt_bid_close_days, @sh_plan_itt_tech_eval_days, @sh_plan_itt_commercial_eval_days, @sh_plan_itt_management_eval_days,

@sh_plan_itt_board_app_days, @proposal_type);

END

GO

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

wow there is so much wrong witht he trigger, i don't know where to start.

it's not designed for more than a single row, so that's one.

every update updates the entire table, no matter whether there was a preexisting date there or not.

there's no guarantee that when you are trying to get your default dates, you even get a date at all.

just one simple example of how every update needs to be rewritten; it might be better to have the GUI generate default dates rather than whatever this was trying to do:



UPDATE proposals

SET sh_act_management_eval_date = {some Date Calculation} --@sh_act_management_eval_date

FROM INSERTED

WHERE proposals.PK = INSERTED.PK

AND INSERTED.sh_act_management_eval_date IS NULL

AND INSERTED.proposal_type = 855

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!

LutzM SSC Guru Points: 107049 More actions · Answer 2

to add what Lowell already stated:

set @sh_act_*, set @sh_plan_* and set @proposal_* logic queries the related table once per column to be selected. This should be done with a single query to those tables. Also, to assign a value to a variable based on a query without using TOP 1 ORDER BY leaves the door wide open for the trigger to fail with "Msg 512, Level 16, State 1 Subquery returned more than 1 value."

To summarize it:Instead of trying to modify those 400+ lines I'd rather prepare to use a "drop trigger" and start from scratch considering

a) there can (and most probably will be) more than one row affected by the update statement causing the trigger to fire,

b) query related tables only once if the only purpose is to assign column values to variables

c) use TOP 1 ORDER BY to prevent the 512 error message.

d) instead of hitting the targe table multiple times without any WHERE clause or at least consider using a CASE statement and a single update path.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

K430 SSC Veteran Points: 264 More actions · Answer 3

Only one record will be updated at a time based on a single insert or update via a front end user app. Each record has 8 dates. The number of days are pulled from a table ( select something_days from days_table.) The dates are calculated based on the number of days for each step in the days table compounded by the previous date field in the sequence and each days field may be different, calculating working days only (M-F).

Date a + days = date b + days = date c

And at any time date b or c may be updated and all dates forward in the sequence will need updated.

So yes on the initial insert all of the date fields for a single record are inserted after that if an update occurs all date fields after the updated field need to be updated with the new calculated dates.

Lowell SSC Guru Points: 323482 More actions · Answer 4

ahh but you are missing the joins to the INSERTED table;

Every UPDATE in that trigger updates every row in the table with the current value calculated...NOT just for the row being inserted...ALL rows. I know that cannot be correct;



UPDATE proposals SET sh_act_board_app_date = @sh_act_itt_board_app_date WHERE proposal_number=@proposal_number;

I'm still picking at the details, for example

you seem to have a setup table [sh_act_board_app_days] used for calculating the proper date limits.

if that table has more than one row, or has any column that is NULL, your logic is bad and needs to be reviewed.

now, if all those dates can be calculated based on a formula like the trigger is doing with the three inserted dates from INSERTED, you should have a VIEW that joins [proposals] and [sh_act_board_app_days], and have all those date columns calculated in the view. that way you eliminate the need for the trigger to do those dates at all.

i would even venture a guess that the table [SH_procurement_process] could be replaced by a view, or maybe the insert can come from that view i mentioned.

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!

K430 SSC Veteran Points: 264 More actions · Answer 5

There is only 1 row in the days table, got that covered. It contains 16 fields based on the 2 different proposal types.

set @proposal_number = (select proposal_number from inserted); limits the insert to the single row being updated.

I don't write a lot of triggers, in my head this trigger should start with check to see if the last date in the sequence is the date that has been updated and work backwards ending when it reached the single date which has been updated.

Thank you for the input and keep it coming please.

K430 SSC Veteran Points: 264 More actions · Answer 6

K430

SSC Veteran

Points: 264

June 29, 2011 at 1:59 pm

#1346219

Also, the application does not recognize views, this data must be in a table which is then viewed by the end user within the application.

Lowell SSC Guru Points: 323482 More actions · Answer 7

ok correct me if i'm wrong, but the business logic is basically saying :

if this date is not null [do stuff]

ELSE

if this other date is not null [do stuff]

etc for validating 10 or so cascading dates as far as NOT NULL/Importantce goes, right?

AT the very end, you have what i suspect is a mistake with the last ELSE:



...

ELSE 

UPDATE proposals SET sh_act_rfi_date = @sh_act_itt_date WHERE proposal_number = @proposal_number;

UPDATE proposals SET sh_act_bid_list_app_date = @sh_act_itt_bid_list_app_date WHERE proposal_number = @proposal_number;

UPDATE proposals SET sh_act_proposal_issue_date = @sh_act_itt_proposal_issue_date WHERE proposal_number=@proposal_number;

UPDATE proposals SET sh_act_bid_close_date = @sh_act_itt_bid_close_date WHERE proposal_number=@proposal_number;

[more updates]

[an insert into sh_procurement_process]

right now,based on the structure of all the other else statemeents, ONE UPDATE wil be conditionally performed, and

ALL the updates at the end ,as well as that insert will be always performed...i think it's supposed to be in a BEGIN...END block but it's missing?

anyway, without addressing the one row logic, or upsetting the rest of the logic, i would streamline what you have slightly to look like this:

I formatted it the way i like it for readability too;



DROP TRIGGER Procurement_Update

GO

/****** Object: Trigger [dbo].[Procurement_Update] Script Date: 06/29/2011 10:19:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[Procurement_Update] ON [dbo].[proposals]

FOR UPDATE

AS

BEGIN

--** DECLARE VARIABLES **

declare

@sh_act_rfi_date                  datetime,

@date_approved                    datetime,

@sh_act_rfi_days                  int,

@proposal_type                    int,

@proposal_number                  char(15),

@sh_plan_rfi_date                 datetime,

@sh_act_bid_list_app_date         datetime,

@sh_act_proposal_issue_date       datetime,

@sh_act_bid_close_date            datetime,

@sh_act_tech_eval_date            datetime,

@sh_act_commercial_eval_date      datetime,

@sh_act_management_eval_date      datetime,

@sh_act_board_app_date            datetime,

@sh_act_bid_list_app_days         int,

@sh_act_proposal_issue_days       int,

@sh_act_bid_close_days            int,

@sh_act_tech_eval_days            int,

@sh_act_commercial_eval_days      int,

@sh_act_management_eval_days      int,

@sh_act_board_app_days            int,

@sh_plan_rfi_days                 int,

@sh_plan_bid_list_app_days        int,

@sh_plan_proposal_issue_days      int,

@sh_plan_bid_close_days           int,

@sh_plan_tech_eval_days           int,

@sh_plan_commercial_eval_days     int,

@sh_plan_management_eval_days     int,

@sh_plan_board_app_days           int,

@sh_initial_rfi_date              datetime,

@sh_plan_bid_list_app_date        datetime,

@sh_plan_proposal_issue_date      datetime,

@sh_plan_bid_close_date           datetime,

@sh_plan_tech_eval_date           datetime,

@sh_plan_commercial_eval_date     datetime,

@sh_plan_management_eval_date     datetime,

@sh_plan_board_app_date           datetime,

@sh_act_itt_days                  int,

@sh_act_itt_bid_list_app_days     int,

@sh_act_itt_proposal_issue_days   int,

@sh_act_itt_bid_close_days        int,

@sh_act_itt_tech_eval_days        int,

@sh_act_itt_commercial_eval_days  int,

@sh_act_itt_management_eval_days  int,

@sh_act_itt_board_app_days        int,

@sh_plan_itt_days                 int,

@sh_plan_itt_bid_list_app_days    int,

@sh_plan_itt_proposal_issue_days  int,

@sh_plan_itt_bid_close_days       int,

@sh_plan_itt_tech_eval_days       int,

@sh_plan_itt_commercial_eval_days int,

@sh_plan_itt_management_eval_days int,

@sh_plan_itt_board_app_days       int,

@sh_act_itt_date                  datetime,

@sh_act_itt_bid_list_app_date     datetime,

@sh_act_itt_proposal_issue_date   datetime,

@sh_act_itt_bid_close_date        datetime,

@sh_act_itt_tech_eval_date        datetime,

@sh_act_itt_commercial_eval_date  datetime,

@sh_act_itt_management_eval_date  datetime,

@sh_act_itt_board_app_date        datetime,

@fromDate                         datetime,

@daysToAdd                        int,

@sh_plan_itt_date                 datetime,

@sh_plan_itt_bid_list_app_date    datetime,

@sh_plan_itt_proposal_issue_date  datetime,

@sh_plan_itt_bid_close_date       datetime,

@sh_plan_itt_tech_eval_date       datetime,

@sh_plan_itt_commercial_eval_date datetime,

@sh_plan_itt_management_eval_date datetime,

@sh_plan_itt_board_app_date       datetime 

--** POPULATE THE VARIABLES **

select @sh_act_rfi_days                  =  sh_act_rfi_days,

       @sh_act_bid_list_app_days         =  sh_act_bid_list_app_days,

       @sh_act_proposal_issue_days       =  sh_act_proposal_issue_days,

       @sh_act_bid_close_days            =  sh_act_bid_close_days,

       @sh_act_tech_eval_days            =  sh_act_tech_eval_days,

       @sh_act_commercial_eval_days      =  sh_act_commercial_eval_days,

       @sh_act_management_eval_days      =  sh_act_management_eval_days,

       @sh_act_board_app_days            =  sh_act_board_app_days,

       @sh_plan_rfi_days                 =  sh_act_rfi_days,

       @sh_plan_bid_list_app_days        =  sh_act_bid_list_app_days,

       @sh_plan_proposal_issue_days      =  sh_act_proposal_issue_days,

       @sh_plan_bid_close_days           =  sh_act_bid_close_days,

       @sh_plan_tech_eval_days           =  sh_act_tech_eval_days,

       @sh_plan_commercial_eval_days     =  sh_act_commercial_eval_days,

       @sh_plan_management_eval_days     =  sh_act_management_eval_days,

       @sh_plan_board_app_days           =  sh_act_board_app_days, 

       @sh_act_itt_days                  = sh_act_itt_days,

       @sh_act_itt_bid_list_app_days     = sh_act_itt_bid_list_app_days,

       @sh_act_itt_proposal_issue_days   = sh_act_itt_proposal_issue_days,

       @sh_act_itt_bid_close_days        = sh_act_itt_bid_close_days,

       @sh_act_itt_tech_eval_days        = sh_act_itt_tech_eval_days,

       @sh_act_itt_commercial_eval_days  = sh_act_itt_commercial_eval_days,

       @sh_act_itt_management_eval_days  = sh_act_itt_management_eval_days,

       @sh_act_itt_board_app_days        = sh_act_itt_board_app_days,

       @sh_plan_itt_days                 = sh_act_itt_days,

       @sh_plan_itt_bid_list_app_days = sh_act_itt_bid_list_app_days,

       @sh_plan_itt_proposal_issue_days  = sh_act_itt_proposal_issue_days,

       @sh_plan_itt_bid_close_days       = sh_act_itt_bid_close_days,

       @sh_plan_itt_tech_eval_days       = sh_act_itt_tech_eval_days,

       @sh_plan_itt_commercial_eval_days = sh_act_itt_commercial_eval_days,

       @sh_plan_itt_management_eval_days = sh_act_itt_management_eval_days,

       @sh_plan_itt_board_app_days       = sh_act_itt_board_app_days

from sh_procurement_days;

select @date_approved   = date_approved,

       @proposal_number = proposal_number,

       @proposal_type   = proposal_type

from inserted;

select @sh_act_rfi_date             = DATEADD (day, CONVERT (INT, sh_act_rfi_days             /5)*7 + sh_act_rfi_days             %5, @date_approved),

       @sh_act_bid_list_app_date    = DATEADD (day, CONVERT (INT, sh_act_bid_list_app_days    /5)*7 + sh_act_bid_list_app_days    %5, @sh_act_rfi_date),

       @sh_act_proposal_issue_date  = DATEADD (day, CONVERT (INT, sh_act_proposal_issue_days  /5)*7 + sh_act_proposal_issue_days  %5,@sh_act_bid_list_app_date),

       @sh_act_bid_close_date       = DATEADD (day, CONVERT (INT, sh_act_bid_close_days       /5)*7 + sh_act_bid_close_days       %5, @sh_act_proposal_issue_date),

       @sh_act_tech_eval_date       = DATEADD (day, CONVERT (INT, sh_act_tech_eval_days       /5)*7 + sh_act_tech_eval_days       %5, @sh_act_bid_close_date),

       @sh_act_commercial_eval_date = DATEADD (day, CONVERT (INT, sh_act_commercial_eval_days /5)*7 + sh_act_commercial_eval_days %5, @sh_act_tech_eval_date),

       @sh_act_management_eval_date = DATEADD (day, CONVERT (INT, sh_act_management_eval_days /5)*7 + sh_act_management_eval_days %5, @sh_act_commercial_eval_date),

       @sh_act_board_app_date       = DATEADD (day, CONVERT (INT, sh_act_board_app_days       /5)*7 + sh_act_board_app_days       %5, @sh_act_management_eval_date)

from sh_procurement_days;

select @sh_plan_rfi_date             = @sh_act_rfi_date,

       @sh_plan_bid_list_app_date    = @sh_act_bid_list_app_date,

       @sh_plan_proposal_issue_date  = @sh_act_proposal_issue_date,

       @sh_plan_bid_close_date       = @sh_act_bid_close_date,

       @sh_plan_tech_eval_date       = @sh_act_tech_eval_date,

       @sh_plan_commercial_eval_date = @sh_act_commercial_eval_date,

       @sh_plan_management_eval_date = @sh_act_management_eval_date,

       @sh_plan_board_app_date       = @sh_act_board_app_date

select @sh_act_itt_date                 = DATEADD (day, CONVERT (INT, sh_act_itt_days                 /5)*7 + sh_act_itt_days                 %5, @date_approved),

       @sh_act_itt_bid_list_app_date    = DATEADD (day, CONVERT (INT, sh_act_itt_bid_list_app_days    /5)*7 + sh_act_itt_bid_list_app_days    %5, @sh_act_itt_date),

       @sh_act_itt_proposal_issue_date  = DATEADD (day, CONVERT (INT, sh_act_itt_proposal_issue_days  /5)*7 + sh_act_itt_proposal_issue_days  %5,@sh_act_itt_bid_list_app_date),

       @sh_act_itt_bid_close_date       = DATEADD (day, CONVERT (INT, sh_act_itt_bid_close_days       /5)*7 + sh_act_itt_bid_close_days       %5, @sh_act_itt_proposal_issue_date),

       @sh_act_itt_tech_eval_date       = DATEADD (day, CONVERT (INT, sh_act_itt_tech_eval_days       /5)*7 + sh_act_itt_tech_eval_days       %5, @sh_act_itt_bid_close_date),

       @sh_act_itt_commercial_eval_date = DATEADD (day, CONVERT (INT, sh_act_itt_commercial_eval_days /5)*7 + sh_act_itt_commercial_eval_days %5, @sh_act_itt_tech_eval_date),

       @sh_act_itt_management_eval_date = DATEADD (day, CONVERT (INT, sh_act_itt_management_eval_days /5)*7 + sh_act_itt_management_eval_days %5,@sh_act_itt_commercial_eval_date),

       @sh_act_itt_board_app_date       = DATEADD (day, CONVERT (INT, sh_act_itt_board_app_days       /5)*7 + sh_act_itt_board_app_days       %5, @sh_act_itt_management_eval_date)

from sh_procurement_days;

select @sh_plan_itt_date                 = @sh_act_itt_date,

       @sh_plan_itt_bid_list_app_date    = @sh_act_itt_bid_list_app_date,

       @sh_plan_itt_proposal_issue_date  = @sh_act_itt_proposal_issue_date,

       @sh_plan_itt_bid_close_date       = @sh_act_itt_bid_close_date,

       @sh_plan_itt_tech_eval_date       = @sh_act_itt_tech_eval_date,

       @sh_plan_itt_commercial_eval_date = @sh_act_itt_commercial_eval_date,

       @sh_plan_itt_management_eval_date = @sh_act_itt_management_eval_date,

       @sh_plan_itt_board_app_date       = @sh_act_itt_board_app_date

IF @sh_act_commercial_eval_date IS NOT NULL AND @proposal_type = 855

  BEGIN

    UPDATE proposals

    SET sh_act_management_eval_date = @sh_act_management_eval_date,

        sh_act_board_app_date       = @sh_act_management_eval_date

    WHERE proposal_number=@proposal_number;

  END 

ELSE

  IF @sh_act_tech_eval_date IS NOT NULL AND @proposal_type = 855 --RFQ

  BEGIN

    UPDATE proposals

    SET sh_act_commercial_eval_date = @sh_act_commercial_eval_date,

      sh_act_management_eval_date = @sh_act_management_eval_date,

      sh_act_board_app_date = @sh_act_management_eval_date

    WHERE proposal_number=@proposal_number;

  END 

ELSE

  IF @sh_act_bid_close_date IS NOT NULL AND @proposal_type = 855

  BEGIN

    UPDATE proposals

    SET sh_act_tech_eval_date       = @sh_act_tech_eval_date,

        sh_act_commercial_eval_date = @sh_act_commercial_eval_date,

        sh_act_management_eval_date = @sh_act_management_eval_date,

        sh_act_board_app_date       = @sh_act_management_eval_date

    WHERE proposal_number=@proposal_number;

END 

ELSE

  IF @sh_act_proposal_issue_date IS NOT NULL AND @proposal_type = 855

  BEGIN

    UPDATE proposals

    SET sh_act_bid_close_date       = @sh_act_bid_close_date,

        sh_act_tech_eval_date       = @sh_act_tech_eval_date,

        sh_act_commercial_eval_date = @sh_act_commercial_eval_date,

        sh_act_management_eval_date = @sh_act_management_eval_date,

        sh_act_board_app_date       = @sh_act_management_eval_date

    WHERE proposal_number=@proposal_number;

  END

ELSE

  IF @sh_act_bid_list_app_date IS NOT NULL AND @proposal_type = 855

  BEGIN

    UPDATE proposals

    SET sh_act_proposal_issue_date  = @sh_act_proposal_issue_date,

        sh_act_bid_close_date       = @sh_act_bid_close_date,

        sh_act_tech_eval_date       = @sh_act_tech_eval_date,

        sh_act_commercial_eval_date = @sh_act_commercial_eval_date,

        sh_act_management_eval_date = @sh_act_management_eval_date,

        sh_act_board_app_date       = @sh_act_management_eval_date

    WHERE proposal_number=@proposal_number;

  END

ELSE

  IF @sh_act_rfi_date IS NOT NULL AND @proposal_type = 855

  BEGIN

    UPDATE proposals

    SET sh_act_bid_list_app_date    = @sh_act_bid_list_app_date,

        sh_act_proposal_issue_date  = @sh_act_proposal_issue_date,

        sh_act_bid_close_date       = @sh_act_bid_close_date,

        sh_act_tech_eval_date       = @sh_act_tech_eval_date,

        sh_act_commercial_eval_date = @sh_act_commercial_eval_date,

        sh_act_management_eval_date = @sh_act_management_eval_date,

        sh_act_board_app_date       = @sh_act_management_eval_date

    WHERE proposal_number=@proposal_number;

  END

ELSE

  IF @date_approved IS NOT NULL AND @proposal_type = 855

  BEGIN

    UPDATE proposals

    SET sh_act_rfi_date              = @sh_act_rfi_date,

        sh_act_bid_list_app_date     = @sh_act_bid_list_app_date,

        sh_act_proposal_issue_date   = @sh_act_proposal_issue_date,

        sh_act_bid_close_date        = @sh_act_bid_close_date,

        sh_act_tech_eval_date        = @sh_act_tech_eval_date,

        sh_act_commercial_eval_date  = @sh_act_commercial_eval_date,

        sh_act_management_eval_date  = @sh_act_management_eval_date,

        sh_act_board_app_date        = @sh_act_management_eval_date,

        sh_plan_rfi_date             = @sh_plan_rfi_date,

        sh_plan_bid_list_app_date    = @sh_plan_bid_list_app_date,

        sh_plan_proposal_issue_date  = @sh_plan_proposal_issue_date,

        sh_plan_bid_close_date       = @sh_plan_bid_close_date,

        sh_plan_tech_eval_date       = @sh_plan_tech_eval_date,

        sh_plan_commercial_eval_date = @sh_plan_commercial_eval_date,

        sh_plan_management_eval_date = @sh_plan_management_eval_date,

        sh_plan_board_app_date       = @sh_plan_management_eval_date

    WHERE proposal_number=@proposal_number;

  END

ELSE

  IF @date_approved IS NOT NULL AND @proposal_type = 855

  BEGIN

    INSERT INTO SH_procurement_process

      (proposal_number, sh_intial_rfi_enter_date, sh_act_rfi_date,sh_act_bid_list_app_date, sh_act_proposal_issue_date, sh_act_bid_close_date,

       sh_act_tech_eval_date, sh_act_commercial_eval_date, sh_act_management_eval_date,

       sh_plan_rfi_date, sh_plan_bid_list_app_date, sh_plan_proposal_issue_date, sh_plan_bid_close_date,

       sh_plan_tech_eval_date, sh_plan_commercial_eval_date, sh_plan_management_eval_date,

       sh_act_rfi_days, sh_act_bid_list_app_days, sh_act_proposal_issue_days,

       sh_act_bid_close_days, sh_act_tech_eval_days, sh_act_commercial_eval_days, sh_act_management_eval_days,

       sh_plan_rfi_days, sh_plan_bid_list_app_days, sh_plan_proposal_issue_days,

       sh_plan_bid_close_days, sh_plan_tech_eval_days, sh_plan_commercial_eval_days, sh_plan_management_eval_days, proposal_type)

    VALUES (

       @proposal_number,@date_approved, @sh_act_rfi_date, @sh_act_bid_list_app_date, @sh_act_proposal_issue_date, @sh_act_bid_close_date,

       @sh_act_tech_eval_date, @sh_act_commercial_eval_date, @sh_act_management_eval_date,

       @sh_plan_rfi_date, @sh_plan_bid_list_app_date, @sh_plan_proposal_issue_date, @sh_plan_bid_close_date,

       @sh_plan_tech_eval_date, @sh_plan_commercial_eval_date, @sh_plan_management_eval_date,

       @sh_act_rfi_days, @sh_act_bid_list_app_days, @sh_act_proposal_issue_days,

       @sh_act_bid_close_days, @sh_act_tech_eval_days, @sh_act_commercial_eval_days, @sh_act_management_eval_days,

       @sh_plan_rfi_days, @sh_plan_bid_list_app_days, @sh_plan_proposal_issue_days,

       @sh_plan_bid_close_days, @sh_plan_tech_eval_days, @sh_plan_commercial_eval_days, @sh_plan_management_eval_days, @proposal_type);

  END

ELSE

  IF @sh_act_itt_management_eval_date IS NOT NULL AND @proposal_type = 856 --ITT

  BEGIN

    UPDATE proposals

    SET sh_act_board_app_date = @sh_act_itt_board_app_date

    WHERE proposal_number=@proposal_number;

  END

ELSE

  IF @sh_act_itt_commercial_eval_date IS NOT NULL AND @proposal_type = 856

  BEGIN

    UPDATE proposals

    SET sh_act_management_eval_date = @sh_act_itt_management_eval_date,

        sh_act_board_app_date       = @sh_act_itt_board_app_date

   WHERE proposal_number=@proposal_number;

  END

ELSE

  IF @sh_act_itt_tech_eval_date IS NOT NULL AND @proposal_type = 856

  BEGIN

    UPDATE proposals

    SET sh_act_commercial_eval_date = @sh_act_itt_commercial_eval_date,

        sh_act_management_eval_date = @sh_act_itt_management_eval_date,

        sh_act_board_app_date       = @sh_act_itt_board_app_date

    WHERE proposal_number=@proposal_number;

  END

ELSE

  IF @sh_act_itt_bid_close_date IS NOT NULL AND @proposal_type = 856

  BEGIN

    UPDATE proposals

    SET sh_act_tech_eval_date       = @sh_act_itt_tech_eval_date,

        sh_act_commercial_eval_date = @sh_act_itt_commercial_eval_date,

        sh_act_management_eval_date = @sh_act_itt_management_eval_date,

        sh_act_board_app_date       = @sh_act_itt_board_app_date

    WHERE proposal_number=@proposal_number;

  END

ELSE

  IF @sh_act_itt_proposal_issue_date IS NOT NULL AND @proposal_type = 856

  BEGIN

    UPDATE proposals

    SET sh_act_bid_close_date       = @sh_act_itt_bid_close_date,

        sh_act_tech_eval_date       = @sh_act_itt_tech_eval_date,

        sh_act_commercial_eval_date = @sh_act_itt_commercial_eval_date,

        sh_act_management_eval_date = @sh_act_itt_management_eval_date,

        sh_act_board_app_date       = @sh_act_itt_board_app_date

    WHERE proposal_number=@proposal_number;

  END

ELSE

  IF @sh_act_itt_bid_list_app_date IS NOT NULL AND @proposal_type = 856

  BEGIN

    UPDATE proposals

    SET sh_act_proposal_issue_date = @sh_act_itt_proposal_issue_date,

        sh_act_bid_close_date = @sh_act_itt_bid_close_date,

        sh_act_tech_eval_date = @sh_act_itt_tech_eval_date,

        sh_act_commercial_eval_date = @sh_act_itt_commercial_eval_date,

        sh_act_management_eval_date = @sh_act_itt_management_eval_date,

        sh_act_board_app_date = @sh_act_itt_board_app_date

    WHERE proposal_number=@proposal_number;

  END 

ELSE

  IF @sh_act_itt_date IS NOT NULL AND @proposal_type = 856

  BEGIN

    UPDATE proposals

    SET sh_act_bid_list_app_date = @sh_act_itt_bid_list_app_date,

        sh_act_proposal_issue_date = @sh_act_itt_proposal_issue_date,

        sh_act_bid_close_date = @sh_act_itt_bid_close_date,

        sh_act_tech_eval_date = @sh_act_itt_tech_eval_date,

        sh_act_commercial_eval_date = @sh_act_itt_commercial_eval_date,

        sh_act_management_eval_date = @sh_act_itt_management_eval_date,

        sh_act_board_app_date = @sh_act_itt_board_app_date

WHERE proposal_number=@proposal_number;

  END

ELSE

  BEGIN

    UPDATE proposals

    SET sh_act_rfi_date              = @sh_act_itt_date,

        sh_act_bid_list_app_date     = @sh_act_itt_bid_list_app_date,

        sh_act_proposal_issue_date   = @sh_act_itt_proposal_issue_date,

        sh_act_bid_close_date        = @sh_act_itt_bid_close_date,

        sh_act_tech_eval_date        = @sh_act_itt_tech_eval_date,

        sh_act_commercial_eval_date  = @sh_act_itt_commercial_eval_date,

        sh_act_management_eval_date  = @sh_act_itt_management_eval_date,

        sh_act_board_app_date        = @sh_act_itt_board_app_date,

        sh_plan_rfi_date             = @sh_act_itt_date,

        sh_plan_bid_list_app_date    = @sh_act_itt_bid_list_app_date,

        sh_plan_proposal_issue_date  = @sh_act_itt_proposal_issue_date,

        sh_plan_bid_close_date       = @sh_act_itt_bid_close_date,

        sh_plan_tech_eval_date       = @sh_act_itt_tech_eval_date,

        sh_plan_commercial_eval_date = @sh_act_itt_commercial_eval_date,

        sh_plan_management_eval_date = @sh_act_itt_management_eval_date,

        sh_plan_board_app_date       = @sh_act_itt_board_app_date

      WHERE proposal_number=@proposal_number;

--this block

    INSERT INTO sh_procurement_process

      (proposal_number, sh_intial_rfi_enter_date, sh_act_rfi_date,sh_act_bid_list_app_date, sh_act_proposal_issue_date,

      sh_act_bid_close_date, sh_act_tech_eval_date, sh_act_commercial_eval_date, sh_act_management_eval_date,

      sh_act_board_app_date, sh_plan_rfi_date, sh_plan_bid_list_app_date, sh_plan_proposal_issue_date,

      sh_plan_bid_close_date, sh_plan_tech_eval_date, sh_plan_commercial_eval_date, sh_plan_management_eval_date,

      sh_plan_board_app_date, sh_act_rfi_days, sh_act_bid_list_app_days, sh_act_proposal_issue_days,

      sh_act_bid_close_days, sh_act_tech_eval_days, sh_act_commercial_eval_days, sh_act_management_eval_days,

      sh_act_board_app_days, sh_plan_rfi_days, sh_plan_bid_list_app_days, sh_plan_proposal_issue_days,

      sh_plan_bid_close_days, sh_plan_tech_eval_days, sh_plan_commercial_eval_days, sh_plan_management_eval_days,

      sh_plan_board_app_days, proposal_type)

    VALUES

      (@proposal_number,@date_approved, @sh_act_itt_date, @sh_act_itt_bid_list_app_date, @sh_act_itt_proposal_issue_date,

      @sh_act_itt_bid_close_date, @sh_act_itt_tech_eval_date, @sh_act_itt_commercial_eval_date, @sh_act_itt_management_eval_date,

      @sh_act_itt_board_app_date, @sh_plan_itt_date, @sh_plan_itt_bid_list_app_date, @sh_plan_itt_proposal_issue_date,

      @sh_plan_itt_bid_close_date, @sh_plan_itt_tech_eval_date, @sh_plan_itt_commercial_eval_date, @sh_plan_itt_management_eval_date,

      @sh_plan_itt_board_app_date, @sh_act_itt_days, @sh_act_itt_bid_list_app_days, @sh_act_itt_proposal_issue_days,

      @sh_act_itt_bid_close_days, @sh_act_itt_tech_eval_days, @sh_act_itt_commercial_eval_days, @sh_act_itt_management_eval_days,

      @sh_act_itt_board_app_days, @sh_plan_itt_days, @sh_plan_itt_bid_list_app_days, @sh_plan_itt_proposal_issue_days,

      @sh_plan_itt_bid_close_days, @sh_plan_itt_tech_eval_days, @sh_plan_itt_commercial_eval_days, @sh_plan_itt_management_eval_days,

      @sh_plan_itt_board_app_days, @proposal_type);

  END --ELSE

END -- PROC

GO

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!

K430 SSC Veteran Points: 264 More actions · Answer 8

Lowell,

Thank you but this only does what my original code does. It populates the last date field only on an initial insert and doesn't update anything.

SQL Server 2008 does not accept ELSEIF as a single command, it is ELSE IF

At least Im not feeling as frustrated and incompetent as I did a few hours ago. (Sorry)

UGHHHH

Lowell SSC Guru Points: 323482 More actions · Answer 9

i just noticed that and fixed it; copy my code example again...i edited it to be syntactically correct, but with that code block at the end slightly changed to have the ELSE contain a BEGIN...END

ok i'm really familiar with the proc and logic now...what is it not doing correctly? i must have skimmed over that part too quickly.

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!

K430 SSC Veteran Points: 264 More actions · Answer 10

It only populates the last 2 dates for 855 and the last date for 856 for a new record and updates nothing for existing records.

* 855 has the same date for the last 2 date fields which explains the 2 vs 1 for 856

It calculats the dates correct but it is like it is ignoring the IS NOT NULL in the statements before the initial (full) insert.