Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trigger - Access to filepath? Expand / Collapse
Author
Message
Posted Friday, February 18, 2011 10:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 18, 2011 7:00 PM
Points: 2, Visits: 6
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.

Post #1066517
Posted Friday, February 18, 2011 1:00 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:56 AM
Points: 317, Visits: 877
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





Colleen M. Morrow
Cleveland DBA
Post #1066598
Posted Friday, February 18, 2011 3:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 18, 2011 7:00 PM
Points: 2, Visits: 6
Well damn. I guess I'll take that as a "Yes."

Much more than I asked for and much appreciated.

Post #1066686
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse