Creation of Trigger Sql Server

Question

I’m creating a Rigger to create a copy of the changed data in the table. But it presents an error.

USE [BANCO1]
GO
/****** Object:  Trigger [dbo].[BANCO1]    Script Date: 08/07/2015 14:13:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BANCO1]
  ON [dbo].[TABLE1]
  AFTER  UPDATE
AS


  BEGIN
    DECLARE
    @NOSSONRO   VARCHAR(15) 
    SELECT @NOSSONRO = E1_NUMBCO FROM UPDATED
    SET NOCOUNT ON;

    UPDATE TABLE1 SET E1_NOSNRO = @NOSSONRO  WHERE  E1_NOSNRO='' AND E1_NUMBCO=@NOSSONRO


  END

Answer 1

The way SQL Server handles events that trigger triggers (Trigger) can become a real trap for those who program the trigger procedure and ignore the specific details of SQL Server, because while several database managers trigger the trigger once for each line (for each Row), in SQL Server the trigger is triggered once for each DML statement (DELETE, INSERT, UPDATE).

-- código #1
ALTER TRIGGER [dbo].[BANCO1]
  ON [dbo].[TABLE1]
  AFTER  UPDATE as
begin
set nocount on;

UPDATE T
  set E1_NOSNRO = I.E1_NOSNRO
  from TABLE1 as T
       inner join INSERTED as I on I.E1_NUMBCO = T.E1_NUMBCO
  where T.E1_NOSNRO = '';

end;
go

Reading suggestion: Pitfalls in Trigger programming.

Answer 2

Between the Begin and the End of his trigger use the following code:

SET NOCOUNT ON;

DECLARE @NOSSONRO AS VARCHAR(15);

/*Código abaixo obtém o novo E1_NUMBCO, ou seja o valor atualizado. 
Se você quiser pegar o valor antigo do E1_NUMBCO utilize DELETED no lugar do INSERTED*/

SELECT @NOSSONRO = E1_NUMBCO FROM INSERTED;

UPDATE TABLE1 SET E1_NOSNRO = @NOSSONRO  WHERE  E1_NOSNRO='' AND E1_NUMBCO=@NOSSONRO

In Sql Server we do not have updated. updated is treated as an exclusion followed by an inclusion.