Trigger insert, update

Question

Trigger insert, update

Navigation

#1 by (2 votes)

1

Hi, can you help me with a trigger? I want that, if some data of the classifications table is moved, an insert is made if the data does not exist in the history table or, if it exists, only update the data.

I have the code as follows:

 CREATE TRIGGER test
ON [dbo].[CLASIFICACIONES]
AFTER UPDATE, INSERT
AS
BEGIN
SET NOCOUNT ON;

--DECLARE @Activity  NVARCHAR (50)

-- update
    IF EXISTS (SELECT C1, C2, C4, C3, '2', '3', C5  FROM inserted) AND EXISTS (SELECT C1, C2, C4, C3, '2', '3', C5  FROM deleted)
    BEGIN
        UPDATE KDIHCS SET C4=C3, C5='1', C6='2', C7=C5 
    END

    -- insert
    IF EXISTS (SELECT C1, C2, C4, C3, '2', '3', C5 FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
    BEGIN
        INSERT INTO HISTORIAL
        SELECT C1, C2, C4, C3, '2', '3', C5 FROM INSERTED
    END
END

SO I WAS TAKING IT BUT IT DOES NOT WORK YET GOOD

The problem is that I can not update if I already have data added or inserted.

sql-server

asked by Javier Solis 20.11.2018 в 20:31

source

1 answer

How to save an image from vue How to get classes that are not repeated from a Nodelist?

score 2 · Accepted Answer

If when inserting in table A you want to insert in table B, and when updating in table A, you want to update in table B, then I would recommend making several changes regarding the trigger published in Your question:

Since the trigger only triggers after insert or update , to determine if it is a update it is enough to see if there is any data in the pseudo table deleted . If there is not, we can be sure that it is a insert .
It must be taken into account that it can be a massive operation, since in SQL Server , triggers are not launched for each row that is inserted, modified or delete. They are launched only once, even if the instruction is changing or inserting multiple records, therefore, the strategy of insert / update must take this into account.
To update, I use the update/from instruction that is unique to SQL Server , but it makes me very comfortable in these cases. If you are not familiar with it, I recommend your study.
To insert, I do a insert/select , so I insert all the records returned by select .

With this in mind, the trigger would look something like this:

CREATE TRIGGER test
ON [dbo].[CLASIFICACIONES]
AFTER UPDATE, INSERT
AS
BEGIN
  SET NOCOUNT ON;
  if exists (select 1 from deleted) 
  begin
    --es un update, actualizamos:
    update b
       set   c3 = i.c3
           , c4 = i.c4
           , c5 = i.c5
           , c6 = i.c6
      from inserted i
           inner join KDIHCS b on b.c1 = i.c1 and b.c2 = i.c2;
  end
  else 
  begin
    --es un registro nuevo, también lo insertamos
    insert into KDIHCS (c1, c2, c3, c4, c5, c6)
    select c1, c2, c3, c4, c5, c6
      from inserted;
  end;
  set nocount off;
END
go

It may be that the names of the fields do not match, I do not have the structure of your tables at hand. The idea is that you analyze the proposed solution and adapt it to your particular case.