January 14, 2020 at 9:12 pm
Hi,
I am working on update trigger. Whenever the json value is changed for the latest date then it should report the columns added or removed from latest date json.if no change with previous json then dont need to report. Need help with the json part comparison. Please suggest.Thanks.
Input table data-
DROP TABLE IF EXISTS dbo.temp;
CREATE TABLE dbo.temp(
Date VARCHAR(7) NOT NULL
,Name VARCHAR(1) NOT NULL
,Type VARCHAR(44) NOT NULL
);
INSERT INTO dbo.temp(Date,Name,Type) VALUES ('2019-10','A','["Test","id","Num","Start"]');
INSERT INTO dbo.temp(Date,Name,Type) VALUES ('2019-11','A','["Test","id","Num"]');
INSERT INTO dbo.temp(Date,Name,Type) VALUES ('2019-12','A','["Test","id","Num"]');
INSERT INTO dbo.temp(Date,Name,Type) VALUES ('2020-01','A','["Test","id","Num","Hello"]');
INSERT INTO dbo.temp(Date,Name,Type) VALUES ('2018-01','B','["Test11","id11","Num11","Hello11"]');
INSERT INTO dbo.temp(Date,Name,Type) VALUES ('2019-10','B','["Test11","test12","id11","Num11","Hello11"]');
INSERT INTO dbo.temp(Date,Name,Type) VALUES ('2019-11','B','["Test11","test12","id11","Num11","Hello11"]');
INSERT INTO dbo.temp(Date,Name,Type) VALUES ('2019-12','B','["Test11","test12","id11","Num11","Hello11"]');
INSERT INTO dbo.temp(Date,Name,Type) VALUES ('2020-01','B','["Test11","id11","Num11","Hello11","Test3","Test4"]');
Update Trigger-
CREATE TRIGGER Test1
AFTER UPDATE ON dbo.Temp
FOR EACH ROW
SET NOCOUNT ON;
BEGIN
DECLARE @Temp TABLE
(
[Date] varchar(100),
[Name] varchar(50),
ColumnsRemoved varchar(100),
ColumnsAdded varchar(100)
)
IF OLD.Type <> new.Type THEN
INSERT INTO @temp([Date],[Name], ColumnsRemoved,ColumnsAdded)
VALUES(Date Name, New.Type,New.type);
END IF;
Output data should look like below in the @Temp table-
DROP TABLE IF EXISTS dbo.Result;
CREATE TABLE dbo.Result(
Date VARCHAR(7) NOT NULL
,Name VARCHAR(1) NOT NULL
,ColumnsRemoved VARCHAR(6)
,ColumnsAdded VARCHAR(5) NOT NULL
);
INSERT INTO dbo.Result(Date,Name,ColumnsRemoved,ColumnsAdded) VALUES ('2020-01','A',NULL,'Hello');
INSERT INTO dbo.Result(Date,Name,ColumnsRemoved,ColumnsAdded) VALUES ('2020-01','B','test12','Test3');
January 15, 2020 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply