SQL Server Inserting multiple rows using Triggers

Question

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

Why a trigger? Insert the raw data into the table then run another script to move the data. That makes it much easier to cleanse the data, remove dupes, improve performance, etc. — dfundako, May 09 '18 at 16:26
In that case, Possible duplicate of [Handling multiple rows in SQL Server trigger](https://stackoverflow.com/questions/18695815/handling-multiple-rows-in-sql-server-trigger) — dfundako, May 09 '18 at 16:29
I've seen that question but i don't think it will work for me since it's updating the table and not inserting. what i need is to insert new data into the tables and not update — , May 09 '18 at 16:39

marc_s · Accepted Answer · 2018-05-09T16:55:53.107

4

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;

edited May 09 '18 at 16:55

answered May 09 '18 at 16:28

marc_s

732,580
175
1,330
1,459

Still is giving me the same error. I have tried using cursor maybe you'r familiar with it but still have no clue :/ – May 09 '18 at 16:32
1

@FrankanTabone You guys are using triggers AND cursors for this task? What kind of school do you go to?? – dfundako May 09 '18 at 16:34
cursors no but yes trigger and tbh yes they do give out some weird tasks – May 09 '18 at 16:38
@FrankanTabone: the error says that you cannot insert `NULL` into that column - is there any chance that your `INSERT` statement is producing `NULL` as `countryName` ?? If so, add a `where countryName IS NOT NULL` to the `SELECT` in the trigger. See my updated answer – marc_s May 09 '18 at 16:55
1

This worked. Thanks I've been trying for hours and had no clue how to solve this. – May 09 '18 at 16:58

SQL Server Inserting multiple rows using Triggers

1 Answers1