DB/Oracle/PLSQL/¥È¥ê¥¬
Ìܼ¡†
PL/SQL¡¢ÊØÍø¤Ê¥È¥ê¥¬¤Î¤ªÏá£
¥È¥ê¥¬¤È¤Ï†
¥Ç¡¼¥¿¥Ù¡¼¥¹¥È¥ê¥¬¡£¼ÂÂΤϤ¿¤À¤Î¥¹¥È¥¢¥É¥×¥í¥·¡¼¥¸¥ã¡£
ɽ¤ä¥Ó¥å¡¼¤Î¹¹¿·¤Ê¤É¤¬È¯À¸¤·¤¿¤È¤¤Ë¡¢¼«Æ°Åª¤Ë¼Â¹Ô¤µ¤ì¤ë¤â¤Î¡£É½¤ËÂбþÉÕ¤±¤Æ¥Ç¡¼¥¿¥Ù¡¼¥¹¤Ë³ÊǼ¤·¤Æ¤ª¤¯¡£¤Þ¤¿¤Ï¥·¥¹¥Æ¥à¥¤¥Ù¥ó¥È¡ÊDB ¤Îµ¯Æ°»þ¡¢Ää»ß»þ¤Ê¤É¡Ë¤ä¥æ¡¼¥¶¥¤¥Ù¥ó¥È¡Ê¥í¥°¥ª¥ó¡¢¥í¥°¥ª¥Õ¤Ê¤É¡Ë¤ÎºÝ¤Ë¤âµ¯Æ°¤Ç¤¤ë¡£
¤¿¤È¤¨¤Ð¡¢ºß¸Ëɽ¤Î¤¢¤ë¾¦ÉʤλĿô¤¬ 100 ¸Ä¤«¤é 50 ¸Ä¤Ë¤Ê¤Ã¤¿¤È¤¡¢¼«Æ°¤Ç¥È¥ê¥¬¤¬µ¯Æ°¤·¤Æ¿ôÃͤò¥Á¥§¥Ã¥¯¡¢»Ä¿ô¤¬ÃͰʲ¼¤Ç¤¢¤ì¤ÐȯÃíɽ¤Ë¥Ç¡¼¥¿¤òÄɲ乤롢¤Ê¤É¤Î½èÍý¤¬²Äǽ¡£
¥×¥í¥·¡¼¥¸¥ã¤Ê¤À¤±¤¢¤Ã¤Æ¤À¤¤¤¿¤¤²¿¤Ç¤â¤Ç¤¤ë¤±¤É¡¢Oracle ¤Î¤â¤È¤â¤È¤Îµ¡Ç½¤ÇÂåÂؤǤ¤ë¤Ê¤é¤½¤ÎÊý¤¬Áᤤ¡£¤¿¤È¤¨¤Ð¥Á¥§¥Ã¥¯À©Ìó¤È¤«¡£Oracle ¤Îµ¡Ç½¤ÇÂбþ¤Ç¤¤º¤Ë¡¢¼«Æ°Åª¡Ê°ÅÌÛŪ¡Ë¤Ë²¿¤é¤«¤Î½èÍý¤ò¹Ô¤ï¤»¤¿¤¤¤È¤¤ËÍѤ¤¤ë¤È¤è¤¤¡£¤Ã¤Ý¤¤¡£
´ðËÜŪ¤Ê¹½Ê¸†
·ë¶É¤Ï¥¹¥È¥¢¥É¥×¥í¥·¡¼¥¸¥ã¤Ê¤Î¤Ç¹½Ê¸¤Ï»÷¤Æ¤ë¡£¥ª¥×¥·¥ç¥ó¤¬¤¤¤í¤¤¤í¤¢¤ë¤±¤É¡£
- ¥È¥ê¥¬
create or replace -- ¥È¥ê¥¬¤òºî¤ë¡£¥Ñ¥é¥á¡¼¥¿¤Ï¤Ê¤¤ trigger trg_hoge -- ¥¿¥¤¥ß¥ó¥°¡Ê¸å½Ò¡Ë¤È¥¤¥Ù¥ó¥È¡Ê¸å½Ò¡Ë¤òɽ¤ËÂФ·¤Æ¥»¥Ã¥È <timing> <event> on <tablename> -- ¥¿¥¤¥×¡Ê¸å½Ò¡Ë¤Î»ØÄê <type> -- ¤³¤Î²¼¤Ë̵̾¥Ö¥í¥Ã¥¯¤ò¤¯¤Ã¤Ä¤±¤ë declare ... begin ... exception ... end; /
¥¿¥¤¥ß¥ó¥°¤Î»ØÄê†
¤½¤Î¥È¥ê¥¬¤¬¤É¤Î¥¿¥¤¥ß¥ó¥°¤Çµ¯Æ°¤¹¤ë¤Î¤«¤ò»ØÄꤹ¤ë¡£
- ¥¿¥¤¥ß¥ó¥°
¥¿¥¤¥ß¥ó¥° ÀâÌÀ before ɽ¤ËÂФ¹¤ëÁàºî¤Î¼Â¹ÔÁ°¤Ë¥È¥ê¥¬¤òµ¯Æ°¤¹¤ë after ɽ¤ËÂФ¹¤ëÁàºî¤Î¼Â¹Ô¸å¤Ë¥È¥ê¥¬¤òµ¯Æ°¤¹¤ë
¥¤¥Ù¥ó¥È†
ɽ¤ËÂФ¹¤ë¤É¤Î¤è¤¦¤ÊÁàºî¤Çµ¯Æ°¤¹¤ë¤Î¤«¤ò»ØÄꤹ¤ë¡£°Ê²¼¤Î¤½¤ì¤¾¤ì¤òɬÍפʤÀ¤± OR ¤ÇÎóµó¤¹¤ë¡£
- ¥¤¥Ù¥ó¥È¤Î»ØÄê
¥¤¥Ù¥ó¥È ÀâÌÀ insert INSERT »þ¤Ëµ¯Æ°¤¹¤ë update UPDATE »þ¤Ëµ¯Æ°¤¹¤ë update of <columnname> ÆÃÄê¤ÎÎó¤Ø¤Î UPDATE »þ¤Ëµ¯Æ°¤¹¤ë delete DELETE »þ¤Ëµ¯Æ°¤¹¤ë
- ¥¤¥Ù¥ó¥È»ØÄê¤ÎÎã
create or replace trigger trg_hoge -- HOGE ɽ¤ËÂФ¹¤ë INSERT ¤« UPDATE ¤« DELETE ¤Çµ¯Æ°¤¹¤ë before insert or update or delete on hoge -- HOGE ɽ¤ËÂФ¹¤ë INSERT ¤« FUGA Îó¤Ø¤Î UPDATE ¤Çµ¯Æ°¤¹¤ë before insert or update of fuga on hoge
¥¿¥¤¥×†
¥È¥ê¥¬¤Î¥¿¥¤¥×¡£¤É¤ÎÈϰϤÇÆ°¤¯¤Î¤«»ØÄꤹ¤ë¥¤¥á¡¼¥¸¡£
- ¥¿¥¤¥×
¥¿¥¤¥× »ØÄêÊýË¡ ÀâÌÀ ʸ¥È¥ê¥¬ ²¿¤â»ØÄꤷ¤Ê¤¤ ±Æ¶Á¤ò¼õ¤±¤ë¹Ô¤Î¿ô¤È¤Ï´Ø·¸¤Ê¤¯¡¢¤Ò¤È¤Ä¤Î SQL ʸ¤Î¼Â¹Ô»þ¤Ë̵¾ò·ï¤Ëµ¯Æ°¤¹¤ë¥È¥ê¥¬¡£¼Â¹ÔÁ°¤Ëµ¯Æ°¤¹¤ë¤«¼Â¹Ô¸å¤Ëµ¯Æ°¤¹¤ë¤«¤ÏÁ°½Ò¤Î¥¿¥¤¥ß¥ó¥°¤Ç»ØÄꤹ¤ë¡£ ¹Ô¥È¥ê¥¬ for each row °ì¹Ô¤ËÂФ·¤Æ°ì²ó¤º¤Äµ¯Æ°¤¹¤ë¥È¥ê¥¬¡£¤¿¤È¤¨¤Ð°ì²ó¤Î UPDATE ¤Ç 5 ¹Ô¤¬¹¹¿·¤µ¤ì¤ë¾ì¹ç¤Ï 5 ²óµ¯Æ°¤¹¤ë¡£
¼Â¹Ôʸ¤Îµ½Ò†
:new ¤È :old†
¹¹¿·Á°¤È¹¹¿·¸å¤Î¹Ô¤Î¥Ç¡¼¥¿¤¬³ÊǼ¤µ¤ì¤¿¥Ð¥¤¥ó¥ÉÊÑ¿ô¡£¤³¤³¤«¤éÃͤò¤È¤ê¤À¤¹¤³¤È¤Ç¡¢¤¢¤ë¹Ô¤Î¹¹¿·Á°¸å¤ÎÃͤò¼èÆÀ¤Ç¤¤ë¡£
- ÃÍ¥Ù¡¼¥¹´Æºº¤Ë¤â»È¤¨¤ë
create or replace trigger trg_hoge befor update on tbl_hoge for each row declare v_diff number; begin v_diff := :new.foo - :old.foo; dbms_output.put_line('old value : ' || :old.foo); dbms_output.put_line('new value : ' || :old.foo); dbms_output.put_line('diff : ' || :old.foo); end; /
Last-modified: 2011-11-09 (¿å) 15:04:38