Call function with Trigger

No clear what your goal is. But here is a small example. The trigger will call a scalar function after insert to manipulate a string and save it back into a table.

Table Name: dbo.table_1

function

IF OBJECT_ID (N'dbo.ufn_TestFunction', N'FN') IS NOT NULL
DROP FUNCTION dbo.ufn_TestFunction;
GO
CREATE FUNCTION dbo.ufn_TestFunction (@param VARCHAR(50))
RETURNS VARCHAR(250)
WITH EXECUTE AS CALLER
AS
BEGIN
RETURN (@param + '-' + CONVERT(VARCHAR(30),GETDATE(),120))
END
GO

Trigger

IF EXISTS (
SELECT *
FROM sys.objects
WHERE [type] = 'TR' AND [name] = 'trg_Test'
)
DROP TRIGGER trg_Test;
GO
CREATE TRIGGER dbo.trg_Test
ON dbo.table_1
AFTER INSERT
AS
BEGIN
UPDATE dbo.table_1
SET Column1 = dbo.ufn_TestFunction(i.Column1)
FROM dbo.table_1
INNER JOIN inserted i on i.Id = dbo.table_1 .Id
END
GO

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Bryian Tan9y ago
No clear what your goal is. But here is a small example. The trigger will call a scalar function after insert to manipulate a string and save it back into a table.

Table Name: dbo.table_1

function
IF OBJECT_ID (N'dbo.ufn_TestFunction', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ufn_TestFunction;
GO
CREATE FUNCTION dbo.ufn_TestFunction (@param VARCHAR(50))
RETURNS  VARCHAR(250)

WITH EXECUTE AS CALLER
AS

BEGIN
    RETURN (@param + '-' +  CONVERT(VARCHAR(30),GETDATE(),120))
END
GO
Trigger
IF EXISTS (
    SELECT *
    FROM sys.objects
    WHERE [type] = 'TR' AND [name] = 'trg_Test'
    )
    DROP TRIGGER trg_Test;
GO

CREATE TRIGGER dbo.trg_Test
ON  dbo.table_1
AFTER INSERT
AS
BEGIN
    UPDATE dbo.table_1
    SET Column1 = dbo.ufn_TestFunction(i.Column1)
    FROM dbo.table_1
    INNER JOIN inserted i on i.Id = dbo.table_1  .Id
END
GO
0