locked
Question about database trigger (CRM 4.0) RRS feed

  • Question

  • Hello,

    First of all I know what I'm about to ask is highly unsupported and shouldn't be done on a normal basis but it will save me so much development I'm willing to give it a try:

    I'm  creating a "for insert" trigger on the InvoiceExtensionBase table and when an Invoice gets created I want to get the id from the inserted record and update a custom field on the same InvoiceExtensionBase table.

    That value comes from the InvoiceBase Table:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER TRIGGER [dbo].[InsertSetTotalAmountBaseCurrency] ON [dbo].[InvoiceExtensionBase] 
    FOR INSERT
    AS 
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE @taxaconversao DECIMAL(12,3)
    	DECLARE @idsalesorder NVARCHAR(200)
    	declare @idfactura uniqueidentifier
    	SET @idfactura = (SELECT InvoiceId from inserted)
    	declare @cash DECIMAL(12,3)
    	SET @cash =(SELECT TotalAmount FROM InvoiceBase where InvoiceId = @idfactura)
    	
    	UPDATE [InvoiceExtensionBase]
    	SET ESRI_totaldatadeencomenda = @cash
    	where InvoiceId = @idfactura
    	
    
    END
    GO
    

    Now, the question is the TOTALAMOUNT is always 0 for some reason although that's not the value reflected on the InvoiceBase table.

    If I set the @cash variable to any other value it gets updated correctly so I'm wondering what the problem is.

    I'm assuming InvoiceBase is already created by the time InvoiceExtensionBase inserts the record so the value should be there.

    Any ideas?

    Appreciate the help.

    Wednesday, April 16, 2014 10:03 AM

Answers

  • Okay, I understand your point and I can think of two options at this stage

    option 1 : Supported one 

    assuming this is CRM 4 system one supported way to do it using custom workflow as mentioned in steps below...

    1. you need custom workflow which do add/divide/subtract for you and give result. apparently this workflow is already provided by Microsoft for CRM as accelerators at following link

    http://crmaccelerators.codeplex.com/releases/view/26691

    since this is custom workflow it is really less risk since it will only get called in workflow which uses them and it will not impact existing code as well

    2. once you have this custom workflow in your system, you need to create workflow against Invoice post create 

    3. in this workflow you can call new custom workflow function Match and pass relevant parameters..

       since this workflow is against invoice you can retrieve fields from it's parent sales order and pass this as parameter 

    4. once you get result you can just update relevant field in this workflow using update step

     

    Option 2 : Unsupported  one

    you can go ahead with trigger option.

    your current trigger does not specify that it is after trigger and also you can even check trigger nested level and also disable recursive trigger as shown in post below..

    http://stackoverflow.com/questions/5341584/sql-server-after-insert-trigger-update-another-column-in-the-same-table

    Hope this helps


    MayankP
    My Blog
    Follow Me on Twitter

    • Marked as answer by Agenteusa Monday, April 21, 2014 5:20 PM
    Wednesday, April 16, 2014 6:01 PM

