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
– dfundako, Commented May 9, 2018 at 16:26
In that case, Possible duplicate of Handling multiple rows in SQL Server trigger — dfundako
– dfundako, Commented May 9, 2018 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 — user9765107
– user9765107, Commented May 9, 2018 at 16:39

marc_s · Accepted Answer · 2018-05-09 16:55:53Z

5

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 9, 2018 at 16:55

answered May 9, 2018 at 16:28

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user9765107 Over a year ago

Still is giving me the same error. I have tried using cursor maybe you'r familiar with it but still have no clue :/

dfundako Over a year ago

@FrankanTabone You guys are using triggers AND cursors for this task? What kind of school do you go to??

user9765107 Over a year ago

cursors no but yes trigger and tbh yes they do give out some weird tasks

marc_s Over a year ago

@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

user9765107 Over a year ago

This worked. Thanks I've been trying for hours and had no clue how to solve this.

Collectives™ on Stack Overflow

SQL Server Inserting multiple rows using Triggers

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related