locked
Using triggers to update oracle database RRS feed

  • Question

  • I've created a trigger to update a record in the system we are integrating with CRM through a linked server.  When I save the record in CRM I get the Microsoft Dynamics CRM -- Web Page Dialog.

     

    "An error has occurred.

     

    Try this action again.  If the problem continues, check the Microsoft Dynamics CRM Community for solutions or contact your organization's Microsoft Dynamics CRM Administrator."

     

    I do not receive an error if I update this record through Server Management Studio, only when I update it using the CRM interface.  So far this has just been tested through the web interface.

     

    Thanks and here is the trigger.

     

    ALTER TRIGGER [hospitaltrigger]

    ON [PetsBestInsurance_MSCRM].[dbo].[New_vethospitalExtensionBase]

    AFTER INSERT,UPDATE

    AS

    declare @tia_name_id nvarchar(8);

    declare @zip nvarchar(5);

    declare @openquery nvarchar(4000);

    declare @inquery nvarchar(4000);

    begin

    set nocount on

    select @tia_name_id = i.new_tia_id from inserted i;

    select @zip = i.new_zip from inserted i;

    set @openquery = 'update openquery(Cletus,';

    set @inquery = '''select post_area from name where id_no = ' + @tia_name_id + ''') set post_area = ' + @zip;

    commit transaction;

    exec (@openquery + @inquery);

    begin transaction;

     

    end

    Thursday, June 5, 2008 2:18 PM

Answers

  • Adding a trigger to a table in teh CRM DB is not supported.  I would highly reocmmend using a plugin instaead, as it will be invoked by the application tier and is totally supported.

     

    Friday, June 6, 2008 2:37 AM

All replies

  • Adding a trigger to a table in teh CRM DB is not supported.  I would highly reocmmend using a plugin instaead, as it will be invoked by the application tier and is totally supported.

     

    Friday, June 6, 2008 2:37 AM
  • Thanks.  That was my other thought. 

     

    Appreciate the help.

     

    Friday, June 6, 2008 8:34 PM