All replies

  • Only now I noticed, I can't be doing this or else I'll create an endless loop (as I will need for update too).

    Somehow I'll need to do the trigger in InvoiceBase instead.

    I'm just unsure if the InvoiceExtensionBase already has the record when I insert an invoice and the trigger fires.

    Any clue?

    Wednesday, April 16, 2014 3:29 PM
  • you can easily achieve this by doing supported workflow or plug in in CRM 4 environment.

    why do you want to go for unsupported changes? it seems to me you just want total amount field value is copied to ESRI_totaldatadeencomenda field.


    MayankP
    My Blog
    Follow Me on Twitter

    Wednesday, April 16, 2014 4:16 PM
  • Not exactly since I need to make a calculation with a field that is in the salesorder entity. I was only presenting the first part of the problem.

    I don't think I can do calculations with 2 different fields with a workflow and with the plugin I risk causing deadlocks since I already have lots of plugins and business logic triggering from the Invoice and InnvoiceDetail entities.

    Plus I use lots of custom fields which require extensive handling and have to be manually updated everytime I use a plugin. (IE If I were to write a plugin for this I would have to handle every invoicedetail update, create, delete plus the invoice create and delete).

    It's more like :

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    CREATE TRIGGER [dbo].[InsertSetTotalAmountBaseCurrencyInvoice] ON [dbo].[InvoiceBase] 
    FOR INSERT, UPDATE
    AS 
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	DECLARE @idsalesorder uniqueidentifier
    	DECLARE @idfactura uniqueidentifier
    	
    	SET @idfactura = (SELECT InvoiceId from inserted)
    	SET @idsalesorder = (SELECT SalesOrderId  FROM inserted)
    	
    	IF(@idsalesorder IS NOT NULL)
    	BEGIN
    	DECLARE @total DECIMAL(12,3)
    	DECLARE @taxaconversao DECIMAL(12,3)
    	SET @total = (SELECT TotalAmount FROM InvoiceBase where InvoiceId = @idfactura)
    	SET @taxaconversao = (SELECT ESRI_TaxadeCambio FROM SalesOrderExtensionBase 
    	WHERE SalesOrderId LIKE @idsalesorder)
    	UPDATE [InvoiceExtensionBase]
    	SET ESRI_totaldatadeencomenda = @total / @taxaconversao
    	WHERE InvoiceId = @idfactura
    	END
    	ELSE
    	BEGIN
    	UPDATE [InvoiceExtensionBase]
    	SET ESRI_totaldatadeencomenda = (SELECT TotalAmount_Base FROM InvoiceBase where InvoiceId = @idfactura)
    	END
    
    	
    
    END

    And I didn't know we could turn off the option of recursive triggers so it seems I can do the trigger on the InvoiceExtensionBase.
    Wednesday, April 16, 2014 4:30 PM
  • Okay, I understand your point and I can think of two options at this stage

    option 1 : Supported one 

    assuming this is CRM 4 system one supported way to do it using custom workflow as mentioned in steps below...

    1. you need custom workflow which do add/divide/subtract for you and give result. apparently this workflow is already provided by Microsoft for CRM as accelerators at following link

    http://crmaccelerators.codeplex.com/releases/view/26691

    since this is custom workflow it is really less risk since it will only get called in workflow which uses them and it will not impact existing code as well

    2. once you have this custom workflow in your system, you need to create workflow against Invoice post create 

    3. in this workflow you can call new custom workflow function Match and pass relevant parameters..

       since this workflow is against invoice you can retrieve fields from it's parent sales order and pass this as parameter 

    4. once you get result you can just update relevant field in this workflow using update step

     

    Option 2 : Unsupported  one

    you can go ahead with trigger option.

    your current trigger does not specify that it is after trigger and also you can even check trigger nested level and also disable recursive trigger as shown in post below..

    http://stackoverflow.com/questions/5341584/sql-server-after-insert-trigger-update-another-column-in-the-same-table

    Hope this helps


    MayankP
    My Blog
    Follow Me on Twitter

    • Marked as answer by Agenteusa Monday, April 21, 2014 5:20 PM
    Wednesday, April 16, 2014 6:01 PM
  • Sorry for not being able to reply before.

    I didn't know the accelerators existed but I will surely give them a try since they can be very useful for some other stuff I have to implement. thanks for the link.

    I ended up being able to create the trigger and its working succesfully. I don't get it when you say I don't have na after trigger.

    I was almost sure that a FOR trigger was the same as an AFTER trigger being the INSTEAD trigger the other option.

    and as I added in an updated post "And I didn't know we could turn off the option of recursive triggers so it seems I can do the trigger on the InvoiceExtensionBase." .

    so yes I figured out that I recursivness could beturned off.

    I'm marking the answer as accepted since I found your first suggestion very interesting.

    thanks

    Monday, April 21, 2014 5:20 PM