トリガã?«ã‚ˆã‚Šãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã?®ã‚¤ãƒ™ãƒ³ãƒˆã?«å¿œã?˜ã?¦å®Ÿè¡Œã?•れるアクションã?®é›†å?ˆã‚’ã€?表ã?«å®šç¾©ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ã?“ã?“ã?§ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã?®ã‚¤ãƒ™ãƒ³ãƒˆã?¨ã?¯å‰Šé™¤ã€?挿入ã€?更新処ç?†ã?®ã?“ã?¨ã?§ã?™ã€‚ 例ã?ˆã?°è¡¨ã?«å‰Šé™¤ã?¸ã?®ãƒˆãƒªã‚¬ã‚’定義ã?—ã?Ÿå ´å?ˆã€?ã??ã?®ãƒˆãƒªã‚¬ã?®ã‚¢ã‚¯ã‚·ãƒ§ãƒ³ã?¯è¡¨ã?‹ã‚‰è¡Œã?Œå‰Šé™¤ã?•れるã?¨ã??ã?«å®Ÿè¡Œã?•れã?¾ã?™ã€‚
制約ã?¨ä½µã?›ã?¦ã€?トリガã?«ã‚ˆã‚‹æ³¢å?Šçš„ã?ªå‰Šé™¤ã‚„更新を定義ã?™ã‚‹ã?“ã?¨ã?§ã€?æƒ…å ±ã?®æ•´å?ˆæ€§ã‚’ä¿?ã?¤ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ トリガã?§å®Ÿç?¾ã?§ã??る機能ã?¯ã?“ã?®ã?»ã?‹ã?«ã‚‚ã€?è¦å‘Šã?®å‘ˆç¤ºã‚„ã€?ä»–ã?®è¡¨ã?®æ›´æ–°ã€?e-mailã?®é€?ä¿¡ã‚„ã??ã?®ä»–ã?®å‡¦ç?†ã?ªã?©ã€?多å²?ã?«ã‚?ã?Ÿã‚Šã?¾ã?™ã€‚
ã?²ã?¨ã?¤ã?®è¡¨ã?«å®šç¾©å?¯èƒ½ã?ªãƒˆãƒªã‚¬ã?®æ•°ã?«ã?¯ã€?ã??れらã?®ãƒˆãƒªã‚¬ã?Œå?Œã?˜ã‚¤ãƒ™ãƒ³ãƒˆã?¸ã?®ãƒˆãƒªã‚¬ã?§ã?‚ã‚‹ã?‹å?¦ã?‹ã?«ã?‹ã?‹ã‚?らã?šã€?上é™?ã?¯ã?‚りã?¾ã?›ã‚“。
スã‚ーマã?®æ‰€æœ‰è€…ã?§ã?‚るユーザã?§ã?‚れã?°ã€?ã??ã?®ã‚¹ã‚ーマã?«ãƒˆãƒªã‚¬ã‚’作æˆ?ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ 所有ã?—ã?¦ã?„ã?ªã?„表ã?«ãƒˆãƒªã‚¬ã‚’定義ã?™ã‚‹ã?Ÿã‚?ã?«ã?¯ã€?ã??ã?®è¡¨ã?«TRIGGER権é™?ã‚’æŒ?ã?£ã?¦ã?„ã?ªã?‘れã?°ã?ªã‚Šã?¾ã?›ã‚“。 データベースã?®æ‰€æœ‰è€…ã?§ã?‚るユーザã?§ã?‚れã?°ã€?ã?©ã?®ã‚¹ã‚ーマã?®ã?©ã?®è¡¨ã?«å¯¾ã?—ã?¦ã‚‚トリガを作æˆ?ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚
トリガã?¯ãƒˆãƒªã‚¬ã?Œå®šç¾©ã?•れã?¦ã?„る表ã?¨ã?¯ç•°ã?ªã‚‹ã‚¹ã‚ーマã?«å®šç¾©ã?™ã‚‹ã?“ã?¨ã‚‚å?¯èƒ½ã?§ã?™ã€‚
é™?定ã?•れã?Ÿãƒˆãƒªã‚¬å??ã?®ã‚¹ã‚ーマå??ã?¯ã€?SYSã?§å§‹ã?¾ã?£ã?¦ã?¯ã?ªã‚Šã?¾ã?›ã‚“。
CREATE TRIGGER トリガå?? { AFTER | NO CASCADE BEFORE } { INSERT | DELETE | UPDATE [ OF 列å?? [, 列å??]* ] } ON 表å?? [ å?‚照節 ] [ FOR EACH { ROW | STATEMENT } ] [ MODE DB2SQL ] トリガã?•れるSQLæ–‡
表ã?®ã‚¤ãƒ™ãƒ³ãƒˆã?”ã?¨ã?«å®šç¾©ã?§ã??るトリガã?®æ•°ã?«ä¸Šé™?ã?¯ã?‚りã?¾ã?›ã‚“。ã?•らã?«æ›´æ–°ã?¸ã?®ãƒˆãƒªã‚¬ã?§ã?‚れã?°ã€?対象ã?¨ã?ªã‚‹åˆ—を明示ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚
発ç?«ã?®åŽŸå› ã?¨ã?ªã?£ã?Ÿã‚¤ãƒ™ãƒ³ãƒˆã?§ç?¾åœ¨å¤‰æ›´ã?•れã?Ÿæƒ…å ±ã‚’ãƒˆãƒªã‚¬ã?•れるSQLæ–‡ã?‹ã‚‰å?‚ç…§ã?™ã‚‹ã?“ã?¨ã?¯è‰¯ã??ã?‚ã‚‹ã?“ã?¨ã?§ã?™ã€‚ ã?¾ã?Ÿã€?トリガã?•れるSQLæ–‡ã?Œå?‚ç…§ã?—ã?ªã?‘れã?°ã?ªã‚‰ã?ªã?„ã?®ã?¯æ–°ã?—ã?„値(変更後ã€?後ã?®å€¤ã€?ã?¨è¨€ã?†ã?“ã?¨ã‚‚ã?§ã??ã?¾ã?™)ã?§ã?‚ã‚‹å ´å?ˆã?Œã?‚りã?¾ã?™ã€‚
Derbyã?«ã?¦ã€?発ç?«ã?—ã?ŸåŽŸå› ã?¨ã?ªã?£ã?Ÿã‚¤ãƒ™ãƒ³ãƒˆã?«ã‚ˆã‚Šå¤‰æ›´ã?•れã?Ÿæƒ…å ±ã‚’ãƒˆãƒªã‚¬ã?‹ã‚‰å?‚ç…§ã?™ã‚‹æ–¹æ³•ã?¯ã?²ã?¨ã?¤ã? ã?‘ã?§ã?¯ã?‚りã?¾ã?›ã‚“。変更ã?•れã?Ÿæƒ…å ±ã?¯ãƒˆãƒªã‚¬ã?•れるSQLæ–‡ã?‹ã‚‰ã€?é?·ç§»å¤‰æ•°ã?‚ã‚‹ã?„ã?¯é?·ç§»è¡¨ã?§å?‚ç…§ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ å?‚照節ã?«ã?¦é?·ç§»å¤‰æ•°ã?¸ã?®ç›¸é–¢å??や別å??ã‚’ã€?OLD/NEW AS 相関å??ã?¨ã?„ã?†æ›¸å¼?ã?§è¨˜è¿°ã?§ã??ã?¾ã?™ã€‚
REFERENCING OLD AS DELETEDROW
DELETE FROM HotelAvailability WHERE hotel_id = DELETEDROW.hotel_id
æ–‡ã?®ãƒˆãƒªã‚¬ã?§ã?¯ã€?é?·ç§»è¡¨ã‚’トリガã?•れるSQL文やトリガã?®è³‡æ ¼ã?§å?‚ç…§å?¯èƒ½ã?§ã?™ã€‚ å?‚照節ã?«ã?¦å?‚照表ã?¸ã?®ç›¸é–¢å??や別å??ã‚’ã€?OLD_TABLE/NEW_TABLE AS 相関å??ã?¨ã?„ã?†æ›¸å¼?ã?§è¨˜è¿°ã?§ã??ã?¾ã?™ã€‚
REFERENCING OLD_TABLE AS DeletedHotels
DELETE FROM HotelAvailability WHERE hotel_id IN (SELECT hotel_id FROM DeletedHotels)
å?‚照節ã?§ã?¯ã€?æ–°ã?—ã?„ã‚‚ã?®ã?¨å?¤ã?„ã‚‚ã?®ã?«ã??れã?žã‚Œä¸€ã?¤ã?¾ã?§ã€?相関å??ã?‚ã‚‹ã?„ã?¯è˜åˆ¥å?を定義ã?§ã??ã?¾ã?™ã€‚ ã?¾ã?Ÿã€?行ã?®ãƒˆãƒªã‚¬ã?«ã?¯é?·ç§»è¡¨ã?®è˜åˆ¥å?を定義ã?§ã??ã?ªã?„一方ã?§ã€?æ–‡ã?®ãƒˆãƒªã‚¬ã?«ã?¯é?·ç§»å¤‰æ•°ã?®ç›¸é–¢å??を定義ã?§ã??ã?¾ã?›ã‚“。
文�トリガ�トリガ�イベント毎�一回発����。トリガ�発��挿入・更新・削除��何行�変更�れ���関係�り��ん。
行ã?®ãƒˆãƒªã‚¬ã?¯ãƒˆãƒªã‚¬ã?®ã‚¤ãƒ™ãƒ³ãƒˆã?Œå½±éŸ¿ã?™ã‚‹è¡Œæ¯Žã?«ä¸€å›žç™ºç?«ã?—ã?¾ã?™ã€‚影響ã?Œä¸€è¡Œã?«ã‚‚å?Šã?°ã?ªã?‹ã?£ã?Ÿå ´å?ˆã€?トリガã?¯ç™ºç?«ã?—ã?¾ã?›ã‚“。
トリガã?•れるSQLæ–‡ã?§ã?¯ãƒˆãƒªã‚¬ã?Œå®šç¾©ã?•れã?Ÿè¡¨ä»¥å¤–ã?®ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã?®æƒ…å ±ã‚’å?‚ç…§ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ã??れらã?®ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã?®æƒ…å ±ã?Œç ´æ£„ã?•れã?¦ã?„ã?Ÿå ´å?ˆã€?トリガã?¯ç„¡åйã?¨ã?ªã‚Šã?¾ã?™ã€‚ 次回ã?®å®Ÿè¡Œã?«ã?¦ãƒˆãƒªã‚¬ã?®å†?コンパイルã?Œå¤±æ•—ã?—ã?Ÿå ´å?ˆã€?呼ã?³å‡ºã?—ã?¯ä¾‹å¤–を投ã?’ã?¦ã€?トリガを発ç?«ã?•ã?›ã?Ÿæ–‡ã?¯ãƒãƒ¼ãƒ«ãƒ?ックã?•れã?¾ã?™ã€‚
トリガã?•れるSQLæ–‡ã?®è©³ç´°ã?«ã?¤ã?„ã?¦ã?¯ã€?Derby ディベãƒãƒƒãƒ‘ーズ ガイドをå?‚ç…§ã?—ã?¦ã??ã? ã?•ã?„。
å?Œã?˜è¡¨ã?«å¯¾ã?—ã?¦ã€?å?Œã?˜ã‚¤ãƒ™ãƒ³ãƒˆã?®ã€?発ç?«ã?™ã‚‹æ™‚(トリガã?Œå‰?ã?‹å¾Œã?‹ã?¨ã?„ã?†æ„?味ã?§ã?™ã€‚)ã?Œå?Œã?˜ãƒˆãƒªã‚¬ã?Œè¤‡æ•°å®šç¾©ã?•れã?¦ã?„ã?Ÿå ´å?ˆã€?トリガã?¯ä½œæˆ?ã?•れã?Ÿé †ã?«å®Ÿè¡Œã?•れã?¾ã?™ã€‚
-- 文�トリガ
CREATE TRIGGER t1 NO CASCADE BEFORE UPDATE ON x
FOR EACH ROW MODE DB2SQL
values app.notifyEmail('Jerry', 'Table x is about to be updated');
CREATE TRIGGER FLIGHTSDELETE
AFTER DELETE ON FLIGHTS
REFERENCING OLD_TABLE AS DELETEDFLIGHTS
FOR EACH STATEMENT
DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID IN
(SELECT FLIGHT_ID FROM DELETEDFLIGHTS);
CREATE TRIGGER FLIGHTSDELETE3
AFTER DELETE ON FLIGHTS
REFERENCING OLD AS OLD
FOR EACH ROW
DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID = OLD.FLIGHT_ID;
トリガ��帰�最大16����。