Create trigger with update setting field through a function

Question

Create trigger with update setting field through a function

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I want to use a trigger to update the field of a table whenever there is an insert in this table.

The problem I'm having in the trigger is that the update does not work, it follows the 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, but it did not 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 have already identified that the problem is in the SET of update 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 this: can I use a function to set the field in an UPDATE command within an equal trigger I'm doing?

sql sql-server trigger

asked by anonymous 07.02.2018 / 01:25

3 answers

My insert does not register the data in mysql JAVA - Problem deploying by wso2server.bat

score 0 · Answer 1

It's possible, yes, Pedro, I've created a schematic here to show that it works. Here is the image below:

Notethatthefunctionreturnsthesamedatatypeasthefieldinquestion,whichintheexampleistypeINT.

NowseebelowwithanUPDATEinTRIGGERtoincrementonevalueateachINSERT:

In short, yes is possible! Hope this helps!!! Here is 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

score 0 · Answer 2

I needed to create a view, it is an embedded system, probably the order in which the tables are populated does not work with the ones I'm checking. In summary: I created the view with the requested data of the function and made the system start to query the view instead of consulting the original table with the update.

score 0 · Answer 3

-- 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

Suggested Reading: Traps in the programming of trigger procedures .