插入触发器导出后的MS SQL插入到文件中 [英] MS SQL after insert trigger export inserted to file

问题描述

我想使用一个简单的插入触发器来将最后一行（触发触发器）从数据库中导出到文本文件中。



I已经在SSMS中激活了xp_cmdshell。



但是我很难：

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:

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';

问题更新：

我想使用这个最后一行日期来触发需要该表数据的外部打印机作业。 OS和DB是2008.

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.

推荐答案

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

1.: Made a VS2013 SQL Server project

UserDefinedFunctions.WriteTextFile()

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

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

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

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

测试二

Test two

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

我是SQL的初学者，很抱歉，如果我做错了，我希望其他初学者可以使用它。



问候

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

Regards

这篇关于插入触发器导出后的MS SQL插入到文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！