How to check and update table in trigger in sql server

Question

Hi I have a table with data like this...

A     B    C     D    E 
100  A1  Hard  Dece  100
100  A1  Hard  Jan   200
100  A1  SOFT  Jan   200
200  A2  Hard  Dec   250
200  A2  SOFT  Jan   300
----------------------------

Then when an insert or update performed on this table i need to execute a trigger and The data should be like below

A    B   C     F
100  A1 Hard   Dece:100;Jan:200
100  A1 SOFT   Jan:200
200  A2 Hard   Dec:250
------------------------

I have created a trigger like below

ALTER TRIGGER [dbo].[trgInsert9] ON [roll].[dbo].[testing]
after INSERT,Update
AS
Declare @Counter int=0
Declare @max int
Declare @re int
select *  into  #t13  from (select A,B,C,[D] + ':' + cast([E] as varchar) as Common  from [roll].[dbo].[testing]) as X
select * into #t24 from (
SELECT t2.A,t2.B,t2.C,  
STUFF((SELECT ';' + CAST(Common AS varchar) 
FROM #t13 t1  where t1.A =t2.A and t1.B=t2.B and t1.C=t2.C
FOR XML PATH('')), 1 ,1, '') AS List
FROM #t13 t2
GROUP BY t2.A,t2.B,t2.C) as XT
SET @max=(select count(A) from #t24)
while(@Counter<@max)
BEGIN
if exits(select count(t11.A) from Test123 t11 left join #t24 f on t11.A=f.A 
 where t11.A=f.A and t11.B=f.B and t11.C=f.C
and t11.ValueList=f.ValueList)
  Begin
update Test123 set Test123.ValueList=t4.ValueList from #t24 t4
where
 Test123.A=t4.A and Test123.B=t4.B and Test123.C=t4.C
END
ELSE
BEGIN
insert into Test123
select * from #24 t8
END
SET @Counter=@Counter+1
END
drop table #t13
drop table #t24

But i am not getting proper result like if the the first is inserted and then it is not updating means it is adding that to the next one like below:-

A   B  C  F
100 A1 Hard Dece:100
100 A1  Hard Dece:100;jan:200
------------------------------

But in temp table i am getting proper result, when i tried to insert or update then the problem is occuring please help me.

The SQL isn't even valid... (It has `IF EXITS`, not `IF EXISTS`). Also, the trigger never references `inserted` or `deleted`, is called `trgInsert9` yet happens on but `INSERT` **and** `UPDATE`. The aliases don't help at all either, [Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3), and there's no formatting/whitespace to even begin to improve readability. @MitchWheat is right, this is a truly horrible trigger. I suggest we find out your real goal here and start again. — Larnu, Dec 05 '18 at 09:17
Why do you have just one row `200 A2 Hard Dec:250`? How do you group by these rows? — StepUp, Dec 05 '18 at 09:31
Don't do it. Storing multiple values in one column is just storing up problems for later. One value per row per column. That's a real simple rule. — Gordon Linoff, Dec 05 '18 at 12:43

score 1 · Answer 1 · answered Dec 05 '18 at 09:48

Your trigger is completely garbled. First you should learn how to format your sql scripts. I found this SQL Formatting standards title in SO. Please read it. There is no certain rule about formatting but the main rule is your query should be readable!

Also, you shouldn't put lots of calculation to your trigger. It can affect sql performance.

In my humble opinion; DELETE and then INSERT your related rows(to second table) works better than IF EXISTS() --> UPDATE , ELSE --> INSERT process.

I have created sample for you, you can check it :

--DROP TABLE Testing;

CREATE TABLE Testing
(
     A  INT
    ,B  VARCHAR(100)
    ,C  VARCHAR(100)
    ,D  VARCHAR(100)
    ,E  INT
);

--DROP TABLE Test123;

CREATE TABLE Test123
(
     A  INT
    ,B  VARCHAR(100)
    ,C  VARCHAR(100)
    ,F  VARCHAR(MAX)
);


--DROP TRIGGER [dbo].[trgInsert9] ;

CREATE TRIGGER [dbo].[trgInsert9] 
    ON /*[roll].*/[dbo].[Testing]
AFTER INSERT,UPDATE,DELETE
AS

DELETE FROM [dbo].Test123
WHERE EXISTS (
                SELECT 1
                FROM INSERTED I
                WHERE Test123.A = I.A
                AND Test123.B = I.B
                AND Test123.C = I.C
                )

DELETE FROM [dbo].Test123
WHERE EXISTS (
                SELECT 1
                FROM DELETED I
                WHERE Test123.A = I.A
                AND Test123.B = I.B
                AND Test123.C = I.C
                )

SELECT DISTINCT A,B,C 
INTO #List 
FROM INSERTED

INSERT INTO [dbo].Test123(A,B,C,F)
SELECT A,B,C,STUFF((SELECT ';' + CAST(D AS varchar) +':'+ CAST(E AS varchar) 
                    FROM Testing T  
                    WHERE T.A =L.A 
                    AND T.B=L.B
                    AND T.C=L.C
                    FOR XML PATH('')), 1 ,1, '') 
FROM #List L
GO

DELETE FROM Testing;
INSERT INTO Testing
VALUES
 (100,'A1','Hard','Dece',100)
,(100,'A1','Hard','Jan ',200)
,(100,'A1','SOFT','Jan ',200)
,(200,'A2','Hard','Dec ',250)
,(200,'A2','SOFT','Jan ',300);

SELECT * FROM Testing
SELECT * FROM Test123

How to check and update table in trigger in sql server

1 Answers1