February 18, 2011 at 10:50 am
I need to create a trigger event (on create) that will, based on values in the new row, open a matching text file, read in it's contents and append them to a field in that row.
For example, upon adding the row: Smith, John, 12345 the trigger would look for a file "12345.txt" on d:\data\files read in it's contents and append them back into the same row.
Is it possible for a trigger to do this or am I barking up the wrong tree? Thanks.
Thanks.
February 18, 2011 at 1:00 pm
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
February 18, 2011 at 3:22 pm
Well damn. I guess I'll take that as a "Yes." 🙂
Much more than I asked for and much appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply