locked
How to implement sql trigger logic in plugin CRM 2013 RRS feed

  • Question

  • Hi,

    I have created a trigger in sql server for maintain version. But now I want to implement this logic on plugin. Can you tell me how to implement this trigger on plugin in CRM 2013?

    CREATE TRIGGER [dbo].[PWC_TR_UpdateProposalVersion]
    ON [dbo].[new_proposalBase]
    After Insert
    AS
    declare @opportunityId uniqueidentifier
    declare @total decimal(23, 10)
    declare @name nvarchar(100)
    declare @proposalId uniqueidentifier

    set @proposalId=(select new_proposalId from inserted)
    set @opportunityId=(select new_Opportunity from inserted)
    set @name=(select new_name from inserted)
    set @total=(select count(new_proposalId)from new_proposalBase where new_Opportunity=@opportunityId and new_name=@name)-1

    begin

    UPDATE [new_proposalBase]
    SET
    new_version=1+(@total/10)
    where new_proposalId=@proposalId
    end
    GO

    Thursday, February 26, 2015 6:03 AM

Answers

  • hi Aamir,

     your linq is not allowing you to return count. So you have to get the list out and then do a count.

    sample code provided below, please note that this is approximation, i am writing the code in notepad :-).

    var total = (from pr in CrmContext.CreateQuery("new_proposal")
                                  where pr.GetAttributeValue<EntityReference>("new_Opportunity") == new_proposalBase.GetAttributeValue<EntityReference>("new_Opportunity") &&
                                  pr.GetAttributeValue<string>("new_name") == new_proposalBase.GetAttributeValue<string>("new_name")                              
                                  select pr).toList();
    
    int count = total.Count; //This will evaluate the function and return you the count
    
    int total1 = 1 + (count / 10);
                    Version.Set(executionContext, total1.ToString()); 

    Regards,

    Jithesh

    • Marked as answer by Aamir Hijazi Tuesday, March 3, 2015 6:31 AM
    Friday, February 27, 2015 9:25 AM

