USE [Dev]
GO
/****** Object:  Trigger [dbo].[tr_route_cards_update]    Script Date: 06/02/2023 09:03:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_route_cards_update] ON [dbo].[route_cards] 
AFTER UPDATE 
AS
BEGIN
    IF UPDATE(part_num) OR UPDATE(issue) OR UPDATE(batch_qty) OR UPDATE(qty_finished) OR UPDATE(qty_rejected) OR 
        UPDATE(route_status) OR UPDATE(inspected) OR UPDATE(sign_off)
    BEGIN
        DECLARE @old_qty_rejected NUMERIC(19,7);
        DECLARE @new_qty_finished NUMERIC(19,7);
        DECLARE @new_qty_rejected NUMERIC(19,7);
        DECLARE @new_batch_qty INTEGER;
        DECLARE @new_part_num NVARCHAR(36);
        DECLARE @new_part_issue NVARCHAR(10);
        DECLARE @route_date DATETIME;
        DECLARE @contract_no INTEGER;
        DECLARE @top_level_part NVARCHAR(36);
        DECLARE @top_level_issue NVARCHAR(10);

        SELECT    @new_qty_finished = COALESCE(qty_finished, 0)
                ,@new_batch_qty = batch_qty
                ,@new_part_num = part_num
                ,@new_part_issue = issue
                ,@top_level_part = top_level_part
                ,@top_level_issue = top_level_issue
                ,@contract_no = contract_no
        FROM inserted;    
        SET @new_qty_rejected = 0; 

        -- part last order details update
        UPDATE    parts 
        SET        last_order_date = GETDATE()
                ,last_order_qty = @new_qty_finished - @new_qty_rejected
                ,lead_time = DATEDIFF(dd,  @route_date, GETDATE())
        WHERE    parts.part_num = @new_part_num 
        AND        parts.issue = @new_part_issue;

        IF @contract_no is not null AND @contract_no > 0 AND @top_level_part = @new_part_num AND @top_level_issue = @new_part_issue
            execute sp_contract_update_qty_info @contract_no;
    END;
END;

USE [Dev]
GO
/****** Object:  Trigger [dbo].[tr_route_cards_update]    Script Date: 06/02/2023 09:03:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_route_cards_update] ON [dbo].[route_cards] 
AFTER UPDATE 
AS
BEGIN
    IF UPDATE(part_num) OR UPDATE(issue) OR UPDATE(batch_qty) OR UPDATE(qty_finished) OR UPDATE(qty_rejected) OR 
        UPDATE(route_status) OR UPDATE(inspected) OR UPDATE(sign_off)
    BEGIN
        DECLARE @old_qty_rejected NUMERIC(19,7);
        DECLARE @new_qty_finished NUMERIC(19,7);
        DECLARE @new_qty_rejected NUMERIC(19,7);
        DECLARE @new_batch_qty INTEGER;
        DECLARE @new_part_num NVARCHAR(36);
        DECLARE @new_part_issue NVARCHAR(10);
        DECLARE @route_date DATETIME;
        DECLARE @contract_no INTEGER;
        DECLARE @top_level_part NVARCHAR(36);
        DECLARE @top_level_issue NVARCHAR(10);

        SELECT    @new_qty_finished = COALESCE(qty_finished, 0)
                ,@new_batch_qty = batch_qty
                ,@new_part_num = part_num
                ,@new_part_issue = issue
                ,@top_level_part = top_level_part
                ,@top_level_issue = top_level_issue
                ,@contract_no = contract_no
        FROM inserted;    
        SET @new_qty_rejected = 0; 

        -- part last order details update
        UPDATE    parts 
        SET        last_order_date = GETDATE()
                ,last_order_qty = @new_qty_finished - @new_qty_rejected
                ,lead_time = DATEDIFF(dd,  @route_date, GETDATE())
        WHERE    parts.part_num = @new_part_num 
        AND        parts.issue = @new_part_issue;

        IF @contract_no is not null AND @contract_no > 0 AND @top_level_part = @new_part_num AND @top_level_issue = @new_part_issue
            execute sp_contract_update_qty_info @contract_no;
    END;
END;