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 oppo_opportunityid=@OppoId
update opportunity set oppo_forecast=@TotalOrders where oppo_opportunityid=@OppoId

update opportunity set Oppo_Stage=@OppoStage, Oppo_Status = @OppoStatus where oppo_opportunityid=@OppoId

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()