We have a website under development that maintains a database. This website is replacing classic ASP pages and FoxPro applications along with new development written in ASP.NET 3.5 (mostly VB, a little C# and some AJAX). However, there are other systems that update our database.
我们正在开发一个维护数据库的网站。这个网站取代了经典的ASP页面和FoxPro应用程序，并在ASP中编写了新的开发。NET 3.5(主要是VB、一些c#和一些AJAX)。但是，还有其他系统更新我们的数据库。
Because of those systems that do things like updating statuses in our data, we have a few triggers in the database (SQL Server 2005). Usually it's something simple that is handled in SQL but now we have something else.
由于这些系统可以更新数据中的状态，所以我们在数据库中有一些触发器(SQL Server 2005)。通常它是用SQL处理的一些简单的东西，但是现在我们有了其他的东西。
Our users will be uploading and storing a file (Excel spreadsheet) that will have updates for the database that should only be processed when an external system gives the go-ahead (i.e. when the trigger is fired). The file will be stored as any other 'attachment' (our database has pointers to files in particular directories) since it needs to be verified up front (long before it would actually be processed).
我们的用户将上传并存储一个文件(Excel电子表格)，该文件将对数据库进行更新，而该数据库只有在外部系统提供许可时才会进行处理(即触发触发器时)。该文件将被存储为任何其他“附件”(我们的数据库有指向特定目录的文件的指针)，因为它需要预先验证(在实际处理之前很久)。
What we want to do is execute the same method that 'verifies' the file (except this time it will actually process the updates) when the SQL trigger fires.
我们要做的是在SQL触发器触发时执行与“验证”文件相同的方法(除了这次它将实际处理更新)。
Is there a relatively uncomplicated way of doing this?
有相对简单的方法吗?
4 个解决方案
#1
3
It's been done before you have to use the CLR Stored Procdure...
这是在你使用CLR存储产品之前完成的。
See here for an example http://www.codeproject.com/KB/database/Managed_Code_in_SQL.aspx
这里有一个示例:http://www.codeproject.com/KB/database/Managed_Code_in_SQL.aspx。
Microsoft also has some CLR examples in there SQL Server Database Engine Samples on codeplex.
微软在codeplex上也有一些CLR示例SQL Server数据库引擎示例。
Good luck getting DBA sign off.
祝DBA好运。
Another option is to use the xp_cmdshell and execute a "command" on the naitive system, which could then call your .net code. Yet again.. good luck with DBAS ;)
另一个选项是使用xp_cmdshell并在naitive系统上执行“命令”，该系统随后可以调用.net代码。又一次. .祝dba好运!)
Other than those two I can't think of any other "simple" ways to do it. There are tons of complicated ways to do it..
除了这两种方法，我想不出其他任何“简单”的方法。有很多复杂的方法可以做到。
#2
1
Without enabling CLR on your SQL Server you can't run SQL CLR stored procs and triggers on it.
如果在SQL服务器上不启用CLR，就不能在它上运行SQL CLR存储的proc和触发器。
Meanwhile SQL CLR trigger is the best solution for your task
同时，SQL CLR触发器是您的任务的最佳解决方案
#3
0
I've done some fuzzy searching in assemblies on the database. It is very tricky. First of all you can not use the .NET 2.0 extentions (V3.0, V3.5, V4.0) depending on the SQL Server Version. Secondly you have to make sure that you only use managed code. No COM-Interops etc.
我在数据库的程序集中做了一些模糊搜索。这是非常棘手的。首先，根据SQL Server版本，您不能使用。net 2.0扩展(V3.0、V3.5、V4.0)。第二，必须确保只使用托管代码。没有COM-Interops等等。
I would think of a better solution since the triggers should be very fast and processing an excel can be very slow. How about an external process that polls on that table?
我想要一个更好的解决方案，因为触发器应该非常快，处理excel可能非常慢。那么在这个表上进行轮询的外部过程呢?
#4
0
I would have the trigger put an entry into a queue for processing and have the queue monitored actively for actions which need to be performed. This could be as simple as a table or as complex as a web service call. But it should be quick.
我将让触发器将一个条目放入队列中进行处理，并让队列主动监视需要执行的操作。这可以像表一样简单，也可以像web服务调用一样复杂。但它应该很快。
Typically, you don't want trigger operations to be lengthy or involve higher-level business logic. Triggers should be used to maintain the database in a state so that the database can provide consistent services to all database users (so low-level business logic in triggers is still fine). Once you get to application-level business logic, you really don't want that tightly coupled into your database.
通常，您不希望触发器操作太长或涉及高级业务逻辑。应该使用触发器将数据库保持在一种状态，以便数据库能够为所有数据库用户提供一致的服务(因此触发器中的低级业务逻辑仍然良好)。一旦进入应用程序级的业务逻辑，您真的不希望将其紧密耦合到数据库中。
#1
3
It's been done before you have to use the CLR Stored Procdure...
这是在你使用CLR存储产品之前完成的。
See here for an example http://www.codeproject.com/KB/database/Managed_Code_in_SQL.aspx
这里有一个示例:http://www.codeproject.com/KB/database/Managed_Code_in_SQL.aspx。
Microsoft also has some CLR examples in there SQL Server Database Engine Samples on codeplex.
微软在codeplex上也有一些CLR示例SQL Server数据库引擎示例。
Good luck getting DBA sign off.
祝DBA好运。
Another option is to use the xp_cmdshell and execute a "command" on the naitive system, which could then call your .net code. Yet again.. good luck with DBAS ;)
另一个选项是使用xp_cmdshell并在naitive系统上执行“命令”，该系统随后可以调用.net代码。又一次. .祝dba好运!)
Other than those two I can't think of any other "simple" ways to do it. There are tons of complicated ways to do it..
除了这两种方法，我想不出其他任何“简单”的方法。有很多复杂的方法可以做到。
#2
1
Without enabling CLR on your SQL Server you can't run SQL CLR stored procs and triggers on it.
如果在SQL服务器上不启用CLR，就不能在它上运行SQL CLR存储的proc和触发器。
Meanwhile SQL CLR trigger is the best solution for your task
同时，SQL CLR触发器是您的任务的最佳解决方案
#3
0
I've done some fuzzy searching in assemblies on the database. It is very tricky. First of all you can not use the .NET 2.0 extentions (V3.0, V3.5, V4.0) depending on the SQL Server Version. Secondly you have to make sure that you only use managed code. No COM-Interops etc.
我在数据库的程序集中做了一些模糊搜索。这是非常棘手的。首先，根据SQL Server版本，您不能使用。net 2.0扩展(V3.0、V3.5、V4.0)。第二，必须确保只使用托管代码。没有COM-Interops等等。
I would think of a better solution since the triggers should be very fast and processing an excel can be very slow. How about an external process that polls on that table?
我想要一个更好的解决方案，因为触发器应该非常快，处理excel可能非常慢。那么在这个表上进行轮询的外部过程呢?
#4
0
I would have the trigger put an entry into a queue for processing and have the queue monitored actively for actions which need to be performed. This could be as simple as a table or as complex as a web service call. But it should be quick.
我将让触发器将一个条目放入队列中进行处理，并让队列主动监视需要执行的操作。这可以像表一样简单，也可以像web服务调用一样复杂。但它应该很快。
Typically, you don't want trigger operations to be lengthy or involve higher-level business logic. Triggers should be used to maintain the database in a state so that the database can provide consistent services to all database users (so low-level business logic in triggers is still fine). Once you get to application-level business logic, you really don't want that tightly coupled into your database.
通常，您不希望触发器操作太长或涉及高级业务逻辑。应该使用触发器将数据库保持在一种状态，以便数据库能够为所有数据库用户提供一致的服务(因此触发器中的低级业务逻辑仍然良好)。一旦进入应用程序级的业务逻辑，您真的不希望将其紧密耦合到数据库中。