none
Updating existing CLR trigger - an easier way?

    Question

  • [Running SQL Server 2005]

    I have a test case CLR DML trigger up and running. However, it seems tedious to update to a new version of the .dll:

    - Compile .dll
    - Delete existing trigger
    - Delete existing assembly
    - Re-create assembly
    - Re-create trigger

    Is there some method requiring fewer steps that I am missing? Thanks for any assistance.

    Wednesday, March 21, 2012 6:17 PM

Answers

  • If you don’t change any of the method signatures of the items declared to T-SQL (which you wouldn’t for DDL trigger because it accepts no input parameters), you can simply do ALTER ASSEMBLY..... I haven’t tried with SSDT in SQL Server 2012 yet, but VS2010 auto-deploy doesn’t support ALTER ASSEMBLY, you’ll need to code the DDL.
     
    Cheers,
    Bob
    • Proposed as answer by pen_2 Monday, March 26, 2012 2:09 PM
    • Marked as answer by KJian_ Wednesday, March 28, 2012 6:10 AM
    Wednesday, March 21, 2012 8:14 PM
    Moderator

All replies

  • If you don’t change any of the method signatures of the items declared to T-SQL (which you wouldn’t for DDL trigger because it accepts no input parameters), you can simply do ALTER ASSEMBLY..... I haven’t tried with SSDT in SQL Server 2012 yet, but VS2010 auto-deploy doesn’t support ALTER ASSEMBLY, you’ll need to code the DDL.
     
    Cheers,
    Bob
    • Proposed as answer by pen_2 Monday, March 26, 2012 2:09 PM
    • Marked as answer by KJian_ Wednesday, March 28, 2012 6:10 AM
    Wednesday, March 21, 2012 8:14 PM
    Moderator
  • Thanks, Bob.
    Wednesday, March 28, 2012 3:23 PM
  • It would certainly be nice if the process was easier.

    i.e. Publish Update menu item or Publish just being smart enough to replace the existing trigger if it exists - in Oracle you can mark in an update script to "update or replace", which is what we really need here. 

    I'd classify the current solution as a work around rather than a solution.

    Thursday, January 16, 2014 10:01 PM