CODEWITHSUNDEEP
sql-servertriggers
user9765107
user9765107

Reputation:

SQL Server Inserting multiple rows using Triggers

I am loading data from a JSON file to a table "main.jsontable" the trigger job is to insert the data of all different countries from "main.jsontable" into "main.country" table. My problem is that the trigger needs to handle inserting multiple rows my current code is:

create or alter trigger main.afterParsing
on main.jsontable 
after insert
as
begin
    declare @country nvarchar(50);

    insert into main.country(countryName)
    values(@country)
end;

but I get this error (obviously because triggers can only handle inserting 1 row at a time):

Cannot insert the value NULL into column 'countryName', table 'assignment2.main.country'; column does not allow nulls. INSERT fails.

Does anyone know how I can use the trigger to insert multiple rows?

Thanks

Upvotes: 2

Views: 3519

Answers (1)

marc_s
marc_s

Reputation: 754508

You need to use the Inserted pseudo table, and you need to understand that it can contain multiple rows (if your INSERT statement inserted more than 1 row at once), and you need to treat that table accordingly, using a proper set-based approach.

Try something like this:

create or alter trigger main.afterParsing
on main.jsontable 
after insert
as
begin
    insert into main.country(countryName)
        select countryName
        from Inserted
        -- if the data being inserted could have NULL in the
        -- countryName - filter those entries out
        where countryName is not null
end;

Upvotes: 5

Related Questions