SQL Trigger to output to a text file

[pr33tz]

Hi,

I've been using SQL Server 2000 for a short while now and want to know if the following is possible. I've got two tables, Customer table and an Address table. I want to create a trigger that, when a new customer is added or amended, will output the changes to a text file. For example, if Mr Smith's address is created, it will pick up his name from the Customer table and his address from the Address table (linked by Cutomer_ID) and output the results to a text file in a '0001, Mr Smith, 12, Temple Road, London, NW2 4ET, 02084521234' format or similar.

I think this is possible for a single table but not sure about multiple tables. Also, any examples or guides would be very helpful!!

Thanks in advance.

[iburyak]

Unfortunately it is not a solution it is jut a food to chew on and find your own way to do it.

--1 Create tables.
Create table Customer(CustID int, CustName varchar(50))
Create table Address(CustID int, Address varchar(4000))

--2. Insert Customer data
insert into Customer values (1, 'pr33tz')

--3. Create ouput table
Create table CustomerInfo(CustID int, CustName varchar(50), CustAddr varchar(4000))

--4. Create a trigger

CREATE TRIGGER tI_Address ON Address FOR INSERT AS
BEGIN

Insert into CustomerInfo
Select i.CustID, c.CustName, i.Address
From inserted i
join Customer c on i.CustID = c.CustID

END


--5. Try to insert data into Address table and see results.

insert into Address values(1, 'Some address')

select * from CustomerInfo

--6. Insert data into a text file in a separate scheduled job. You can run it every 5 min if you wish.
-- I tried to do it in a trigger but process that does insert into a table blocks Command line from accessing
-- the same table from a different process ID which is the case when you execute xp_cmdshell

BEGIN
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = 'c:\query_result' + replace(replace(convert(varchar(20), getdate()),' ','_'),':','-') + '.txt'

select @FileName

SET @bcpCommand = 'bcp "select * from database_name..CustomerInfo " queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U UID_here -P PWD_here -c'

EXEC master..xp_cmdshell @bcpCommand
-- delete everything you already saved into a file.
Delete * from CustomerInfo
END

[pr33tz]

Thanks, that has partly worked. Is it possible to have the text file display the result in a csv format? i.e., "cust id","address","tel_no"

[iburyak]

Basically I am trying to forge csv file here by adding quotes and commas. Try if it works for you.

[PHP]BEGIN
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = 'c:\query_result' + replace(replace(convert(varchar(20), getdate()),' ','_'),':','-') + '.csv'

select @FileName

SET @bcpCommand = 'bcp "select char(34)+string_column+char(34)+char(44)+char(34)+ convert(varchar(10),Int_or_date_column) + char(34) from database_name..CustomerInfo " queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U UID_here -P PWD_here -c'

EXEC master..xp_cmdshell @bcpCommand
-- delete everything you already saved into a file.
Delete * from CustomerInfo
END[/PHP]