'CREATE TRIGGER' must be the first statement in a query batch

Question

My script is wrapped in a begin transaction and commit transaction. I cant even use Go inbetween. In the script I want to disable one trigger and then want to Create another one But when i execute both the statements simultaneously I get the foll error:

Msg 111, Level 15, State 1, Line 6 'CREATE TRIGGER' must be the first statement in a query batch.

My code is:


BEGIN TRY  
BEGIN TRANSACTION 

DISABLE TRIGGER [dbo].[trg_Beneficiary_After_Update] ON [dbo].[Beneficiary] 

CREATE TRIGGER [dbo].[trg_Beneficiary_After_Update1] ON [dbo].[Beneficiary] FOR UPDATE AS
BEGIN 
INSERT INTO [dbo].[Beneficiary_History]([Beneficiary_Id],[Customer_Id],[Beneficiary_Type],[Nick_Name],[Rib_Key],[Action_Flag],[Benef_No],[Original_Date],[Provider_Id],[Create_Date])
SELECT [Beneficiary_Id],[Customer_Id],[Beneficiary_Type],[Nick_Name],[Rib_Key],'U',[Benef_No],[Create_Date],[Provider_Id],GETDATE() FROM DELETE
PRINT 'AFTER Beneficiary Table UPDATE1 trigger fired.'  
END

UPDATE [dbo].[Beneficiary] SET [Benef_No] = REPLACE (Benef_No, +2120, ++212) WHERE Benef_No like '+2120%'

DISABLE TRIGGER [dbo].[trg_Beneficiary_After_Update1] ON [$(oltpdb)].[dbo].[Beneficiary] ; 

ENABLE TRIGGER [dbo].[trg_Beneficiary_After_Update] ON [$(oltpdb)].[dbo].[Beneficiary] 

end
COMMIT TRANSACTION                                                                     END TRY  
BEGIN CATCH  
     ROLLBACK TRANSACTION
END CATCH;

You are missing some key concept. Transactions don't generally apply to DDL commands. They are designed for *data* integrity. — Gordon Linoff, Jun 30 '17 at 11:09
Hi sir @Priyanka check this two link may be you will find your solution :) [dynamic-sql-error-create-trigger](https://stackoverflow.com/questions/10336384/dynamic-sql-error-create-trigger-must-be-the-first-statement-in-a-query-batch) [create-trigger-must-be-the-first-statement-in-a-batch](https://stackoverflow.com/questions/13370505/create-trigger-must-be-the-first-statement-in-a-batch) — Hicham Bouchilkhi, Jun 30 '17 at 12:01
**Moderator Note:** Please do not vandalize your posts. Once you've posted a question, you have licensed the content to the Stack Overflow community at large (under the CC-by-SA license). If you would like to disassociate this post from your account, see [What is the proper route for a disassociation request?](http://meta.stackoverflow.com/questions/323395/what-is-the-proper-rout‌e-for-a-dissociation-request) — Bhargav Rao, Jul 03 '17 at 13:15

score 2 · Answer 1 · edited Jul 03 '17 at 13:13

@Priyanka, You can use dynamic SQL for creating trigger to be embedded in a stored procedure. Using semicolons will execute script in a batch manner. However, it is always best practice for you to create trigger outside this stored proc in this instance i believe. Test this script and let me know if it works.

BEGIN TRY
    BEGIN TRANSACTION

    BEGIN
            ;

        DISABLE TRIGGER [dbo].[trg_Beneficiary_After_Update]
            ON [dbo].[Beneficiary];

        DECLARE @SQL VARCHAR(MAX);

        SET @SQL = 
            'CREATE TRIGGER [dbo].[trg_Beneficiary_After_Update1] ON [dbo].[Beneficiary] FOR UPDATE AS
               BEGIN 
            INSERT INTO [dbo].[Beneficiary_History]([Beneficiary_Id],[Customer_Id],[Beneficiary_Type],[Nick_Name],[Rib_Key],[Action_Flag],[Benef_No],[Original_Date],[Provider_Id],[Create_Date])
            SELECT [Beneficiary_Id],[Customer_Id],[Beneficiary_Type],[Nick_Name],[Rib_Key],''U'',[Benef_No],[Create_Date],[Provider_Id],GETDATE() FROM DELETED 
             PRINT ''AFTER Beneficiary Table UPDATE1 trigger fired'' END'

        EXEC (@SQL);

        UPDATE [dbo].[Beneficiary]
        SET [Benef_No] = REPLACE(Benef_No, + 2120, + + 212)
        WHERE Benef_No LIKE '+2120%';

        DISABLE TRIGGER [dbo].[trg_Beneficiary_After_Update1]
            ON [$(oltpdb)].[dbo].[Beneficiary];

        ENABLE TRIGGER [dbo].[trg_Beneficiary_After_Update]
            ON [$(oltpdb)].[dbo].[Beneficiary];
    END

    COMMIT TRANSACTION;
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH;

@priyanka if you would like to take off Begin and end, it will still work — Ven, Jun 30 '17 at 13:41
@Priyanka That's Brilliant, thank you for giving feedback :) — Ven, Jul 03 '17 at 12:08

score 0 · Answer 2 · edited Jul 10 '17 at 06:54

Insert ";" in end line!

As

BEGIN TRY  
BEGIN TRANSACTION 

DISABLE TRIGGER [dbo].[trg_Beneficiary_After_Update] ON [dbo].[Beneficiary]; 

CREATE TRIGGER [dbo].[trg_Beneficiary_After_Update1] ON [dbo].[Beneficiary] FOR UPDATE AS
BEGIN 
INSERT INTO [dbo].[Beneficiary_History]([Beneficiary_Id],[Customer_Id],[Beneficiary_Type],[Nick_Name],[Rib_Key],[Action_Flag],[Benef_No],[Original_Date],[Provider_Id],[Create_Date])
SELECT [Beneficiary_Id],[Customer_Id],[Beneficiary_Type],[Nick_Name],[Rib_Key],'U',[Benef_No],[Create_Date],[Provider_Id],GETDATE() FROM DELETE
PRINT 'AFTER Beneficiary Table UPDATE1 trigger fired.'  
END

UPDATE [dbo].[Beneficiary] SET [Benef_No] = REPLACE (Benef_No, +2120, ++212) WHERE Benef_No like '+2120%'

DISABLE TRIGGER [dbo].[trg_Beneficiary_After_Update1] ON [$(oltpdb)].[dbo].[Beneficiary] ; 

ENABLE TRIGGER [dbo].[trg_Beneficiary_After_Update] ON [$(oltpdb)].[dbo].[Beneficiary] ;

end
COMMIT TRANSACTION                                                                     END TRY  
BEGIN CATCH  
     ROLLBACK TRANSACTION
END CATCH;

'CREATE TRIGGER' must be the first statement in a query batch

2 Answers2