Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want to use a simple after insert trigger to get the last row, which was fireing the trigger, out of the database into a text file.

I already activated xp_cmdshell in SSMS.

But i struggle with:
SQL
CREATE TRIGGER tr_Test_I ON [dbo].[Employee_Test]
AFTER INSERT
AS
SELECT * FROM inserted

exec master..xp_cmdshell 'BCP <inserted> OUT test.txt -T -c';



Question update:
I want use this last row date to trigger a external printer job which needs the data of this table. OS and DB are 2008.
Posted
Updated 9-Nov-14 21:32pm
v3
Comments
PhilLenoir 6-Nov-14 11:32am    
BCP will not work in this way as "inserted" only has meaning within the confines of the trigger.

You could use CLR to write to the file system or use a log table as a vehicle for BCP, possibly clearing the log after the shell call returns (although you need to guard against concurrency issues).

Temporary tables will not work either as they will also be unavailable to BCP.
Tomas Takac 6-Nov-14 14:40pm    
What about a global temp table? That should work. Or a dedicated staging table.
PhilLenoir 6-Nov-14 14:48pm    
That's what I was getting at with the log table. Bulk copy will only see anything that's got a global context. You can pass dynamic SQL but not dynamic data. If Gert uses a global construct he needs to make sure that he doesn't have a concurrency issue. The call to xp_cmdshell is synchronous, but the write/reads to a global structure would not be. He could generate a key and pass that to BCP using a dynamic query to remove any possibility of collisions ... or use CLR (possibly the most straightforward solution).. There's not enough detail in the question to post a definitive solution (yet), although these comments might give him all the push he needs.
PhilLenoir 6-Nov-14 14:58pm    
Gert, BCP can't see "inserted". BCP establishes a new connection to SQL and the context of the trigger or anything it calls is not available to BCP and BCP cannot be transactional with the trigger.

If you were to insert into another table or have a field "logged" in the table concerned you could then either use BCP to log the contents of that other table (possibly cleaning it up as you go) or have BCP call a stored procedure that selects the data to be logged and cleans up within a transaction.

1 solution

Thanks a lot PhilLenoir, you got me on the right way.
 
My solution was:

1.: Made a VS2013 SQL Server project

C#
UserDefinedFunctions.WriteTextFile()


2.: Copy the dll to the DB.
3.: In SSMS setup DB, create an assembly with the dll and create a function

SQL
ALTER DATABASE SP_Test SET TRUSTWORTHY ON

CREATE ASSEMBLY DBTest
FROM 'C:\DBTest\Database2.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

CREATE FUNCTION [dbo].[WriteTextFile](@text [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [DBTest].[UserDefinedFunctions].[WriteTextFile]

4.: Updated the trigger from my question

SQL
ALTER TRIGGER [dbo].[tr_Test_I] ON [dbo].[Employee_Test]
AFTER INSERT
AS
	declare @someName varchar(100);


	select @someName=i.Emp_Name from inserted i;	


SELECT dbo.WriteTextFile(@empname);

5.: Tested it

Test one
SQL
SELECT dbo.WriteTextFile('hello sql-world');

Test two
SQL
Insert into [dbo].[Employee_Test] values ('Hello employee, this is the SQL world!')


I am a beginner in SQL so sorry if I did something wrong and I hope some other beginner can use this.

Regards
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900