问题描述
我有一个过程,该过程从外部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/en-us/library/ms188365(v=sql.105).aspxand 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行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!