Êý¾Ý¿âϵͳ¸ÅÂÛÍõɺµÚÎå°æÑ§Ï°±Ê¼Ç ÏÂÔØ±¾ÎÄ

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