How is it possible in SQL Server to execute multiple DDL statements dynamically with a single script?

  Kiến thức lập trình

I want to dynamically create many triggers in the same script, but it fails, do you know any way to achieve this?

Try the following script:

declare @commands varchar(max) = '
    drop table if exists dbo.Countries
    drop table if exists dbo.Cities

    create table dbo.Countries(
        id int identity not null primary key,
        Country varchar(50) not null
    )

    create table dbo.Cities(
        id int identity not null primary key,
        City varchar(50) not null
    )
'

/* This execution works well */
execute (@commands)
go

declare @commands varchar(max) = '
    create trigger tr_Countries on
        dbo.Countries for insert as
    begin
        print ''A new country was created.''
    end

    create trigger tr_Cities on
        dbo.Cities for insert as
    begin
        print ''A new city was created.''
    end
'
/* This execution fails:
Msg 156, Level 15, State 1, Procedure tr_Countries, Line 8 [Batch Start Line 20]
Incorrect syntax near the keyword 'trigger'. 
*/
execute (@commands)

As you can see, the two batches are very similar, but the first one runs successfully but the second one fails.

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website Kho Theme wordpress Kho Theme WP Theme WP

LEAVE A COMMENT