Trigger in SQL server

Question

Trigger in SQL server

Navigation

#1 by (2 votes)

2

I wanted to do a query of triggers in SQL Server. I do not know if it will be complex but I am needing to solve the following:

The LINKED CASES column I need to be a field calculated through the execution of a trigger, every time a record is inserted, updated or deleted. I do not know how to do it because the trigger should go through all the records of the same surname and of the same name and in the column CASES Linked place a string of text that is equal to the numbers of cases in which the same surname is associated with the same name.

In the table that I hit here, it would be how the result should look.

sql trigger

asked by Gustavo 16.06.2016 в 13:53

source

1 answer

Paginacion en php Error copying a file in java

score 2 · Answer 1

If your base table is not that big, you do not need a trigger to keep the column, but you can perform the calculation on the fly in a query, which you can also store as a view so as not to repeat yourself.

The basic idea is to pull the clause for xml to concatenate in a column all the rows that show matches.

One example is worth a thousand words:

with
Caso as (
          select cast('20121130' as date) FechaAlta, 17853 Caso, 'AMENDOAIN' Apellido, 'Jose Abel' Nombre
union all select '20121130', 28080, 'AMENDOAIN', 'Jose Abel'
union all select '20121130', 36035, 'Estevez', 'Francisco'
union all select '20130607', 43868, 'Estevez', 'Francisco'
union all select '20121130', 31107, 'Fernandez', 'Pablo'
union all select '20121130', 31109, 'Fernandez', 'Pablo'
union all select '20121130', 30563, 'Fernandez', 'Roberto'
union all select '20121130', 30995, 'Fernandez', 'Roberto'
union all select '20121130', 31005, 'Fernandez', 'Roberto'
union all select '20140402', 65791, 'Gutierrez', 'Isabel'
union all select '20121130', 35006, 'Jimenez', 'Omar'
union all select '20140829', 55013, 'Jimenez', 'Omar'
union all select '20160513', 74159, 'Jimenez', 'Omar'
union all select '20150605', 54386, 'Rancedo', 'Lucia'
union all select '20160914', 50973, 'Villar', 'Anahi'

)
select   *
       , stuff(
          (select ', ' + cast(caso as varchar(20)) 
            from Caso c2
           where c2.Apellido = c1.Apellido  
             and c2.Nombre = c1.Nombre
           order by Caso
             for xml path('')
          ), 1, 2, '') as CasosVinculados
  from Caso c1

The result obtained is:

FechaAlta  Caso        Apellido  Nombre    CasosVinculados
---------- ----------- --------- --------- ---------------------
2012-11-30 17853       AMENDOAIN Jose Abel 17853, 28080
2012-11-30 28080       AMENDOAIN Jose Abel 17853, 28080
2012-11-30 36035       Estevez   Francisco 36035, 43868
2013-06-07 43868       Estevez   Francisco 36035, 43868
2012-11-30 31107       Fernandez Pablo     31107, 31109
2012-11-30 31109       Fernandez Pablo     31107, 31109
2012-11-30 30563       Fernandez Roberto   30563, 30995, 31005
2012-11-30 30995       Fernandez Roberto   30563, 30995, 31005
2012-11-30 31005       Fernandez Roberto   30563, 30995, 31005
2014-04-02 65791       Gutierrez Isabel    65791
2012-11-30 35006       Jimenez   Omar      35006, 55013, 74159
2014-08-29 55013       Jimenez   Omar      35006, 55013, 74159
2016-05-13 74159       Jimenez   Omar      35006, 55013, 74159
2015-06-05 54386       Rancedo   Lucia     54386
2016-09-14 50973       Villar    Anahi     50973

(15 row(s) affected)