ÔÚSQL Server 2005ÖÐʵÏÖÒì²½´¥·¢Æ÷¼Ü¹¹
ÔÚSQL Server 2005Êý¾Ý¿âÖУ¬Í¨¹ýÐÂÔöµÄService Broker¿ÉÒÔʵÏÖÒì²½´¥·¢Æ÷µÄ´¦Àí¹¦ÄÜ¡£±¾ÎÄÌṩһÖÖʹÓÃService BrokerʵÏÖµÄͨÓÃÒì²½´¥·¢Æ÷·½·¨¡£
ÔÚ±¾Õâ¸ö·½·¨ÖУ¬Í¨¹ýService Broker¹¹ÔìÒì²½´¥·¢Æ÷´¦Àí¼Ü¹¹£¬¶ÔÓÚҪʹÓÃÕâÖּܹ¹µÄ±í£¬Ö»ÐèÒª´´½¨ÏàÓ¦µÄ´¥·¢Æ÷¼°´¦Àí´¥·¢Æ÷ÖÐÊý¾ÝµÄ´æ´¢¹ý³Ì£¬²¢ÇÒÔÚÒì²½´¥·¢Æ÷¼Ü¹¹ÖеǼǴ¥·¢Æ÷ºÍ´¦ÀíµÄ´æ´¢¹ý³Ì¼´¿É¡£Èç¹ûÒ»¸ö´¥·¢Æ÷ÖеÄÊý¾ÝÒª±»¶à¸ö±íʹÓã¬Ö»ÐèÒªÔÚdbo.tb_async_trigger_subscribtionÖеǼÇÏàÓ¦´¦ÀíÊý¾ÝµÄ´æ´¢¹ý³Ì¼´¿É£¬¼´Ò»¸ö±íµÄÊý¾Ý±ä¸ü¿ÉÒÔ±»¶à¸ö±í¶©ÔÄ£¨Ê¹Óã©¡£
¼Ü¹¹µÄ²½ÖèÈçÏ£º
1£® Êý¾Ý¿âÅäÖÃ
ÐèÒªÅäÖÃÊý¾Ý¿âÒÔÔÊÐíʹÓÃService Broker¡£±¾ÎÄÒÔtempdb¿âΪÀý£¬¹ÊÅäÖþùÔÚtempdbÉÏÏÂÎÄÖнøÐС£
USE tempdb
GO
-- ÔÊÐíService Broker
ALTER DATABASE tempdb SET
ENABLE_BROKER
GO
2£® ¹¹½¨Òì²½´¥·¢Æ÷Ïà¹ØµÄ¶ÔÏó
ÏÂÃæµÄT-SQL´´½¨Òì²½´¥·¢Æ÷´¦Àí¼Ü¹¹Ïà¹ØµÄ¶ÔÏó¡£
-- =======================================
-- Òì²½´¥·¢Æ÷¶ÔÏó
-- 1. service broker ¶ÔÏó
-- =======================================
-- a. message type, ÒªÇóʹÓÃxml ´«µÝÊý¾Ý
CREATE MESSAGE TYPE MSGT_async_trigger
VALIDATION = WELL_FORMED_XML
GO
-- b. Ö»ÐèÒª·¢ËÍÏûÏ¢
CREATE CONTRACT CNT_async_trigger(
MSGT_async_trigger SENT BY INITIATOR)
GO
-- c. ´æ´¢ÏûÏ¢µÄ¶ÓÁÐ
CREATE QUEUE dbo.Q_async_trigger
GO
-- d. ÓÃÓÚÏûÏ¢´¦ÀíµÄ·þÎñ
CREATE SERVICE SRV_async_trigger
ON QUEUE dbo.Q_async_trigger(
CNT_async_trigger)
GO
-- =======================================
-- Òì²½´¥·¢Æ÷¶ÔÏó
-- 2. Òì²½´¥·¢Æ÷´¦ÀíµÄ¶ÔÏó
-- =======================================
-- a. µÇ¼ÇÒì²½´¥·¢Æ÷µÄ±í
CREATE TABLE dbo.tb_async_trigger(
ID int IDENTITY
PRIMARY KEY,
table_name sysname,
trigger_name sysname
)
-- b. µÇ¼Ç¶©ÔÄÒì²½´¥·¢Æ÷µÄ´æ´¢¹ý³Ì
CREATE TABLE dbo.tb_async_trigger_subscriber(
ID int IDENTITY
PRIMARY KEY,
procedure_name sysname
)
-- c. Òì²½´¥·¢Æ÷ºÍ´æ´¢¹ý³ÌÖ®¼äµÄ¶©ÔĹØÏµ
CREATE TABLE dbo.tb_async_trigger_subscribtion(
trigger_id int
REFERENCES dbo.tb_async_trigger(
Ïà¹ØÎĵµ£º
Sql´úÂë
--²ÉÓÃSQLÓï¾äʵÏÖsql2005ºÍExcel Êý¾ÝÖ®¼äµÄÊý¾Ýµ¼Èëµ¼³ö£¬ÔÚÍøÉÏÕÒÀ´Ò»--Ï£¬ÊµÏÖ·½·¨ÊÇÕâÑùµÄ£º
--Excel---->SQL2005 µ¼È룺
select * into useinfo from O ......
Èç¹ûÄã¾³£Óöµ½ÏÂÃæµÄÎÊÌ⣬Äã¾ÍÒª¿¼ÂÇʹÓÃSQL ServerµÄÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾äÁË£º
SQL³õѧÕß¡£
¾³£Íü¼Ç³£ÓõÄDML»òÊÇDDL SQL Óï¾ä¡£
ÔÚ¶àÈË¿ª·¢Î¬»¤µÄSQLÖУ¬Ã¿¸öÈ˶¼ÓÐ×Ô¼ºµÄSQLϰ¹ß£¬Ã»ÓÐÒ»Ì×ͳһµÄ¹æ·¶¡£
ÔÚSQL Server Management StudioÖУ¬ÒѾ¸ø´ó¼ÒÌṩÁ˺ܶೣÓõÄÏÖ³ÉSQL¹æ·¶Ä£°å¡£
SQL Server Management ......
×÷Õß: ÈýÊ®¶øÁ¢Ê±¼ä£º2009Äê10ÔÂ15ÈÕ 21:59:51±¾Îijö×Ô ¡°inthirties£¨ÈýÊ®¶øÁ¢£©¡±²©¿Í£¬×ªÔØÇëÎñ±Ø×¢Ã÷×÷Õߺͱ£Áô³ö´¦http://blog.csdn.net/inthirties/archive/2009/10/15/4673268.aspx °Ñ´ÎÐò²»¶ÔµÄÁÐÕÒ³öÀ´ ÇëÎÊ ÏÂÃæµÄ±í¸ÃÈçºÎÅÅÐò£¿ÎÒÓÐÏÂÃæµÄ±í¡£´ÎÐòÈçÏ ÆäÖеÚ5¸ö¼Ç¼ºÍµÚ6Ìõ¼Ç¼´ÎÐòµßµ¹ÁË¡£Õâ¸ö±íÊý¾ÝÁ¿±È½Ï ......
Select * into customers from clients
(Êǽ«clients±íÀïµÄ¼Ç¼²åÈëµ½customersÖУ¬ÒªÇó£ºcustomers±í²»´æÔÚ£¬ÒòΪÔÚ²åÈëʱ»á×Ô¶¯´´½¨Ëü£»)
Insert into customers select * from clients
½â£ºInsert into customers select * from clients£©ÒªÇóÄ¿±ê±í£¨customers£©´æÔÚ£¬
ÓÉÓÚÄ¿±ê±íÒѾ´æÔÚ£¬ËùÒÔÎÒÃdzýÁ˲åÈëÔ´±í£ ......