CODEWITHSUNDEEP
sql-servertriggers
sakana
sakana

Reputation: 4481

SQL Server: trigger updating a just inserted row

I currently working with an old application that I don't own any source code but I really need to add some new logic to the app.

The only way I found to do it is to mess up with database data, and change whatever I need with a trigger.

I wrote the following trigger that should update 2 columns of a just inserted row:

CREATE TRIGGER addLoteInfo ON fi
FOR INSERT
AS 
DECLARE
    @loteN          varchar(15),
    @refN           varchar(18),
    @CientifN               varchar(40),
    @CaptureZ       varchar(20)


    declare MY_CURSOR cursor for 
    select   
        i.lote, i.ref
    from inserted i

    open MY_CURSOR

    fetch next from MY_CURSORinto @loteN, @refN

    while @@fetch_status = 0  
    begin 

         SELECT @CientifN = u_ncientif FROM SE where lote LIKE @loteN and ref LIKE @refN;

         SELECT @CaptureZ = u_zcaptura FROM SE where lote LIKE @loteN and ref LIKE @refN;

        UPDATE FI SET LNCIENT=@CientifN, LZCAP=@CaptureZ;

        fetch next from CURSOR_TESTE into @loteN, @refN 

    end 

    close MY_CURSOR
    deallocate MY_CURSOR

The problem is when a new registry is inserted, it seems it gets to a deadlock.

Its impossible to do what I'm trying to do?

Can you help me with another approach?

Upvotes: 0

Views: 923

Answers (1)

a1ex07
a1ex07

Reputation: 37354

You need to use INSTEAD OF trigger if you want to modify newly inserted/updated record on the fly.

Upvotes: 1

Related Questions