All replies

  • Hi Aamir,

     You could create a realtime workflow on proposal to update this field. There are a lot of custom workflow activities available for data manipulation. Alternately, you may have to create a custom workflow activity to do the computation.

    Regards,

    Jithesh

    Thursday, February 26, 2015 6:25 AM
  • Can you guide me how to implement it with custom workflow activity?
    Thursday, February 26, 2015 6:36 AM
  • Please look into the following folder under SDK, SDK\SampleCode\CS\Process\CustomWorkflowActivities.

    Meanwhile, I will also see if I can create a class for you to start with.

    Regards

    Jithesh

    Thursday, February 26, 2015 6:54 AM
  • I would leave the finer formatting and debuggin to you. Please create a dll, register using plugin registration tool.

    using System;
        using System.Activities;
        using System.ServiceModel;
        using Microsoft.Xrm.Sdk;
        using Microsoft.Xrm.Sdk.Workflow;
        using Microsoft.Xrm.Sdk.Query;
        using System.Linq;
    
    
        public sealed class UpdateProposalVersion : CodeActivity
        {
    
            [Output("Version")]
            public OutArgument<string> Version { get; set; }
    
            /// <summary>
            /// Executes the workflow activity.
            /// </summary>
            /// <param name="executionContext">The execution context.</param>
            protected override void Execute(CodeActivityContext executionContext)
            {
                // Create the tracing service
                ITracingService tracingService = executionContext.GetExtension<ITracingService>();
    
                if (tracingService == null)
                {
                    throw new InvalidPluginExecutionException("Failed to retrieve tracing service.");
                }
    
                tracingService.Trace("Entered UpdateProposalVersion.Execute(), Activity Instance Id: {0}, Workflow Instance Id: {1}",
                    executionContext.ActivityInstanceId,
                    executionContext.WorkflowInstanceId);
    
                // Create the context
                IWorkflowContext context = executionContext.GetExtension<IWorkflowContext>();
    
                if (context == null)
                {
                    throw new InvalidPluginExecutionException("Failed to retrieve workflow context.");
                }
    
                tracingService.Trace("UpdateProposalVersion.Execute(), Correlation Id: {0}, Initiating User: {1}",
                    context.CorrelationId,
                    context.InitiatingUserId);
    
                IOrganizationServiceFactory serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();
                IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
    
                try
                {
                   // Get the current entity.
                    Entity new_proposalBase = service.Retrieve(context.PrimaryEntityName, context.PrimaryEntityId, new ColumnSet(true));
    
                    Microsoft.Xrm.Sdk.Client.OrganizationServiceContext CrmContext = new Microsoft.Xrm.Sdk.Client.OrganizationServiceContext(service);
    
                    var total = (from pr in CrmContext.CreateQuery("new_proposal")
                                  where pr.GetAttributeValue<EntityReference>("new_Opportunity") == new_proposalBase.GetAttributeValue<EntityReference>("new_Opportunity") &&
                                  pr.GetAttributeValue<string>("new_name") == new_proposalBase.GetAttributeValue<string>("new_name")                              
                                  select pr).Count();
    
                    total = 1 + (total / 10);
                    Version.Set(executionContext, total.ToString());
                }
                catch (FaultException<OrganizationServiceFault> e)
                {
                    tracingService.Trace("Exception: {0}", e.ToString());
    
                    // Handle the exception.
                    throw;
                }
    
                tracingService.Trace("Exiting UpdateProposalVersion.Execute(), Correlation Id: {0}", context.CorrelationId);
            }
        }

    Regards,

    Jithesh

    Thursday, February 26, 2015 7:19 AM
  • Thank you Jithesh.
    Thursday, February 26, 2015 7:53 AM
  • Hi Jithesh,

    I have registered custom workflow. When I define steps then there is no input parameters show. Please see images and tell me what did I mistake?

    Thursday, February 26, 2015 9:09 AM
  • No, no mistake, There is no input parameter for the activity only .

    Leave that step there.

    1. Change the current step description as "Get Total" (instead of "Type a step description here")

    2. Insert another step (after the current step) to update process entity - on the Process, select field "new_version"  and insert "Get Total" as the value

    -- Operator Drop down will have a Dynamic value called "Get Total" (kind of as last value).

    Let me know if you have any clarification.

    Cheers,

    Jithesh

    Thursday, February 26, 2015 9:26 AM
  • Hi Jithesh,

    I followed your instructions as you told me but unfortunately there is no version visible in version field.

    Thursday, February 26, 2015 10:27 AM
  • Please uncheck the "Automatically Delete Completed Workflow Jobs" option to see what is happening. Also change the scope of workflow from user to Organisation. If this does not work, next option would be to debug the plugin.

    For that you have to copy the pdb file and dll into Dynamics CRM<Installation Folder>\server\bin\assembly, and attach to the process.

    More detailed steps are here https://msdn.microsoft.com/en-us/library/cc151088.aspx

    Also Please make sure that the process that you create to test has new_name and new_opportunity details set. Process Session on the workflow on the left hand side will detail you the workflow progress.

    Regards,

    Jithesh

    Thursday, February 26, 2015 10:37 AM
  • Error is occurred on plugin execution.

    Unhandled Exception: System.ArgumentException: Unable to parse the OrganizationServiceFault.
    Parameter name: serializedReport
       at PluginProfiler.Library.ProfilerUtility.ExtractReport(String serializedReport)
       at PluginProfiler.Library.ProfilerUtility.DeserializeProfilerReport(String assemblyFilePath, String logFilePath)
       at PluginProfiler.Library.ProfilerExecutionUtility.RetrieveReport(String logFilePath)
       at Microsoft.Crm.Tools.PluginRegistration.OrganizationHelper.ParseReportOrShowError(IWin32Window owner, FileBrowserControl profilePathControl, Boolean requireReportParse, ProfilerPluginReport& report)
    Inner Exception: System.InvalidOperationException: File does not contain a valid serialized OrganizationServiceFault.
       at PluginProfiler.Library.ProfilerUtility.ConvertFaultToStream(String serializedFault)
       at PluginProfiler.Library.ProfilerUtility.ExtractReport(String serializedReport)

    Thursday, February 26, 2015 10:48 AM
  • http://missdynamicscrm.blogspot.com.au/2014/05/debug-crm-2013-using-plugin-profiler.html

    The actual message might be somewhere hidden.try to do the following steps.

    1. Build the dll.

    2. copy the dll and the .pdb file into "Dynamics CRM<Installation Folder>\server\bin\assembly".

    3. Register the dll using plugin registration tool.

    4. Create a new process,

    5. Attach Visual studio debugger to Async process and w3wp process.

    6. add a new breakpoint on line "

    ITracingService tracingService = executionContext.GetExtension<ITracingService>();

    7. Step thru and see what the exception is. remember, I created the plugin without knowing the data type or field names. so I might have made a mistake.

    Almost there mate, just debugging and finding what is happening inside is left to you.

    Please note that the plugin executes on the record created. you can also make the workflow as ondemand to help with testing.

    Let me know how it goes.

    Jithesh

    Thursday, February 26, 2015 11:06 AM
  • Hi Jithesh,

    I have debugged my plugin there is an error occurred. Please see in below image and give suggestion.

    Friday, February 27, 2015 8:43 AM
  • hi Aamir,

     your linq is not allowing you to return count. So you have to get the list out and then do a count.

    sample code provided below, please note that this is approximation, i am writing the code in notepad :-).

    var total = (from pr in CrmContext.CreateQuery("new_proposal")
                                  where pr.GetAttributeValue<EntityReference>("new_Opportunity") == new_proposalBase.GetAttributeValue<EntityReference>("new_Opportunity") &&
                                  pr.GetAttributeValue<string>("new_name") == new_proposalBase.GetAttributeValue<string>("new_name")                              
                                  select pr).toList();
    
    int count = total.Count; //This will evaluate the function and return you the count
    
    int total1 = 1 + (count / 10);
                    Version.Set(executionContext, total1.ToString()); 

    Regards,

    Jithesh

    • Marked as answer by Aamir Hijazi Tuesday, March 3, 2015 6:31 AM
    Friday, February 27, 2015 9:25 AM