Trigger SQL Server insert record if certain field is updated

Question

Trigger SQL Server insert record if certain field is updated

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

Good afternoon colleagues I have this trigger to insert a record but only if the teststatus field changes, but even if there are other fields that are updated I still insert the record someone knows why

create trigger trTestStatusInsertarAct on test
    after update
    as
    begin
        IF UPDATE (TestStatus)
        begin
            insert into estadoproceso (ID ,proceso , TestStatus ,Engineer,FirstDate)
            select ID ,proceso , TestStatus ,Engineer,GETDATE()
            from inserted
        end
    end

sql sql-server insert trigger

asked by Raul.Sen 28.06.2017 в 22:54

source

2 answers

0

A valid alternative could be

CREATE TRIGGER trTestStatusInsertarAct ON test
    AFTER UPDATE
AS
    BEGIN

        INSERT  INTO estadoproceso
                ( ID ,
                  proceso ,
                  TestStatus ,
                  Engineer ,
                  FirstDate
                )
                SELECT  ID ,
                        proceso ,
                        TestStatus ,
                        Engineer ,
                        GETDATE()
        SELECT  i.*
        FROM    inserted i
                INNER JOIN deleted d ON d.id = i.id
                                        AND d.TestStatus != i.TestStatus 

/*Si test status la subconsulta no trae nada, no insera*/

    END

answered by 29.06.2017 в 18:06

Problems grouping with ArrayList Why does not the function run on the first click in this case?

score 1 · Accepted Answer

--Creamos 2 tablas de prueba
create table Prueba (Id tinyint identity, Texto varchar(20), TestStatus char(1))
create table PruebaDestino (Id tinyint, Texto varchar(20), TestStatus char(1), Fecha date)

--Insertamos valores de prueba
insert into Prueba (Texto, TestStatus) 
values ('Texto 1', 'A'), ('Texto 2', 'A'), ('Texto 3', 'A'), ('Texto 4', 'A'), ('Texto 5', 'A')


alter trigger trTestStatusInsertarAct on Prueba
after update
as
begin
    declare @TestStatus char(1),
            @Id TinyInt,
            @Texto varchar(20)

    if update (TestStatus)
    insert into PruebaDestino (Id, Texto, TestStatus, Fecha)
        select i.Id, i.Texto, i.TestStatus, Getdate()
        from inserted i  
        inner join Prueba p
          on p.id = i.id
end


--Pruebas de actualizaciones
update Prueba
set TestStatus = 'x'
where Id = 5

update Prueba
set Texto = 'Texto 5'
where Id = 5

--Validaciones
select *
from PruebaDestino