Create Trigger with update setando field through a function

Question

I’m looking to use a trigger to update the field of a table whenever there is an Insert in this same table.

The problem I’m having at Trigger is that the update doesn’t work, it follows Trigger:

CREATE TRIGGER descNF ON tabela1
AFTER INSERT AS
BEGIN
 DECLARE @IDMOV VARCHAR(MAX)
 SET @IDMOV = (SELECT IDMOV FROM INSERTED)
 UPDATE tabela1 SET tabela1.HISTORICOLONGO = dbo.f_descricao(@IDMOV) WHERE IDMOV = @IDMOV
END

I’ve tried to make some changes, like this one, but it didn’t work:

CREATE TRIGGER descNF ON tabela1
AFTER INSERT AS
BEGIN
    DECLARE @IDMOV VARCHAR(MAX)
    SET @IDMOV = (SELECT IDMOV FROM INSERTED)
    DECLARE @TEXTO VARCHAR(MAX)
    SET @TEXTO = dbo.f_descriptionNFSe(@IDMOV)
    UPDATE tabela1 SET tabela1.HISTORICOLONGO = @TEXTO WHERE IDMOV = @IDMOV
END

I already identified that the problem is in the update SET with a function, because when I do this:

CREATE TRIGGER descNF ON tabela1
AFTER INSERT AS
BEGIN
    DECLARE @IDMOV VARCHAR(MAX)
    SET @IDMOV = (SELECT IDMOV FROM INSERTED)
    UPDATE tabela1 SET tabela1.HISTORICOLONGO = 'TESTE' WHERE IDMOV = @IDMOV
END

It works and performs the update.

Removing the Trigger update and running as a common UPDATE as below:

DECLARE @IDMOV VARCHAR(MAX)
SET @texto = dbo.f_descriptionNFSe(123)
UPDATE tabela1 SET tabela1.HISTORICOLONGO = @texto WHERE IDMOV = 123

OU 

UPDATE tabela1 SET tabela1.HISTORICOLONGO = dbo.f_descriptionNFSe(123) WHERE IDMOV = 123

It also works, so my question is the following: It is possible to use a function to set the field in an UPDATE command within a Rigger like I am doing?

Answer 1

-- código #1
CREATE TRIGGER dbo.descNF 
    on dbo.tabela1
    after INSERT AS
begin

-- verifica número de linhas a tratar
declare @NL int;
set @NL= (SELECT count(*) from (SELECT top (2) * from INSERTED) as I);

-- encerra o processamento se não há linha para tratar
IF @NL = 0 return;

-- 
UPDATE T1
  set HISTORICOLONGO= dbo.f_descricao(T2.IDMOV) 
  from dbo.tabela1 as T1
       inner join INSERTED as T2 on T1.IDMOV = T2.IDMOV;
END;
go

Reading suggestion: Traps in trigger procedure programming.

Answer 2

It is possible yes Pedro, I created a quick scheme here to show that it works. Follow image below:

Noting that the Function returns the same type of data from the Field in question as in the example is type INT.

Now see below with an UPDATE in TRIGGER to Increment a value at each INSERT:

In short, yes is possible! I hope I helped!!! Below is the code used in the image:

CREATE TABLE Tabela01(
    ID INT IDENTITY PRIMARY KEY NOT NULL,
    Nome NVARCHAR(MAX)
)
GO


CREATE TABLE Tabela02(  
    Total INT
)
GO


CREATE FUNCTION Somar(@N1 INT, @N2 INT)
RETURNS INT
AS
BEGIN
    RETURN @N1 + @N2
END
GO


CREATE TRIGGER UsandoFuncao ON Tabela01
AFTER INSERT
AS
BEGIN
    DECLARE @Resultado INT
    DECLARE @QTD INT

    SELECT @Resultado = [dbo].[Somar] (10, 10)
    SELECT @QTD = COUNT(*) FROM Tabela02

    IF @QTD < 1 
        INSERT Tabela02 VALUES([dbo].[Somar] (10, 10))
    ELSE
        UPDATE Tabela02 SET Total += [dbo].[Somar] (10, 10)
    --UPDATE Tabela02 SET Total = @Resultado
END
GO


INSERT INTO Tabela01 VALUES('Paulo2')
GO

SELECT * FROM Tabela01
GO
SELECT * FROM Tabela02
GO

Answer 3

I needed to create a view, it’s an embedded system, probably the order in which the tables are populated doesn’t work with the ones I’m checking. In summary: I created the view with the searched function data and made the system start to query the view instead of the original table with the update.