SQLTeam.com | Weblogs | Forums

Trigger on insert to write JSON data to another table

Hi ,
I have error with this statement:
The multi-part identifier OriginatingTables.JSONColumn could not be bound.
It works fine as a standard select, without the triggger, no red tilde and parses correctly.

CREATE TRIGGER [dbo].JSON_Trigger ON [dbo].OriginatingTables
AFTER INSERT
AS
BEGIN

INSERT INTO [dbo].[TargetTable]
(OriginatingTableID, FileName)
SELECT OriginatingTableID,
JSON_VALUE(S.value,'$.Filename') as FileName
FROM inserted
CROSS APPLY OPENJSON(OriginatingTables.JSONColumn) S
end
GO

The table name is "inserted" not "OriginatingTables". It's very common to use "i" as an alias for inserted, like this:

INSERT INTO [dbo].[TargetTable]
(OriginatingTableID, FileName)
SELECT OriginatingTableID,
JSON_VALUE(S.value,'$.Filename') as FileName
FROM inserted i
CROSS APPLY OPENJSON(i. JSONColumn) S
end
GO