Populate Date Using SQL Trigger

My company produces orders using Sage 100 and CRM. The opportunity follows a workflow right up until it is finished, but stops before the customer is billed. Billing is done exclusively in Sage. We have a SQL trigger set up that will change the stage and status of the CRM opportunity when the order is completed in Sage. What I'm trying to do is to the trigger to make the Closed date populate.

This is the current script for the order trigger:

USE [CRM]
GO
/****** Object:  Trigger [dbo].[UpdateForeOrder]    Script Date: 1/7/2021 10:55:51 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UpdateForeOrder] 
ON [dbo].[Orders]
for UPDATE 
AS 
BEGIN 
 
SET NOCOUNT ON; 



declare @OppoId int 
declare @OrderId int	
declare @Quote money
declare @PO nvarchar(30)
declare @CurrPo nvarchar(30)
declare @TotalOrders money
declare @Status nvarchar(30)
declare @OppoStatus nvarchar(30)
declare @OppoStage nvarchar (30)



select @orderId=Orde_OrderQuoteID, @Status = orde_status,  @OppoId=orde_opportunityid, @Quote=orde_grossamt from inserted 



------Get Order Status----------
if @Status = 'Active'
Select @OppoStage = 'Order', @OppoStatus = 'In Progress'
if @Status = 'Completed'
Select @OppoStage = 'Invoiced', @OppoStatus = 'Closed'
if @Status = 'Inactive'
Select @OppoStage = 'Declined', @OppoStatus = 'Closed'


select @TotalOrders = sum(Orde_grossamt) from Orders where orde_opportunityid = @OppoId --and orde_rollup = 'Y'
Update Opportunity set oppo_TotalOrders = @TotalOrders where [email protected]
update opportunity set [email protected] where [email protected]

update opportunity set [email protected], Oppo_Status = @OppoStatus where [email protected]

END

My knowledge of SQL is almost none and I don't have the luxury of a test environment. Would anyone mind letting me know if the following changes might work? Or do you have any ideas of what would?

New local variable:

declare @OppoClosed datetime

Option 1:

if @Status = 'Active'
Select @OppoStage = 'Order', @OppoStatus = 'In Progress'
if @Status = 'Completed'
Select @OppoStage = 'Invoiced', @OppoStatus = 'Closed', @OppoClosed GETDATE()
if @Status = 'Inactive'
Select @OppoStage = 'Declined', @OppoStatus = 'Closed'

Option 2:

if @Status = 'Active'
Select @OppoStage = 'Order', @OppoStatus = 'In Progress'
if @Status = 'Completed'
Select @OppoStage = 'Invoiced', @OppoStatus = 'Closed'
if @Status = 'Inactive'
Select @OppoStage = 'Declined', @OppoStatus = 'Closed'
if @OppoStage = 'Invoiced'
Set @OppoClosed = GETDATE()

Parents
  • Hi Kirsten

    How about the following? 

    (Taken from the DECLAREs as I have tweak your Quote/Total to be Numeric rather than money, feel free to switch them back if you want) and removed two DECLAREs


    declare @OppoId int
    declare @OrderId int
    declare @Quote Numeric(24,2)
    declare @PO nvarchar(30)
    declare @CurrPo nvarchar(30)
    declare @TotalOrders Numeric(24,2)
    declare @Status nvarchar(30)

    select
    @orderId=Orde_OrderQuoteID,
    @Status = orde_status,
    @OppoId=orde_opportunityid,
    @Quote=orde_grossamt
    from inserted

    /* Removed the multiple IFs statements, this will come into play further down /*

    select @TotalOrders = sum(Orde_grossamt) from Orders where orde_opportunityid = @OppoId --and orde_rollup = 'Y'

    BEGIN
    /* Made it all one UPDATE, so you're not updating the table multiple times per update, so doing all the updates at once /*

    UPDATE Opportunity
    SET oppo_totalorders = @Totalorders,
    oppo_forecast = @TotalOrders,

    /* Using CASE WHEN 'this' THEN 'do something' ELSE 'do something else', to handle the 'IF' contidtions you had earlier /*
    oppo_stage = CASE WHEN @Status = 'Active' THEN 'Order'
        WHEN @Status = 'Completed' THEN 'Invoiced'
        WHEN @Status = 'Inactive' THEN 'Declined' ELSE oppo_stage END,
    oppo_status = CASE WHEN @Status = 'Active' THEN 'In Progress'
        WHEN @Status IN ('Completed','Inactive') THEN 'Closed' ELSE oppo_status END,

    /* Populating the Close date when the Oppo is Completed, but also if it is declinded, thought you might want it then as well, but if there is already a date in there then leave it alone */
    oppo_closed = CASE WHEN oppo_closed IS NULL AND @Status IN ('Completed','Inactive') THEN GETDATE() ELSE oppo_closed END
    WHERE oppo_opportunityid = @OppoID
    END

Reply
  • Hi Kirsten

    How about the following? 

    (Taken from the DECLAREs as I have tweak your Quote/Total to be Numeric rather than money, feel free to switch them back if you want) and removed two DECLAREs


    declare @OppoId int
    declare @OrderId int
    declare @Quote Numeric(24,2)
    declare @PO nvarchar(30)
    declare @CurrPo nvarchar(30)
    declare @TotalOrders Numeric(24,2)
    declare @Status nvarchar(30)

    select
    @orderId=Orde_OrderQuoteID,
    @Status = orde_status,
    @OppoId=orde_opportunityid,
    @Quote=orde_grossamt
    from inserted

    /* Removed the multiple IFs statements, this will come into play further down /*

    select @TotalOrders = sum(Orde_grossamt) from Orders where orde_opportunityid = @OppoId --and orde_rollup = 'Y'

    BEGIN
    /* Made it all one UPDATE, so you're not updating the table multiple times per update, so doing all the updates at once /*

    UPDATE Opportunity
    SET oppo_totalorders = @Totalorders,
    oppo_forecast = @TotalOrders,

    /* Using CASE WHEN 'this' THEN 'do something' ELSE 'do something else', to handle the 'IF' contidtions you had earlier /*
    oppo_stage = CASE WHEN @Status = 'Active' THEN 'Order'
        WHEN @Status = 'Completed' THEN 'Invoiced'
        WHEN @Status = 'Inactive' THEN 'Declined' ELSE oppo_stage END,
    oppo_status = CASE WHEN @Status = 'Active' THEN 'In Progress'
        WHEN @Status IN ('Completed','Inactive') THEN 'Closed' ELSE oppo_status END,

    /* Populating the Close date when the Oppo is Completed, but also if it is declinded, thought you might want it then as well, but if there is already a date in there then leave it alone */
    oppo_closed = CASE WHEN oppo_closed IS NULL AND @Status IN ('Completed','Inactive') THEN GETDATE() ELSE oppo_closed END
    WHERE oppo_opportunityid = @OppoID
    END

Children
No Data