SQL´¥·¢Æ÷Óï·¨²Î¿¼
ת×Ôhttp://www.tzwhx.com/newOperate/Html/3/31/311/16609.html
Óï·¨
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ n ]
} ]
sql_statement [ n ]
}
}
²ÎÊý
trigger_name
ÊÇ´¥·¢Æ÷µÄÃû³Æ¡£´¥·¢Æ÷Ãû³Æ±ØÐë·ûºÏ±êʶ·û¹æÔò£¬²¢ÇÒÔÚÊý¾Ý¿âÖбØÐëΨһ¡£¿ÉÒÔÑ¡ÔñÊÇ·ñÖ¸¶¨´¥·¢Æ÷ËùÓÐÕßÃû³Æ¡£
Table | view
ÊÇÔÚÆäÉÏÖ´Ðд¥·¢Æ÷µÄ±í»òÊÓͼ£¬ÓÐʱ³ÆΪ´¥·¢Æ÷±í»ò´¥·¢Æ÷ÊÓͼ¡£¿ÉÒÔÑ¡ÔñÊÇ·ñÖ¸¶¨±í»òÊÓͼµÄËùÓÐÕßÃû³Æ¡£
WITH ENCRYPTION
¼ÓÃÜ syscomments ±íÖаüº¬ CREATE TRIGGER Óï¾äÎı¾µÄÌõÄ¿¡£Ê¹Óà WITH ENCRYPTION ¿É·ÀÖ¹½«´¥·¢Æ÷×÷Ϊ SQL Server ¸´ÖƵÄÒ»²¿·Ö·¢²¼¡£
AFTER
Ö¸¶¨´¥·¢Æ÷Ö»ÓÐÔÚ´¥·¢ SQL Óï¾äÖÐÖ¸¶¨µÄËùÓвÙ×÷¶¼Òѳɹ¦Ö´Ðкó²Å¼¤·¢¡£ËùÓеÄÒýÓü¶Áª²Ù×÷ºÍÔ¼Êø¼ì²éÒ²±ØÐë³É¹¦Íê³Éºó£¬²ÅÄÜÖ´Ðд˴¥·¢Æ÷¡£
Èç¹û½öÖ¸¶¨ FOR ¹Ø¼ü×Ö£¬Ôò AFTER ÊÇĬÈÏÉèÖá£
²»ÄÜÔÚÊÓͼÉ϶¨Òå AFTER ´¥·¢Æ÷¡£
INSTEAD OF
Ö¸¶¨Ö´Ðд¥·¢Æ÷¶ø²»ÊÇÖ´Ðд¥·¢ SQL Óï¾ä£¬´Ó¶øÌæ´ú´¥·¢Óï¾äµÄ²Ù×÷¡£
ÔÚ±í»òÊÓͼÉÏ£¬Ã¿¸ö INSERT¡¢UPDATE »ò DELETE Óï¾ä×î¶à¿ÉÒÔ¶¨ÒåÒ»¸ö INSTEAD OF ´¥·¢Æ÷¡£È»¶ø£¬¿ÉÒÔÔÚÿ¸ö¾ßÓÐ INSTEAD OF ´¥·¢Æ÷µÄÊÓͼÉ϶¨ÒåÊÓͼ¡£
INSTEAD OF ´¥·¢Æ÷²»ÄÜÔÚ WITH CHECK OPTION µÄ¿É¸üÐÂÊÓͼÉ϶¨Òå¡£Èç¹ûÏòÖ¸¶¨ÁË WITH CHECK OPTION Ñ¡ÏîµÄ¿É¸üÐÂÊÓͼÌí¼Ó INS
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃ棬ºóÃæ ......
¾ßÌå²½Ö裺
¡¡¡¡Ò».ÔÚ·þÎñÆ÷¶ËÒÔ
dbsrv7.exe pathyyy.db -n
ServiceName -x tcpip -q
¡¡¡¡Æô¶¯Êý¾Ý¿â£¬ÕâÀïÒ²¿ÉÒÔÖ¸¶¨¶Ë¿Ú£¬ÏêϸµÄ²ÎÊýÇëÓÃdbsrv7.exe /?»ñÈ¡¡£
¡¡¡¡-n servicename:Æô¶¯·þÎñÃûΪservicenameµÄÊý¾Ý¿â·þÎñÓÃÒÔ±ê¼ÇÍøÂçÊý¾Ý¿â·þÎñΨһ±ê¼Ç£»
¡¡¡¡-x tcpip£ºÊ¹ÓÃtcp/ipÐÒ飬ĬÈ϶˿Ú3362,×¢Òâ²»Òª± ......
ΪÁË´ó¼Ò¸üÈÝÒ×Àí½âÎÒ¾Ù³öµÄSQLÓï¾ä£¬±¾Îļٶ¨ÒѾ½¨Á¢ÁËÒ»¸öѧÉú³É¼¨¹ÜÀíÊý¾Ý¿â£¬È«ÎľùÒÔѧÉú³É¼¨µÄ¹ÜÀíΪÀýÀ´ÃèÊö¡£
¡¡¡¡1.ÔÚ²éѯ½á¹ûÖÐÏÔʾÁÐÃû£º
¡¡¡¡a.ÓÃas¹Ø¼ü×Ö£ºselect name as 'ÐÕÃû' from students order by age
¡¡¡¡b.Ö±½Ó±íʾ£ºselect name 'ÐÕÃû' from students order by age
¡¡¡¡2.¾«È·²éÕÒ:
¡¡¡¡a.Ó ......
Çå³ýSQLÊý¾Ý¿âÈÕÖ¾Îļþ
/*--ѹËõÊý¾Ý¿âµÄͨÓô洢¹ý³Ì
ÓÐÁ½ÖÖ·½·¨ Ò»ÊÇѹËõÈÕÖ¾£¬¶þÊÇɾ³ýÈÕÖ¾¡£
Ò»¡¢Ñ¹ËõÈÕÖ¾¼°Êý¾Ý¿âÎļþ´óС
ÒòΪҪ¶ÔÊý¾Ý¿â½øÐзÖÀë´¦Àí
ËùÒÔ´æ´¢¹ý³Ì²»ÄÜ´´½¨ÔÚ±»Ñ¹ËõµÄÊý¾Ý¿âÖÐ
/*--µ÷ÓÃʾÀý
exec p_compdb 'test'
--*/
use master --×¢Òâ,´Ë´æ´¢¹ý³ÌÒª½¨ÔÚmasterÊý¾Ý¿âÖÐ
go ......
£¨1£©Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLE µÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£Èç¹ûÓÐ3¸öÒÔÉϵıíÁ¬½Ó²éѯ, ÄǾÍÐèҪѡÔñ½»²æ±í(interse ......