May 5, 2009 at 5:04 am
Hi All,
i'm in desperate need of some pointers, and thought you might be able to help.
i'm in need of a trigger that will output the entered data into either a XML, CSV or TXT file.
is this possible?
i have a trigger emailing the entered data.
any help is greatly appreciated.
Regards
Jason
May 5, 2009 at 7:16 am
can you provide a bit more information?
a trigger should do any work that is directly related to the data that is being inserted, Updated. or Deleted. it would fire one time for all the rows being affected. what happens if this trigger fires 100 times a minute/hour? are you sure you need absolutely instantaneous right-when-it-gets-inserted files created, or maybe you need a rollup of information instead?
what specific information do you want to send to a file?
the reason I ask, is it may be that you think you want to do something in a trigger, and it might be better served as a different process. By describing the desired process, we can offer better suggestions.
Are you trying to create an audit? does some other process need a "file" to read if something is Inserted?
Lowell
May 5, 2009 at 8:22 am
Thanks for the response Lowell,
hopefully i can explain a bit more, and hopefully i can make some sense....
i have an application that when a form is filled out, a new entry in the database is entered - and i'd like all the column information from that entry in a file - i'm not worried about the quantity, as it would be that much per hour if you see what i mean.
i use a trigger on another db to email me the contents of that entry and that works great - but for email only, and not file. here is the trigger that i currently use on a different DB...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trig_NewEventRegistration] ON [dbo].[cust_Forms]
FOR INSERT
AS
declare @body varchar(3000)
declare @Username varchar(1000)
declare @AfterText varchar(3000)
declare @AfterText2 varchar(3000)
declare @EmailAddress varchar(100)
declare @form varchar(100)
SELECT @Username = [KeyName] FROM inserted WHERE [KeyName] like 'YourName'
SELECT @EmailAddress = [KeyValue] FROM inserted WHERE [KeyName] like 'YourEmailAddress'
SELECT @form = [KeyValue] FROM inserted WHERE [KeyName] like 'Event'
SET @AfterText = ' Has Just Registered For The Event '
SET @AfterText2 = ' with An Email Address Of '
SET @body = @Username + @AfterText + @form + @AfterText2 + @EmailAddress
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test', @recipients = '.....', @body = @body, @subject = "Event New Registrant"
-------------------------------------------------------------------------------------
this works great for sending an email with all that i need, but i'd like to see if i can have this in a file, as i ahve another program that i want to kick of with this file, using the entered information.
Hope this is clear for everyone.
Many thanks
Jason
May 5, 2009 at 8:40 am
if you can give me the CREATE TABLe statement for your table ([cust_Forms] ? maybe)
I'll play with making a trigger; this is kind of interesting.
this part doesn't make sense:
I'm assuming you are expecting a single row of data in the INSERTED table, because you declared variables,
so how can KEYNAME ever be like two different strings:
also, if they are LIKE, should they have beginning or ending Percent Signs? otherwise it should be
[KeyName] = 'YourEmailAddress' and [KeyName] = 'Event'
SELECT @EmailAddress = [KeyValue] FROM inserted WHERE [KeyName] like 'YourEmailAddress'SELECT @form = [KeyValue] FROM inserted WHERE [KeyName] like 'Event'
Lowell
May 5, 2009 at 8:49 am
Hi Lowell,
glad you find it interesting - i'm getting stressed - he he
hopefully this is what you are after.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[cust_Forms](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PostID] [int] NULL,
[FormID] [int] NULL,
[KeyName] [nvarchar](200) NULL,
[KeyValue] [varchar](50) NULL,
[DT] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[cust_Forms] ADD CONSTRAINT [DF_cust_Forms_DT] DEFAULT (getdate()) FOR [DT]
GO
Cheers
jason
May 5, 2009 at 9:18 am
ok the below works; someone else may have other ideas.
basically there is a function which calls SPOaCreate to make a file system object, to write/append to a file. that means you might need to use the surface area configuration to allow a command prompt and ole.
it seemed to be pretty quick on my machine.
my trigger just slaps a string together and sends it to teh function; note that you can write to a single file, or create a file on the fly, but to make it unique, you really need to use GETDATE to construct the filename like i did.
hopefully you can use the code as an example, and just create a string based on your old trigger to just pass a string to the function.
CREATE function dbo.Ufn_WriteToFile
(
@FileName varchar(1000), @Text1 varchar(1000)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @status VARCHAR(100), @eof VARCHAR(10)
SET @status = 'SUCCESS'
DECLARE @FS int, @OLEResult int, @FileID int
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult 0
SET @status= 'Error: Scripting.FileSystemObject'
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@FileName, 8, 1 -- Append if required (8)
--execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@FileName, 1
IF @OLEResult 0
SET @status ='Error: OpenTextFile'
--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult 0
SET @status= 'Error : WriteLine'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
RETURN @status
END
GO
create table EXAMPLE(
body varchar(3000),
Username varchar(1000),
AfterText varchar(3000),
AfterText2 varchar(3000),
EmailAddress varchar(100),
Form varchar(100) )
GO
CREATE TRIGGER TR_EXAMPLE_FILEMAKER ON EXAMPLE
FOR INSERT
AS
BEGIN
DECLARE @TEXT VARCHAR(MAX),
@FileName varchar(100)
--C:\SomePath\MyFormLog-20090505-110451233.txt
SET @FileName = 'C:\SomePath\MyFormLog-' + CONVERT(VARCHAR,getdate(),112) + '-' + REPLACE(CONVERT(VARCHAR,getdate(),114),':','') + '.txt'
SET @TEXT = ''
--CHAR(9) is tab delimited, swap to comma for CSV
SELECT @TEXT = @TEXT + Username + CHAR(9)
+ EmailAddress + CHAR(9)
+ AfterText + CHAR(9)
+ AfterText2 + CHAR(9)
+ body + CHAR(9)
+ body + CHAR(9)
+ CONVERT(VARCHAR,getdate(),112) + '-' + CONVERT(VARCHAR,getdate(),114)
+ CHAR(13) + CHAR(10) --vbCrLf
FROM INSERTED
--to append, simply use the same file name, otherwise, use a uniquename:
print @TEXT
SELECT DBO.Ufn_WriteToFile('c:\MyFormLog.txt',@TEXT)
--or
--SELECT DBO.Ufn_WriteToFile(@FileName,@TEXT)
END
GO
INSERT INTO EXAMPLE(body,Username,AfterText,AfterText2,EmailAddress,Form)
SELECT 'Web form request','Bob','Has Just Registered For The Event1','with An Email Address Of' ,'bob@somesite.com','form data'
UNION ALL
SELECT 'Web form request','jeff','Has Just Registered For The Event2','with An Email Address Of' ,'jeff@somesite.com','form data'
Lowell
May 5, 2009 at 9:47 am
wow, that was quick....
thanks Lowell - i'll give this a try, i'm fairly new to SQL, but i think i can make out most of it.....
i'll let you know how i get on.
Jason
May 5, 2009 at 9:55 am
it was quick just because I've already got an write-to-file example in my code snippets; i knew that worked, it was just calling it in a trigger instead of a stored proc, like my saved example had.
Hope it works for oyu.
Lowell
May 8, 2009 at 12:12 pm
In addition to Lowell's solution above, the other options include:
--creating a CLR UDF to dump files (fastest solution)
--creating an audit table to store the changes and then using SSIS task to dump to files as needed (most comprehensive solution)
I've implemented both options at various times depending on circumstances. If a history trail has to be maintained, then the audit option serves well. Both of these require additional access right to the server that Lowell's solution avoids.
May 8, 2009 at 1:32 pm
I recommend against doing something like creating a file from a trigger. Why? Because the trigger is part of the transaction and if your trigger fails for any reason then the entire transaction is rolled back.
This is something that you may want to investigate using Service Broker for as it will work asynchronously and thus not rollback the transaction if there is a problem writing the file.
Since you are new to SQL Server you may want to read this article[/url] about triggers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply