perform an update type trigger

Question

perform an update type trigger

Navigation

#1 by (0 votes)

0

I have the following query: Realize a trigger of type update in the table "x" and verify if the new data of the name and appelled already exist, if they do not change, otherwise modify the information.

create trigger ActualizarInfo
on tabla1
for update
as
  begin 
       declare @nombre varchar(30)
               @apellido varchar (30)

     if exists (name=@nombre and lastname=@apellido)
        print 'usuario existente, no modificar'

   end
   else 
      begin
           update tabla1
           set name=@nombre
           set lastname=@apellido

end

My question is: if I can handle my variables that I declare (@ name, @ last name)? or as valid if they exist or not?

sql sql-server

asked by m3nt0r1337 10.08.2016 в 22:51

source

1 answer

Problem CalendarView in extends (navigation drawer) Fragment? How do I do the following copy a column of a table to another table

score 0 · Accepted Answer

First of all, what you need is a INSTEAD OF trigger, because you should check the changes before that the data is modified, not later. And then you should understand how triggers work in SQL Server.

You always have to think that when you make a INSERT , UPDATE or DELETE , you should think that you are going to modify a set of data, and not just a single row (even when the particular operation is a single row, you must assume that they can be modified more than one at a time). This is why the solution can not be assigning data to simple variables, because that assumes that a single row was always affected.

Also, in a trigger you can use the pseudo-tables INSERTED and DELETED , which have all the rows that were affected by your operation. It is with these tables that you should compare the information:

CREATE TRIGGER [dbo].[ActualizarInfo]
   ON  [dbo].[Tabla1]
   INSTEAD OF UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE A
    SET A.Name      = ISNULL(A.Name,B.Name),
        A.LastName  = ISNULL(A.LastName,B.LastName)
    FROM dbo.Tabla1 A
    INNER JOIN INSERTED B
        ON A.ColumnaLlave = B.ColumnaLlave
    WHERE A.Name IS NULL
    OR A.LastName IS NULL;

END