Trigger for Update

  • Papil

    SSCommitted

    Points: 1983

    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');

     

     

  • Site Owners

    SSC Guru

    Points: 80379

    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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply