SQL Server：触发器仅影响1行 [英] SQL Server : trigger only affecting 1 row

问题描述

我有一个过程，该过程从外部JSON中获取数据并将其解析为一个表，该表运行良好。现在，我正在尝试创建一个触发器，以便每添加一个数据集，触发器就会将数据分离到适当的表和列中。

I have a procedure which takes data from an external JSON and parses it into a table, which works perfectly. Now I am trying to make a trigger so that, whenever a dataset is added, the trigger separates the data into the appropriate tables and columns.

CREATE TRIGGER main.afterParsing 
ON main.jsontable
AFTER INSERT
AS
BEGIN
    --Country Variables
    DECLARE @CountryCountry NVARCHAR(50)

    --Person Variables
    DECLARE @PersonName NVARCHAR(50)
    DECLARE @PersonSurname NVARCHAR(50)
    DECLARE @PersonEmail NVARCHAR(50)
    DECLARE @PersonAge NVARCHAR(50)
    DECLARE @PersonCountry NVARCHAR(50)
    DECLARE @PersonRegistered NVARCHAR(50)

    --Get Country
    SELECT @CountryCountry = jsontable.country FROM inserted jsontable;

    --Insert Country
    INSERT INTO country(countryName)
        SELECT @CountryCountry
        WHERE @CountryCountry NOT IN (Select CountryName FROM Country)
END;

这是触发器，但由于某种原因，它仅更新一行。我在做什么错？

This is the trigger, but for some reason it is only updating a single row. What am I doing wrong?

推荐答案

默认情况下，语句不执行触发器。但是，您可以通过使用FIRE_TRIGGERS限定词来启用触发器：

By default bulk, statement do not execute triggers. However, you can enable triggers by using FIRE_TRIGGERS qualifiers:

http://msdn.microsoft.com/zh-CN/library/ms188365（v = sql.105）.aspx
和下面是触发器的更新代码。

http://msdn.microsoft.com/en-us/library/ms188365(v=sql.105).aspx and below is updated code for your trigger.

GO

CREATE TRIGGER main.afterParsing ON main.jsontable
AFTER INSERT
AS
BEGIN

    --Country Variables
    DECLARE @CountryCountry NVARCHAR(50)

    --Person Variables
    DECLARE @PersonName NVARCHAR(50)
    DECLARE @PersonSurname NVARCHAR(50)
    DECLARE @PersonEmail NVARCHAR(50)
    DECLARE @PersonAge NVARCHAR(50)
    DECLARE @PersonCountry NVARCHAR(50)
    DECLARE @PersonRegistered NVARCHAR(50)

    --Get Country
    SELECT @CountryCountry = jsontable.country FROM inserted jsontable;

    IF NOT EXISTS (Select CountryName FROM Country WHERE CountryName=@CountryCountry)
    BEGIN
    --Insert Country
    INSERT INTO country(countryName)
    VALUES (@CountryCountry)
   END 

END;

GO

这篇关于SQL Server：触发器仅影响1行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！