drop function people
4.´¥·¢Æ÷¡£
Create trigger XXX On table
[ for /after /instead of ] Insert,delete,update As ´¥·¢Æ÷ʵ¼ÊÐèÒª´¥·¢ÄÚÈÝ
For£ºÓ÷¨Í¬after
After£ºÖ´ÐÐÍêsqlÓï¾äÖ®ºó½øÐмì²é£¬¼ì²é²»·ûºÏÌõ¼þµÄ»°»Ø¹ö roll back ¡£after´¥·¢Æ÷Ö»ÄÜÔÚ±íÉÏÖ¸¶¨¡£
Instead of £ºÖ¸¶¨´¥·¢Æ÷ʱ´úÌæSQLÓï¾äÖ´Ðе쬯äÓÅÏȼ¶¸ßÓÚ´¥·¢Óï¾äµÄ²Ù×÷¡£ Àý£º
Ϊ student±í½¨Á¢´¥·¢Æ÷T1£¬µ±²åÈë»ò¸üбíÖеÄÊý¾Ýʱ£¬±£Ö¤Ëù²Ù×÷µÄ¼Ç¼µÄsageÖµ´óÓÚ0¡£ create trigger T1 on student after insert,update as begin
declare @sage int
select @sage = inserted.sage from inserted if(@sage < 0) begin
print 'ÄêÁä´íÎó' rollback transaction
end
end
update student set sage = 38 where sno = '01'
insert into student(sno,sname,ssex,sage,sdept) values('2007011','ÕÅÈý','ÄÐ',55,'ma')
Ϊstudent±í½¨Á¢´¥·¢Æ÷T2£¬½ûֹɾ³ý±àºÅΪ¡°0001¡±µÄѧÉú¡£ insert into student(sno,sname,ssex,sage,sdept) values('0001','ɵ´ó¸ö','ÄÐ',19,'is')
create trigger T2 on student after delete as begin
declare @sno char(10)
select @sno = deleted.sno from deleted if(@sno = '0001')
begin
print '½ûֹɾ³ýѧºÅ0001ÐÅÏ¢' rollback transaction
end
end
delete student where sno = '0001'
drop trigger T2
µÚÊ®ÕÂ
1£®ÊÂÎñ¡£
ÊÇһϵÁеÄÊý¾Ý¿â²Ù×÷£¬ÊÇÊý¾Ý¿âÓ¦ÓóÌÐòµÄ»ù±¾Âß¼µ¥Ôª¡£
ÊÇÓû§¶¨ÒåµÄÒ»¸öÊý¾Ý¿â²Ù×÷ÐòÁУ¬ÕâЩ²Ù×÷Ҫôȫ×ö£¬ÒªÃ´È«²»×ö£¬ÊÇÒ»¸ö²»¿É·Ö¸î
µÄ¹¤×÷µ¥Ôª¡£ 2£®ÊÂÎñµÄËĸöÌØÐÔ¡£
Ô×ÓÐÔ£ºÊÂÎñÊÇÊý¾Ý¿âµÄÂß¼¹¤×÷µ¥Î»£¬ÊÂÎñ×ܰüÀ¨µÄÖî²Ù×÷Ҫôȫ×ö£¬ÒªÃ´È«²»×ö¡£ Ò»ÖÂÐÔ£ºÊý¾Ý¿âÖ»°üº¬³É¹¦ÊÂÎñÌá½»µÄ½á¹û£¬¾Í˵Êý¾Ý¿â´¦ÓÚÒ»ÖÂÐÔ״̬¡£
¸ôÀëÐÔ£ºÒ»¸öÊÂÎñµÄÖ´Ðв»Äܱ»ÆäËûÊÂÎñ¸ÉÈÅ¡£¼´Ò»¸öÊÂÎñµÄÄÚ²¿²Ù×÷¼°Ê¹ÓõÄÊý¾Ý¶Ô
ÆäËû²¢·¢ÊÂÎñÊǸôÀëµÄ£¬²¢·¢Ö´Ðеĸ÷¸öÊÂÎñÖ®¼ä²»ÄÜ»¥Ïà¸ÉÈÅ¡£
³ÖÐøÐÔ£ºÖ¸Ò»¸öÊÂÎñÒ»µ©Ìá½»£¬Ëû¶ÔÊý¾Ý¿âÖÐÊý¾ÝµÄ¸Ä±ä¾ÍÓ¦¸ÃÊÇÓÀ¾ÃÐԵġ£
ÊÂÎñÊǻָ´ºÍ²¢·¢¿ØÖƵĻù±¾µ¥Î»¡£ 3£®Êý¾Ý¿â»Ö¸´¡£
°ÑÊý¾Ý¿â´Ó´íÎó״̬»Ö¸´µ½Ä³Ò»ÒÑÖªµÄÕýȷ״̬µÄ¹¦ÄÜ¡£
1. ÔËÐÐÊÂÎñ·ÇÕý³£Öжϡ£ 2. Êý¾Ý¶ªÊ§¡£
4£®¹ÊÕÏÖÖÀà¡£
ÔËÐÐÊÂÎñ·ÇÕý³£ÖжÏ----------»Ö¸´-ÊÂÎñ³·Ïú¡£ ϵͳ¹ÊÕÏ¡¢½éÖʹÊÕÏ¡¢¼ÆËã»ú²¡¶¾¡£
5£®»Ö¸´ÊµÏÖ¼¼Êõ¡£
Êý¾Ý»Ö¸´µÄ»ù±¾ÔÀí£ºÈßÓà¡£
½¨Á¢ÈßÓàÊý¾Ý×î³£Óõļ¼ÊõÊÇÊý¾Ýת´¢ºÍµÇ¼ÇÈÕÖ¾Îļþ¡£
6£® Êý¾Ýת´¢¡£
¾²Ì¬×ª´¢£ºÊÇÔÚϵͳÖÐÎÞÔËÐÐÊÂÎñʱ½øÐеÄת´¢²Ù×÷¡£
¶¯Ì¬×ª´¢£ºÊÇָת´¢ÆÚ¼äÔÊÐí¶ÔÊý¾Ý¿â½øÐдæÈ¡»òÐ޸ġ£¼´×ª´¢ºÍÓû§ÊÂÎñ¿ÉÒÔ²¢·¢Ö´
ÐС£
º£Á¿×ª´¢£ºÃ¿´Îת´¢È«²¿Êý¾Ý¿â¡£
ÔöÁ¿×ª´¢£ºÖ¸Ã¿´Îֻת´¢ÉÏÒ»´Îת´¢ºó¸üйýµÄÊý¾Ý¡£
7.ÈÕÖ¾Îļþ¡£
ÊÇÓÃÀ´¼Ç¼ÊÂÎñ¶ÔÊý¾Ý¿âµÄ¸üвÙ×÷µÄÎļþ¡£ ÔÚÊÂÎñ¹ÊÕϻָ´ºÍϵͳ¹ÊÕϻָ´±ØÐëÓÃÈÕÖ¾Îļþ¡£
ÔÚ¶¯Ì¬×ª´¢·½Ê½ÖбØÐ뽨Á¢ÈÕÖ¾Îļþ£¬ºó±¸¸±±¾ºÍÈÕÖ¾Îļþ½áºÏÆðÀ´²ÅÄÜÓÐЧµØ»Ö¸´Êý
¾Ý¿â¡£
ÔÚ¾²Ì¬×ª´¢ÖÐÒ²½¨Á¢ÈÕÖ¾Îļþ¡¡
8£®µÇ¼ÇÈÕÖ¾Îļþ£¨¶ÔÊý¾Ý¿âµÄ²Ù×÷¼Ç¼£©¡£
µÇ¼ÇµÄ´ÎÐòÑϸñ°´²¢·¢ÊÂÎñÖ´ÐеÄʱ¼ä´ÎÐò¡£ ±ØÐëÏÈдÈÕÖ¾Îļþ£¬ºóдÊý¾Ý¿â¡£
9£®ÊÂÎñ¹ÊÕϵĻָ´¡£
»Ö¸´×ÓϵͳӦÀûÓÃÈÕÖ¾Îļþ³·Ïú´ËÊÂÎñÒѶÔÊý¾Ý¿â½øÐеÄÐ޸ģ¬ÏµÍ³×Ô¶¯½øÐС£
10£®ÏµÍ³¹ÊÕϻָ´¡£
µ¼Ö¹ÊÕÏÔÒò¿ÉÄÜÓÐÁ½¸ö£º
1. δÍê³ÉÊÂÎñ¶ÔÊý¾Ý¿âµÄ¸üпÉÄÜÒÑдÈëÊý¾Ý¿â¡£
2. ÒÑÌá½»ÊÂÎñ¶ÔÊý¾Ý¿âµÄ¸üпÉÄÜ»¹ÁôÔÚ»º³åÇøÃ»À´µÃ¼°Ð´ÈëÊý¾Ý¿â¡£
»Ö¸´²ßÂÔÊÇ ³·Ïú¹ÊÕÏ·¢ÉúʱδÍê³ÉµÄÊÂÎï£¬ÖØ×öÒÑÍê³ÉµÄÊÂÎï¡£ 11£®½éÖʹÊÕϵĻָ´¡£
Êý¾Ý¿â¸±±¾¡£
µÚʮһÕÂ
1£® ²¢·¢²Ù×÷´øÀ´µÄÊý¾Ý²»Ò»ÖÂÐÔ¡£ ²¢·¢²Ù×÷ÆÆ»µÁËÊÂÎñµÄ¸ôÀëÐÔ¡£
¶ªÊ§Ð޸ģºÁ½¸öÊÂÎñͬʱ¶ÁÈëͬһÊý¾Ý²¢Ð޸ģ¬T2Ìá½»µÄ½á¹¹ÆÆ»µÁËT1Ìá½»µÄ½á¹û£¬
µ¼ÖÂT1µÄÐ޸ı»¶ªÊ§¡£
²»¿ÉÖØ¸´¶Á£ºÊÂÎñ1¶ÁÈ¡Êý¾Ýºó£¬ÊÂÎñ2Ö´ÐиüвÙ×÷£¬Ê¹T1ÎÞ·¨ÔÙÏÖǰһ´Î¶ÁÈ¡½á
¹û¡£
¶ÁÔàÊý¾Ý£ºÊÂÎñ1ÐÞ¸ÄijһÊý¾Ý²¢½«Æäд»Ø´ÅÅÌ£¬ÊÂÎñ2¶ÁȡͬһÊý¾Ýºó£¬T1ÓÉÓÚij