´ÙÀ½ Àý¿¡¼´Â ±×·¯ÇÑ ÀÀ¿ëÇÁ·Î±×·¥ ±¸Á¶ÀÇ ¸ðµ¨·Î Æ®¸®°Å¿Í Á¦ÇÑÁ¶°ÇÀ» Ȱ¿ëÇÏ´Â ¹æ¹ý¿¡ ´ëÇÏ¿© ¼³¸íÇÕ´Ï´Ù. Æ®¸®°Å¸¦ »ç¿ëÇÏ¿© ´ÙÀ½À» ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.
LOB °ª¿¡¼, ¿ÏÀüÇÑ ÀüÀÚ ¿ìÆíÀÌ ELECTRONIC_MAIL Å×À̺íÀÇ Ä÷³ MESSAGE¿¡ ÀúÀåµË´Ï´Ù. ÀüÀÚ ¿ìÆíÀ» Á¶ÀÛÇϱâ À§Çؼ, UDF°¡ SQL¹® ¾È¿¡¼ ±×·¯ÇÑ Á¤º¸¸¦ ÇÊ¿ä·ÎÇÒ ¶§¸¶´Ù ¸Þ½ÃÁö Ä÷³À¸·ÎºÎÅÍ Á¤º¸¸¦ ÃßÃâÇÕ´Ï´Ù.
Á¶È¸´Â Á¤º¸¸¦ ÃßÃâÇÏÁö ¾Ê°í, À̸¦ ¸í½ÃÀûÀ¸·Î Å×À̺í Ä÷³À¸·Î ÀúÀåÇÔ¿¡ ÁÖÀÇÇϽʽÿÀ. À̰ÍÀÌ ¿Ï·áµÇ¸é, UDF°¡ ¹Ýº¹ÀûÀ¸·Î È£ÃâµÇÁö ¾Ê¾Æ¼ »Ó¸¸ ¾Æ´Ï¶ó ÀÌÁ¦ ÃßÃâÇÑ Á¤º¸¿¡ »öÀÎÀ» Á¤ÀÇÇÒ ¼ö ÀÖÀ¸¹Ç·Î, Á¶È¸ ¼º´ÉÀÌ Çâ»óµË´Ï´Ù.
Æ®¸®°Å¸¦ »ç¿ëÇÏ¿©, »ç¿ëÀÚ´Â »õ·Î¿î ÀüÀÚ ¿ìÆíÀÌ µ¥ÀÌÅͺ£À̽º¿¡ ÀúÀåµÉ ¶§¸¶´Ù ÀÌ Á¤º¸¸¦ ÃßÃâÇÒ ¼ö ÀÖ½À´Ï´Ù. À̰ÍÀ» ÀÌ·ç±â À§ÇØ ´ÙÀ½°ú °°ÀÌ »õ·Î¿î Ä÷³À» ELECTRONIC_MAIL Å×ÀÌºí¿¡ Ãß°¡Çϰí, BEFORE Æ®¸®°Å¸¦ Á¤ÀÇÇÏ¿© ´ÙÀ½°ú °°ÀÌ ÇØ´ç Á¤º¸¸¦ ÃßÃâÇÕ´Ï´Ù.
ALTER TABLE ELECTRONIC_MAIL ADD COLUMN SENDER VARCHAR (200) ADD COLUMN RECEIVER VARCHAR (200) ADD COLUMN SENT_ON DATE ADD COLUMN SUBJECT VARCHAR (200) CREATE TRIGGER EXTRACT_INFO NO CASCADE BEFORE INSERT ON ELECTRONIC_MAIL REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET N.SENDER = SENDER(N.MESSAGE); SET N.RECEIVER = RECEIVER(N.MESSAGE); SET N.SENT_ON = SENDING_DATE(N.MESSAGE); SET N.SUBJECT = SUBJECT(N.MESSAGE); END
ÀÌÁ¦ »õ·Î¿î ÀüÀÚ ¿ìÆíÀÌ ¸Þ½ÃÁö Ä÷³¿¡ »ðÀ﵃ ¶§¸¶´Ù, ¼Û½ÅÀÚ, ¼ö½ÅÀÚ, ¼Û½ÅµÈ ³¯Â¥ ¹× ÁÖÁ¦°¡ ¸Þ½ÃÁö¿¡¼ ÃßÃâµÇ¾î º°µµÀÇ Ä÷³¿¡ ÀúÀåµË´Ï´Ù.
ÀüÀÚ¿ìÆí ÁÖ¼Ò°¡ Ʋ·ÈÀ» °æ¿ì, ¼Û½ÅÇÑ ¿ìÆíÀ» ¼Û½ÅµÇÁö ¸øÇϵµ·Ï, ¹è´ÞµÇÁö ¾Ê°í ´Ù½Ã »ç¿ëÀÚ¿¡°Ô ¸®ÅϵǾî, ÀüÀÚ ¿ìÆí Å×ÀÌºí¿¡ ÀúÀåµÇÁö ¾Êµµ·Ï ÇϰíÀÚ ÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ.
À̸¦ À§Çؼ »ç¿ëÀڴ ƯÁ¤ SQL INSERT¹®ÀÇ ½ÇÇàÀ» ±ÝÁöÇØ¾ß ÇÕ´Ï´Ù. À̰ÍÀ» ¼öÇàÇϱâ À§ÇÑ ¹æ¹ýÀº µÎ °¡Áö°¡ ÀÖ½À´Ï´Ù.
CREATE TRIGGER BLOCK_INSERT NO CASCADE BEFORE INSERT ON ELECTRONIC_MAIL REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (SUBJECT(N.MESSAGE) = 'undelivered mail') BEGIN ATOMIC SIGNAL SQLSTATE '85101' ('Attempt to insert undelivered mail'); END
ALTER TABLE ELECTRONIC_MAIL ADD CONSTRAINT NO_UNDELIVERED CHECK (SUBJECT <> 'undelivered mail')
Á¦ÇÑÁ¶°ÇÀÇ ¼±¾ðÀû ¼º°ÝÀÇ ÀÌÁ¡À¸·Î ÀÎÇØ Á¦ÇÑÁ¶°ÇÀº ÀϹÝÀûÀ¸·Î Æ®¸®°Å ´ë½Å Á¤ÀǵǾî¾ß ÇÕ´Ï´Ù.
»ç¿ëÀÚÀÇ È¸»ç¿¡¼´Â °í°´ÀÇ ºÒ¸¸À» ´Ù·ç´Â ¸ðµç ÀüÀÚ ¿ìÆíÀÇ CC ¸ñ·Ï¿¡ ¸¶ÄÉÆÃ °ü¸®ÀÚ Mr. NelsonÀÌ ÀÖ¾î¾ß ÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ. À̰ÍÀº ±ÔÄ¢À̱⠶§¹®¿¡, À̰ÍÀ» ´ÙÀ½ Áß Çϳª¿Í °°Àº Á¦ÇÑÁ¶°ÇÀ¸·Î Ç¥ÇöÇϰíÀÚ ÇÒ ¼ö ÀÖ½À´Ï´Ù(À̰ÍÀ» È®ÀÎÇÒ CC_LIST UDFÀÇ Á¸À縦 °¡Á¤)
ALTER TABLE ELECTRONIC_MAIL ADD CHECK (SUBJECT <> 'Customer complaint' OR CONTAINS (CC_LIST(MESSAGE), 'nelson@vnet.ibm.com') = 1)
±×·¯³ª ±×·¯ÇÑ Á¦ÇÑÁ¶°ÇÀº cc ¸ñ·Ï¿¡ ¸¶ÄÉÆÃ °ü¸®ÀÚ°¡ ¾ø´Â °í°´ ºÒ¸¸À» ´Ù·é ÀüÀÚ ¿ìÆíÀÇ »ðÀÔÀ» ±ÝÇÏ°Ô µË´Ï´Ù. À̰ÍÀº ºÐ¸í ȸ»çÀÇ ºñÁö´Ï½º ±ÔÄ¢ÀÌ ÀǵµÇÏ´Â ¹Ù´Â ¾Æ´Õ´Ï´Ù. ¿øÇÏ´Â °ÍÀº ¸¶ÄÉÆÃ °ü¸®ÀÚ·Î º¹»çµÇÁö ¾Ê¾Ò´ø °í°´ ºÒ¸¸À» ´Ù·é ÀüÀÚ ¿ìÆíÀ» ¸¶ÄÉÆÃ °ü¸®ÀÚ¿¡°Ô À̼ÛÇÏ´Â °ÍÀÔ´Ï´Ù. ±×·¯ÇÑ ºñÁö´Ï½º ±ÔÄ¢Àº, ¼±¾ðÀû Á¦ÇÑÁ¶°ÇÀ¸·Î´Â Ç¥ÇöÇÒ ¼ö ¾ø´Â Á¶Ä¡¸¦ ÇÊ¿ä·Î Çϱ⠶§¹®¿¡, Æ®¸®°Å·Î¸¸ Ç¥ÇöµÉ ¼ö ÀÖ½À´Ï´Ù. Æ®¸®°Å´Â E_MAIL À¯ÇüÀÇ ¸Å°³º¯¼ö¿Í ¹®ÀÚ¿À» °®´Â SEND_NOTE ÇÔ¼öÀÇ Á¸À縦 °¡Á¤ÇÕ´Ï´Ù.
CREATE TRIGGER INFORM_MANAGER AFTER INSERT ON ELECTRONIC_MAIL REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (N.SUBJECT = 'Customer complaint' AND CONTAINS (CC_LIST(MESSAGE), 'nelson@vnet.ibm.com') = 0) BEGIN ATOMIC VALUES(SEND_NOTE(N.MESSAGE, 'nelson@vnet.ibm.com')); END
ÀÌÁ¦ »ç¿ëÀÚÀÇ ÀÏ¹Ý °ü¸®ÀÚ´Â ÃÖ±Ù 72½Ã°£ µ¿¾È ¼¼Àå ÀÌ»óÀÇ ºÒ¸¸À» ¼Û½ÅÇÑ °í°´ÀÇ À̸§À» º°µµÀÇ Å×ÀÌºí¿¡ º¸°üÇϰíÀÚ ÇÕ´Ï´Ù. ÀÏ¹Ý °ü¸®ÀÚ´Â ¶ÇÇÑ °í°´ÀÇ À̸§ÀÌ ÀÌ Å×ÀÌºí¿¡ ÇÑ ¹ø ÀÌ»ó »ðÀ﵃ ¶§¸¶´Ù ÀÌ¿¡ ´ëÇÑ ÅëÁö¸¦ ¹Þ°íÀÚ ÇÕ´Ï´Ù.
±×·¯ÇÑ Á¶Ä¡¸¦ Á¤ÀÇÇÏ·Á¸é, ´ÙÀ½À» Á¤ÀÇÇϽʽÿÀ.
CREATE TABLE UNHAPPY_CUSTOMERS ( NAME VARCHAR (30), EMAIL_ADDRESS VARCHAR (200), INSERTION_DATE DATE)
CREATE TRIGGER STORE_UNHAPPY_CUST AFTER INSERT ON ELECTRONIC_MAIL REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (3 <= (SELECT COUNT(*) FROM ELECTRONIC_MAIL WHERE SENDER = N.SENDER AND SENDING_DATE(MESSAGE) > CURRENT DATE - 3 DAYS) ) BEGIN ATOMIC INSERT INTO UNHAPPY_CUSTOMERS VALUES ((SELECT NAME FROM CUSTOMERS WHERE E_MAIL_ADDRESS = N.SENDER), N.SENDER, CURRENT DATE); END
CREATE TRIGGER INFORM_GEN_MGR AFTER INSERT ON UNHAPPY_CUSTOMERS REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (1 <(SELECT COUNT(*) FROM UNHAPPY_CUSTOMERS WHERE EMAIL_ADDRESS = N.EMAIL_ADDRESS) ) BEGIN ATOMIC VALUES(SEND_NOTE('Check customer:' CONCAT N.NAME, 'bigboss@vnet.ibm.com')); END