USE AdventureWorks;

GO

CREATE TABLE myTable(

FileName nvarchar(60),

FileType nvarchar(60),

Document nvarchar(max));

GO

ALTER TRIGGER myTrig

ON myTable

INSTEAD OF INSERT

AS

DECLARE @filename nvarchar(60);

DECLARE @filepath nvarchar(60);

DECLARE @sqlstmt nvarchar(4000);

DECLARE @document nvarchar(max)

SELECT @filename = Filename FROM inserted ;

SET @filepath = 'D:\' + @filename +'.txt';

set @sqlstmt = 'SELECT @docout = BulkColumn FROM OPENROWSET(BULK ''' + @filepath + ''', SINGLE_CLOB) AS Document;'

select @sqlstmt

exec sp_executesql @sqlstmt, N'@docout nvarchar(max) OUTPUT', @docout=@document output

INSERT INTO myTable select i.filename, i.filetype, @document from inserted i

GO