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.