user1170330 - 1 month ago 4x

SQL Question

Suppose I have the following table

`T1`

`| col1 | col2 |`

|------|------|

| 0 | 0 | // A++

| 3 | 123 | // B++

| 0 | 5 | // C++

| 8 | 432 | // A++

| 0 | 4 | // B++

I now need to create a trigger (on

`INSERT`

`T2`

`IF col1 = 0 AND col2 = 0`

A++

ELSE IF col1 = 0 col2 > 0

B++

ELSE IF col1 > 0

C++

In this case,

`T2`

`| id | A | B | C |`

|----|---|---|---|

| 1 | 1 | 2 | 2 |

My question is more about the design: Should I really iterate through each row, as described HERE, or is there a more efficient way?

Answer

Try something like this in trigger

```
;with data as
(
SELECT Sum(CASE WHEN col1 = 0 AND col2 = 0 THEN 1 END) AS a,
Sum(CASE WHEN col1 = 0 AND col2 > 0 THEN 1 END) AS b,
Sum(CASE WHEN col1 > 0 THEN 1 END) AS c
FROM (VALUES (0, 0 ),
(3, 123 ),
(0, 5 ),
(8, 432 ),
(0, 4 ) ) tc ( col1, col2 )
)
UPDATE yt
SET a = dt.a,
b = dt.b,
c = dt.c
FROM yourtable yt
JOIN data dt
ON a.id = b.id
```

This does not require row by row iteration. Replace the table valued constructor with `Inserted`

table

Source (Stackoverflow)

Comments