USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trig_WriteDropfileOnChange] ON [dbo].[tableName]
FOR INSERT, UPDATE
AS

-- declare vars
DECLARE @RecordId bigint,
    @RetCode int,
    @FileSystem int,
    @FileHandle int,
    @MyDate varchar(255),
    @FileName varchar(255)

-- build filename
SET @MyDate = getDate()
SET @MyDate = REPLACE(@MyDate, ':', '')
SET @MyDate = REPLACE(@MyDate, ' ', '')
SET @MyDate = REPLACE(@MyDate, '-', '')
SET @MyDate = REPLACE(@MyDate, '.', '')

SELECT @RecordID = myfield
FROM INSERTED

SET @FileName = '\pathtodropdirectory' + @MyDate + '.txt'

EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject', @FileSystem OUTPUT
    IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0)
        RAISEERROR('could not create FileSystemObject', 16, 1)

EXECUTE @RetCode = sp_OAMethod @FileSystem, 'OpenTextFile', @FileHandle OUTPUT, @FileName, 2, 1
    IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
        RAISEERROR('Could not open file.', 16, 1)

EXECUTE @RetCode = sp_OAMethod @FileHandle, 'WriteLine', NULL, CONVERT(varchar, @RecordId)
    IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
        RAISEERROR('Could not write to file.', 16, 1)

EXECUTE @RetCode = sp_OAMethod @FileHandle, 'Close', NULL
    IF (@@ERROR|@RetCode > 0)
        RAISEERROR('Could not close file.', 16, 1)

EXEC sp_OADestroy @FileSystem