none
Trigger help RRS feed

  • Question

  • 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


    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,Test4’);



    • Edited by Papil1 Wednesday, January 15, 2020 1:39 PM SQL
    Tuesday, January 14, 2020 10:46 PM

Answers

  • As I expected, you need an INSERT trigger. Try this:

    DROP TABLE IF EXISTS dbo.Temp;
    GO
    CREATE TABLE dbo.Temp (
    	Date VARCHAR(7) NOT NULL,
    	Name VARCHAR(1) NOT NULL,
    	Type VARCHAR(100) 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 ('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"]');
    GO
    
    DROP TABLE IF EXISTS dbo.Result;
    GO
    CREATE TABLE dbo.Result (
    	Date VARCHAR(7) NOT NULL,
    	Name VARCHAR(1) NOT NULL,
    	ColumnsRemoved VARCHAR(200) NULL,
    	ColumnsAdded VARCHAR(200) NOT NULL
    );
    GO
    
    CREATE TRIGGER dbo.TR_Temp_INSERT ON dbo.Temp
    AFTER INSERT
    AS
    BEGIN
    	DECLARE @Type VARCHAR(100);
    	DECLARE @Date VARCHAR(7);
    	DECLARE @NewType VARCHAR(100);
    	DECLARE @Name VARCHAR(1);
    
    	SELECT @Date = Date, @Name = Name, @NewType = Type FROM inserted;
    	SELECT TOP 1 @Type = Type FROM dbo.Temp WHERE Date < @Date AND Name = @Name ORDER BY Date DESC;
    
    	IF @NewType <> @Type
    	BEGIN
    		DECLARE @ColumnsRemoved VARCHAR(20);
    		DECLARE @ColumnsAdded VARCHAR(20);
    		DECLARE @OldTypes Table (
    			Field varchar(20)
    		);
    		DECLARE @NewTypes Table (
    			Field varchar(20)
    		);
    		INSERT INTO @OldTypes
    		SELECT value FROM OPENJSON(@Type);
    
    		INSERT INTO @NewTypes
    		SELECT value FROM OPENJSON(@NewType);
    
    		SELECT @ColumnsRemoved = STUFF(
    			(
    				SELECT ',' + Field
    				FROM @OldTypes 
    				WHERE Field NOT IN (SELECT Field FROM @NewTypes)
    				FOR XML PATH(''), TYPE
    			).value('.', 'NVARCHAR(MAX)'),
    			1,
    			1,
    			''
    		);
    		
    		SELECT @ColumnsAdded = STUFF(
    			(
    				SELECT ',' + Field
    				FROM @NewTypes 
    				WHERE Field NOT IN (SELECT Field FROM @OldTypes)
    				FOR XML PATH(''), TYPE
    			).value('.', 'NVARCHAR(MAX)'),
    			1,
    			1,
    			''
    		);
    
    		INSERT INTO dbo.Result (Date, Name, ColumnsRemoved, ColumnsAdded) 
    		VALUES (@Date, @Name, @ColumnsRemoved, @ColumnsAdded);
    	END
    END
    GO
    
    -- Test here
    SELECT * FROM dbo.Temp;
    SELECT * FROM dbo.Result;
    GO
    INSERT INTO dbo.Temp(Date,Name,Type) VALUES ('2020-01','A','["Test","id","Num","Hello"]');
    INSERT INTO dbo.Temp(Date,Name,Type) VALUES ('2020-01','B','["Test11","id11","Num11","Hello11","Test3","Test4"]');
    GO
    SELECT * FROM dbo.Temp;
    SELECT * FROM dbo.Result;
    GO


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Papil1 Wednesday, January 15, 2020 9:49 PM
    • Unmarked as answer by Papil1 Wednesday, January 22, 2020 7:09 PM
    • Marked as answer by Papil1 Wednesday, January 22, 2020 9:47 PM
    Wednesday, January 15, 2020 4:24 PM

All replies

  • You do not have any update statement, how do you get the Result data from the UPDATE trigger? Guess you may need an INSERT trigger. And also in the last row of the sample data, test12 is removed while Test3 and Tes4 are added, but your result data only have Test3 added.  

    A Fan of SSIS, SSRS and SSAS

    Wednesday, January 15, 2020 3:22 AM
  • CREATE TRIGGER Test1 AFTER UPDATE ON dbo.Temp
    FOR EACH ROW

    Is that PL/SQL Oracle? In MS SQL Server we don't have FOR EACH ROW in trigger declaration.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 15, 2020 6:45 AM
  • Hi Papil1,

    There is a simple skeleton: Oracle / PLSQL: AFTER INSERT Trigger.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, January 15, 2020 8:27 AM
  • CREATE TRIGGER Test1 AFTER UPDATE ON dbo.Temp
    FOR EACH ROW

    Is that PL/SQL Oracle? In MS SQL Server we don't have FOR EACH ROW in trigger declaration.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    SQL server
    Wednesday, January 15, 2020 1:39 PM
  • Can you describe how the trigger fires? Based on your description, it seems you need an INSERT trigger.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, January 15, 2020 2:51 PM
  • Can you describe how the trigger fires? Based on your description, it seems you need an INSERT trigger.

    A Fan of SSIS, SSRS and SSAS

    When a new record is inserted into the table-dbo.temp.It will be compared with the previous record and if there is any change in the Type column. It should be reported.
    Wednesday, January 15, 2020 3:26 PM
  • You can use a separate query to retrieve this information.

    Here is a sample query based on your requirements:

    DROP TABLE IF EXISTS dbo.temp;
    
    CREATE TABLE dbo.temp(
    Date VARCHAR(7) NOT NULL
    ,Name VARCHAR(1) NOT NULL
    ,Type VARCHAR(51) NOT NULL
    );
    
    INSERT INTO dbo.temp(Date,Name,Type) 
    VALUES ('2019-10','A','["Test","id","Num","Start"]'), 
    ('2019-11','A','["Test","id","Num"]'),
     ('2019-12','A','["Test","id","Num"]'),
     ('2020-01','A','["Test","id","Num","Hello"]'),
     ('2018-01','B','["Test11","id11","Num11","Hello11"]'),
     ('2019-10','B','["Test11","test12","id11","Num11","Hello11"]'),
    ('2019-11','B','["Test11","test12","id11","Num11","Hello11"]'),
    ('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"]');
    
    
    --Query
    Begin
    
    DECLARE @Temp TABLE
    
    (
    
    [Date] varchar(100),
    [Name] varchar(50),
    ColumnsRemoved varchar(100),
    ColumnsAdded varchar(100)
    
    )
    
    
    ;with mycte as 
    (
    select Date,Name,[Type]
    ,row_number() Over(partition by  Name Order by Date DESC) rn
    from  dbo.temp src
    )
     
     ,mycte2 as (
    select Date,Name
    ,unpvt.[Key], unpvt.Value ,rn
    from mycte src
    CROSS APPLY OpenJson(src.[Type]) AS unpvt  
    Where
    rn in (1,2)
    )
     
    
    ,resultcte as (
    select  t1.Date ,
    coalesce(t1.Name,t2.Name) Name,
      string_agg(coalesce(t1.Value,t2.Value) ,',')  Added,
      string_agg(coalesce(t1.Value,t2.Value) ,',')  Removed
    from (select * from mycte2 where rn=1) t1
    full outer join   (select * from mycte2 where rn=2) t2 on t1.name=t2.name 
    and t1.rn=t2.rn-1
    and t1.value=t2.value
    WHERE t1.rn is null or t2.rn is null
     group by t1.Date, coalesce(t1.Name,t2.Name)  
    )
    INSERT INTO @temp([Date],[Name], ColumnsRemoved,ColumnsAdded)
    
    select  
    max(Date) Date
    , name
    , Max(Case when Date is null then Removed else null end) Removed
    , Max(Case when Date is not null then Added else null end) Added
    from resultcte 
    group by  name
    
     
    Select * from @Temp
    
     
    
     END
    
    
    DROP TABLE IF EXISTS dbo.temp;

    Wednesday, January 15, 2020 4:21 PM
    Moderator
  • As I expected, you need an INSERT trigger. Try this:

    DROP TABLE IF EXISTS dbo.Temp;
    GO
    CREATE TABLE dbo.Temp (
    	Date VARCHAR(7) NOT NULL,
    	Name VARCHAR(1) NOT NULL,
    	Type VARCHAR(100) 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 ('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"]');
    GO
    
    DROP TABLE IF EXISTS dbo.Result;
    GO
    CREATE TABLE dbo.Result (
    	Date VARCHAR(7) NOT NULL,
    	Name VARCHAR(1) NOT NULL,
    	ColumnsRemoved VARCHAR(200) NULL,
    	ColumnsAdded VARCHAR(200) NOT NULL
    );
    GO
    
    CREATE TRIGGER dbo.TR_Temp_INSERT ON dbo.Temp
    AFTER INSERT
    AS
    BEGIN
    	DECLARE @Type VARCHAR(100);
    	DECLARE @Date VARCHAR(7);
    	DECLARE @NewType VARCHAR(100);
    	DECLARE @Name VARCHAR(1);
    
    	SELECT @Date = Date, @Name = Name, @NewType = Type FROM inserted;
    	SELECT TOP 1 @Type = Type FROM dbo.Temp WHERE Date < @Date AND Name = @Name ORDER BY Date DESC;
    
    	IF @NewType <> @Type
    	BEGIN
    		DECLARE @ColumnsRemoved VARCHAR(20);
    		DECLARE @ColumnsAdded VARCHAR(20);
    		DECLARE @OldTypes Table (
    			Field varchar(20)
    		);
    		DECLARE @NewTypes Table (
    			Field varchar(20)
    		);
    		INSERT INTO @OldTypes
    		SELECT value FROM OPENJSON(@Type);
    
    		INSERT INTO @NewTypes
    		SELECT value FROM OPENJSON(@NewType);
    
    		SELECT @ColumnsRemoved = STUFF(
    			(
    				SELECT ',' + Field
    				FROM @OldTypes 
    				WHERE Field NOT IN (SELECT Field FROM @NewTypes)
    				FOR XML PATH(''), TYPE
    			).value('.', 'NVARCHAR(MAX)'),
    			1,
    			1,
    			''
    		);
    		
    		SELECT @ColumnsAdded = STUFF(
    			(
    				SELECT ',' + Field
    				FROM @NewTypes 
    				WHERE Field NOT IN (SELECT Field FROM @OldTypes)
    				FOR XML PATH(''), TYPE
    			).value('.', 'NVARCHAR(MAX)'),
    			1,
    			1,
    			''
    		);
    
    		INSERT INTO dbo.Result (Date, Name, ColumnsRemoved, ColumnsAdded) 
    		VALUES (@Date, @Name, @ColumnsRemoved, @ColumnsAdded);
    	END
    END
    GO
    
    -- Test here
    SELECT * FROM dbo.Temp;
    SELECT * FROM dbo.Result;
    GO
    INSERT INTO dbo.Temp(Date,Name,Type) VALUES ('2020-01','A','["Test","id","Num","Hello"]');
    INSERT INTO dbo.Temp(Date,Name,Type) VALUES ('2020-01','B','["Test11","id11","Num11","Hello11","Test3","Test4"]');
    GO
    SELECT * FROM dbo.Temp;
    SELECT * FROM dbo.Result;
    GO


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Papil1 Wednesday, January 15, 2020 9:49 PM
    • Unmarked as answer by Papil1 Wednesday, January 22, 2020 7:09 PM
    • Marked as answer by Papil1 Wednesday, January 22, 2020 9:47 PM
    Wednesday, January 15, 2020 4:24 PM
  • Your description makes no sense. Neither in a JSON array nor an JSON object has columns.
    Wednesday, January 15, 2020 4:35 PM
  • @Papil1: This is not a correct answer.

    Cause you will only store arbitrary information about your data changes. Triggers are executed per statement, thus more than one row can be inserted at once. Thus you need to use the DELETED and INSERTED virtual tables.

    Thursday, January 16, 2020 10:09 AM
  • It does not make sense to flag Stephan's reply. 

    You are on a wrong path if I am not wrong.

    Wednesday, January 22, 2020 8:33 PM
    Moderator
  • If you use the same values (2020-01) of the column Date, how do you know which one is the previous one?

    A Fan of SSIS, SSRS and SSAS

    Wednesday, January 22, 2020 8:51 PM