SQL Server´¥·¢Æ÷´´½¨¡¢É¾³ý¡¢Ð޸ġ¢²é¿´Ê¾Àý½Ì³Ì
Ò»﹕
´¥·¢Æ÷ÊÇÒ»ÖÖÌØÊâµÄ´æ´¢¹ý³Ì﹐Ëü²»Äܱ»ÏÔʽµØµ÷ÓÃ﹐¶øÊÇÔÚÍù±íÖвåÈë¼Ç¼﹑¸üмǼ»òÕßɾ³ý¼Ç¼ʱ±»×Ô¶¯µØ¼¤»î¡£ËùÒÔ´¥·¢Æ÷¿ÉÒÔÓÃÀ´ÊµÏÖ¶Ô±íʵʩ¸´ÔÓµÄÍê
ÕûÐÔÔ¼`Êø¡£
¶þ﹕ SQL
ServerΪÿ¸ö´¥·¢Æ÷¶¼´´½¨ÁËÁ½¸öרÓñí﹕Inserted±íºÍDeleted±í¡£ÕâÁ½¸ö±íÓÉϵͳÀ´Î¬»¤﹐ËüÃÇ´æÔÚÓÚÄÚ´æÖжø²»ÊÇÔÚÊý¾Ý¿âÖС£ÕâÁ½¸ö
±íµÄ½á¹¹×ÜÊÇÓë±»¸Ã´¥·¢Æ÷×÷ÓõıíµÄ½á¹¹Ïàͬ¡£´¥·¢Æ÷Ö´ÐÐ Íê³Éºó﹐Óë¸Ã´¥·¢Æ÷Ïà¹ØµÄÕâÁ½¸ö±íÒ²±»É¾³ý¡£
Deleted±í´æ·ÅÓÉÓÚÖ´ÐÐDelete»òUpdateÓï¾ä¶øÒª´Ó±íÖÐɾ³ýµÄËùÓÐÐС£
Inserted±í´æ·ÅÓÉÓÚÖ´ÐÐInsert»òUpdateÓï¾ä¶øÒªÏò±íÖвåÈëµÄËùÓÐÐС£
Èý﹕Instead of ºÍ After´¥·¢Æ÷
SQL Server2000ÌṩÁËÁ½ÖÖ´¥·¢Æ÷﹕Instead of ºÍAfter ´¥·¢Æ÷¡£ÕâÁ½ÖÖ´¥·¢Æ÷µÄ²î±ðÔÚÓÚËûÃDZ»¼¤»îµÄͬ﹕
Instead of´¥·¢Æ÷ÓÃÓÚÌæ´úÒýÆð´¥·¢Æ÷Ö´ÐеÄT-SQLÓï¾ä¡£³ý±íÖ®Íâ﹐Instead of
´¥·¢Æ÷Ò²¿ÉÒÔÓÃÓÚÊÓͼ﹐ÓÃÀ´À©Õ¹ÊÓͼ¿ÉÒÔÖ§³ÖµÄ¸üвÙ×÷¡£
After´¥·¢Æ÷ÔÚÒ»¸öInsert,Update»òDeletedÓï¾äÖ®ºóÖ´ÐÐ﹐½øÐÐÔ¼Êø¼ì²éµÈ¶¯×÷¶¼ÔÚAfter´¥·¢Æ÷±»¼¤»î֮ǰ·¢Éú¡£After´¥
·¢Æ÷Ö»ÄÜÓÃÓÚ±í¡£
Ò»¸ö±í»òÊÓͼµÄÿһ¸öÐ޸͝×÷(insert,updateºÍdelete)¶¼¿ÉÒÔÓÐÒ»¸öinstead of
´¥·¢Æ÷﹐Ò»¸ö±íµÄÿ¸öÐ޸͝×÷¶¼¿ÉÒÔÓжà¸öAfter´¥·¢Æ÷¡£
ËÄ﹕´¥·¢Æ÷µÄÖ´Ðйý³Ì
Èç¹ûÒ»¸öInsert﹑update»òÕßdeleteÓï¾äÎ¥·´ÁËÔ¼Êø﹐ÄÇçÛAfter´¥·¢Æ÷²»»áÖ´ÐÐ﹐ÒòΪ¶ÔÔ¼ÊøµÄ¼ì²éÊÇÔÚAfter´¥·¢Æ÷±»¼¤¶¯Ö®Ç°·¢
ÉúµÄ¡£ËùÒÔAfter´¥·¢Æ÷²»Äܳ¬Ô½Ô¼Êø¡£
Instead of
´¥·¢Æ÷¿ÉÒÔÈ¡´ú¼¤·¢ËüµÄ²Ù×÷À´Ö´ÐС£ËüÔÚInserted±íºÍDeleted±í¸Õ¸Õ½¨Á¢﹐ÆäËüÈκβÙ×÷»¹Ã»Óз¢Éúʱ±»Ö´ÐС£ÒòΪInstead of
´¥·¢Æ÷ÔÚÔ¼ÊøÖ®Ç°Ö´ÐÐ﹐ËùÒÔËü¿ÉÒÔ¶ÔÔ¼Êø½øÐÐһЩԤ´¦Àí¡£
Îå﹕ʹÓÃT-SQLÓï¾äÀ´´´½¨´¥·¢Æ÷
»ù±¾Óï¾äÈçÏÂ﹕
create trigger t
Ïà¹ØÎĵµ£º
declare @XML XML
SET @XML='<root>
<OLDVALUE>
<H_Action id="1130">030</H_Action>
<D_Action>030</D_Action>
<OrderCompany>00220</OrderCompany>
<OrderNumber>10004035</OrderNumber> ......
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-15 22:07:01
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copy ......
Ò»¡¢SQL×¢Èë·À·¶
ÔÚÒ»¸öWEB¶¯Ì¬Ò³ÃæÖУ¨ÀýÈçaspx»òÕßjsp£©£¬Õâ¸öÒ³ÃæÔÊÐíÓû§ÔÚÊäÈë¿òÖÐÊäÈë×Ö·û£¬Õâ¸ö×Ö·û¿ÉÒÔ±»ÒýÈëµ½Êý¾Ý¿âÖÐÈ¥½øÐвéѯ£¨ÕâÀïµÄ²éѯÊÇͨÓõÄ˵·¨£¬Êµ¼ÊÉϰüÀ¨ÁËÔöɾ¸Ä²é£©²Ù×÷¡£Ò»¸öºÚ¿ÍÔÚÕâ¸öÊäÈë¿òÖÐÊäÈëÁËÒ»¸ö»ûÐβéѯ×Ö·û´®£¬´Ó¶ø¸Ä±äÁËÔÓеIJéѯ£¬Õâ¿ÉÒÔ±»ÓÃÀ´²åÈ룬¸Ä±ä£¬»òË𺦺ǫ́Êý¾Ý¿â¡£Ôõà ......
Ò»¡¢»ù´¡
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname
3¡¢ËµÃ÷£º±¸·Ýsql server
--- ´´½¨ ±¸·ÝÊý¾ÝµÄ device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- ¿ªÊ¼ ±¸·Ý
BACKUP DATABASE pubs TO testBack
4¡¢Ëµ ......
sql serverµÄËæ»úº¯ÊýnewID()ºÍRAND()¡¡¡¡
¡¡¡¡SELECT * from Northwind..Orders ORDER BY NEWID()
¡¡¡¡--Ëæ»úÅÅÐò
¡¡¡¡SELECT TOP 10 * from Northwind..Orders ORDER BY NEWID()
¡¡¡¡--´ÓOrders±íÖÐËæ»úÈ¡³ö10Ìõ¼Ç¼¡¡¡¡
¡¡¡¡Ê¾Àý¡¡¡¡
¡¡¡¡A.¶Ô±äÁ¿Ê¹Óà NEWID º¯Êý
¡¡¡¡ÒÔÏÂʾÀýʹÓà NEWID() ¶ÔÉùÃ÷Ϊ uniq ......