Altering a trigger

  • Hi

    I'm trying to set the not for replication flag on a trigger and I'm getting the error message

    Cannot perform alter on 'DBO.Trigger1' because it is an incompatible object type.

    The create trigger code is

    CREATE TRIGGER [dbo].[Trigger1] ON [dbo].[Table1] WITH EXECUTE AS CALLER AFTER INSERT, DELETE, UPDATE AS
    EXTERNAL NAME [Trigger1].[CLRTriggers].[DMLTrigger]
    GO

    and I'm trying to alter it using

       ALTER TRIGGER [DBO].[Trigger1]
        ON [dbo].[Table1]
        AFTER INSERT, DELETE, UPDATE
        NOT FOR REPLICATION
      AS
      BEGIN
      SET NOCOUNT ON
      END

    I also notice that Trigger1 in SSMS has a padlock icon on it, so I presume it's locked in some way. I'm an SA on the box though.

    Any help really appriciated.

    Alex

  • It's a CLR trigger, common language runtime, written in C#. It's not a T-SQL trigger and that's why you're getting the error when you try to alter it to a T-SQL trigger.

    Try adding the NOT FOR REPLICATION option to the CREATE TRIGGER code (and change it to an ALTER).

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I tried that before I posted. It's a sea of red and returns Incorrect syntax near 'NOT'.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply