SQL Server´¥·¢Æ÷´´½¨¡¢É¾³ý¡¢Ð޸ġ¢²é¿´Ê¾Àý½Ì³Ì
Ò»﹕ ´¥·¢Æ÷ÊÇÒ»ÖÖÌØÊâµÄ´æ´¢¹ý³Ì﹐Ëü²»Äܱ»ÏÔʽµØµ÷ÓÃ﹐¶øÊÇÔÚÍù±íÖвåÈë¼Ç¼﹑¸üмǼ»òÕßɾ³ý¼Ç¼ʱ±»×Ô¶¯µØ¼¤»î¡£ËùÒÔ´¥·¢Æ÷¿ÉÒÔÓÃÀ´ÊµÏÖ¶Ô±íʵʩ¸´ÔÓµÄÍêÕûÐÔÔ¼`Êø¡£
¶þ﹕ SQL ServerΪÿ¸ö´¥·¢Æ÷¶¼´´½¨ÁËÁ½¸öרÓñí﹕Inserted±íºÍDeleted±í¡£ÕâÁ½¸ö±í¡£
Ò»﹕ ´¥·¢Æ÷ÊÇÒ»ÖÖÌØÊâµÄ´æ´¢¹ý³Ì﹐Ëü²»Äܱ»ÏÔʽµØµ÷ÓÃ﹐¶øÊÇÔÚÍù±íÖвåÈë¼Ç¼﹑¸üмǼ»òÕßɾ³ý¼Ç¼ʱ±»×Ô¶¯µØ¼¤»î¡£ËùÒÔ´¥·¢Æ÷¿ÉÒÔÓÃÀ´ÊµÏÖ¶Ô±íʵʩ¸´ÔÓµÄÍêÕûÐÔÔ¼`Êø¡£
¶þ﹕ 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 ´¥·¢Æ÷ÔÚÔ¼ÊøÖ®Ç°Ö´ÐÐ﹐ËùÒÔËü
Ïà¹ØÎĵµ£º
Five basic search conditions are summarized here:
1) Comparison test
2) Range test
3) Set membership test
4) Pattern matching test (Like)
The pattern matching test checks to see whether the data value in a column matches a specified pattern
% mathes any se ......
´´½¨Íâ¼üÔ¼Êø
CREATE TABLE order_sample
(
orderid int PRIMARY KEY,
cust_id int FOREIGN KEY REFERENCES cuts_sample(cust_id) ON DELETE NO CASCADE
)
ON DELETE--ÓÃÓÚ¿ØÖƳ¢ÊÔɾ³ýÍâ¼üÏà¹ØÁªµÄÖ÷±íÖ¸ÏòÐÐʱ²ÉÈ¡µÄ²Ù×÷
-NO ACTION
ɾ³ýÍâ¼üÏà¹ØÁªµÄÖ÷±íÖ¸ÏòÐÐʱ£¬±¨´í
-CASCADE
ɾ³ýÍâ¼üÏà¹ØÁªµÄÖ÷±íÖ¸ÏòÐÐʱ ......
±ÜÃâSQL×¢ÈëµÄ·½·¨ÓÐÁ½ÖÖ£ºÒ»ÊÇËùÓеÄSQLÓï¾ä¶¼´æ·ÅÔÚ´æ´¢¹ý³ÌÖУ¬ÕâÑù²»µ«¿ÉÒÔ±ÜÃâSQL×¢È룬»¹ÄÜÌá¸ßһЩÐÔÄÜ£¬²¢ÇÒ´æ´¢¹ý³Ì¿ÉÒÔÓÉרÃŵÄÊý¾Ý¿â¹ÜÀíÔ±(DBA)±àдºÍ¼¯ÖйÜÀí£¨ÕâÖÖ×ö·¨ÎÒÔÚһЩ¹«Ë¾¼û¹ý£©£¬²»¹ýÕâÖÖ×ö·¨ÓÐʱºòÕë¶ÔÏàͬµÄ¼¸¸ö±íÓв»Í¬Ìõ¼þµÄ²éѯ£¬SQLÓï¾ä¿ÉÄܲ»Í¬£¬ÕâÑù¾Í»á±àд´óÁ¿µÄ´æ´¢¹ý³Ì£¬ËùÒÔÓÐÈËÌá³ ......
IF OBJECT_ID('DataAsInsCommand') IS NOT NULL DROP PROC DataAsInsCommand
GO
SET QUOTED_IDENTIFIER OFF
GO
CREA ......
´Ó²©¿ÍÔ°Öп´µ½Ò»ÆªÎÄÕ£¬½éÉÜ´óÈí¼þ¹«Ë¾ÃæÊÔʱ³£³£»á³öµÄÁ½µÀSQLÌ⣨¼û¸½Â¼£©¡£
ÎÒ¾õµÃÊÜÒæºÜ¶à£¬ÔÚ´Ë֮ǰ£¬ÎÒÒ»Ö±¾õµÃ£¬SQL2008ËÆºõÌṩÁËÕâ·½ÃæµÄÖ§³Ö£¬µ«¸üµÍµÄ°æ±¾£¬°üÀ¨2005£¬·ÇÓαê×ö²»³öÀ´£¨Ë®Æ½¹»²Ë£©¡£×ܽáÐĵÃÈçÏ£º
1¡¢ Ç¿´óµÄgroup by
1 select stdname,
2 isnull( ......