CREATE RULE name AS ON event
TO object [ WHERE condition ]
DO [ INSTEAD ] action
ÕâÀï action ¿ÉÒÔÊÇ£º
NOTHING
|
query
|
( query ; query ... )
|
[ query ; query ... ]
´´½¨µÄ¹æÔòÃû£®
ʼþÊÇ SELECT£¬ UPDATE£¬DELETE »ò INSERT Ö®Ò»£®
¶ÔÏóÊÇ table »ò table.column£® £¨Ä¿Ç°Ö»ÓÐ table ÐÎʽʵ¼ÊÉÏÊÇʵÏÖÁ˵ģ®
ÈÎÒâ SQL ²¼¶ûÌõ¼þ±í´ïʽ£®Ìõ¼þ±í´ïʽ³ýÁËÒýÓà new ºÍ old Ö®Íâ²»ÄÜÒýÓÃÈÎºÎ±í£®
×é³É action µÄ²éѯ¿ÉÒÔÊÇÈκΠSQL SELECT£¬INSERT£¬ UPDATE£¬DELETE£¬»ò NOTIFY Óï¾äÖ®Ò»£®
ÔÚ condition ºÍ action À ÌØÊâ±íÃû×Ö new ºÍ old ¿ÉÒÔÓÃÓÚÖ¸ÏòÒýÓÃ±í £¨ object£© ÀïµÄÊýÖµ new ÔÚ ON INSERT ºÍ ON UPDATE ¹æÔòÀï ¿ÉÒÔÖ¸Ïò±»²åÈë»ò¸üеÄÐÂÐУ® old ÔÚ ON SELECT£¬ON UPDATE£¬ºÍ ON DELETE ¹æÔòÀï¿ÉÒÔÖ¸ÏòÏÖ´æµÄ±»Ñ¡Ôñ£¬¸üУ¬»òÕßɾ³ýµÄÐУ®
Postgres ¹æÔòϵͳ ÔÊÐíÎÒÃÇÔÚ´ÓÊý¾Ý¿â»ò±íÖиüУ¬ ²åÈë»òɾ³ý¶«Î÷ʱ¶¨ÒåÒ»¸ö¿ÉÑ¡µÄ¶¯×÷À´Ö´ÐС£Ä¿Ç°£¬¹æÔòÓÃÓÚʵÏÖ±íÊÓͼ¡£
¹æÔòµÄÓïÒâÊÇÔÚÒ»¸öµ¥¶ÀµÄ¼Ç¼Õý±»·ÃÎÊ£¬¸üУ¬²åÈë»òɾ³ýʱ£¬ ½«´æÔÚÒ»¸ö¾É¼Ç¼£¨ÓÃÓÚ¼ìË÷£¬¸üкÍɾ³ý£©ºÍÒ»¸öмǼ £¨ÓÃÓÚ¸üкÍ×·¼Ó£©£® Õâʱ¸ø¶¨Ê¼þÀàÐͺ͸ø¶¨Ä¿±ê¶ÔÏó£¨±í£©µÄËùÓйæÔò¶¼½«±»¼ì²é£¬ £¨Ë³Ðò²»¶¨£©£® Èç¹ûÔÚ WHERE £¨Èç¹ûÓУ©×Ó¾äÀïÃæËùÉùÃ÷µÄ condition£ ΪÕ棬ÄÇô action ²¿·ÖµÄ¹æÔò¾Í±»Ö´ÐУ® Èç¹ûÉùÃ÷ÁË INSTEAD£¬ÄÇô action ¾Í»á´úÌæÔÀ´µÄ²éѯ£»·ñÔòËüÔÚÖ´ÐÐÔÀ´µÄ²éѯ֮ǰ´¦Àí£® ÔÚ condition ºÍ action ÀïÃ棬 ÔھɼǼÀï×ֶεÄÊýÖµºÍ/»òмǼÀï×ֶεÄÊýÖµ±» old. attribute-name ºÍ new. attribute-name ´úÌ森
¹æÔòµÄ action ²¿·Ö¿ÉÒÔÓÉÒ»Ìõ»òÕ߶àÌõ²éѯ×é³É£®ÒªÐ´¶à¸ö²éѯ£¬ÓÃÔ²À¨»¡»òÕß·½À¨»¡ °ÑËüÃÇ°üΧÆðÀ´£® ÕâÑùµÄ²éѯ½«ÒÔÉùÃ÷µÄ˳ÐòÖ´ÐУ¨Ö»ÊÇÎÒÃDz»Äܱ£Ö¤ ¶ÔÒ»¸ö¶ÔÏóµÄ¶à¸ö¹æÔòµÄÖ´ÐÐ˳Ðò£©£® action »¹¿ÉÒÔÊÇ NOTHING ±íʾûÓж¯×÷£®Òò´Ë£¬Ò»¸ö DO INSTEAD NOTHING ¹æÔòÖÆÖ¹ÁË ÔÀ´µÄ²éѯµÄÔËÐУ¨µ±Ìõ¼þΪÕæʱ£©£» DO NOTHING ¹æÔòÊÇûÓÐÓõģ®
¹æÔòµÄ action ²¿·Ö Ö´ÐеÄʱºò´øÓкʹ¥·¢¶¯×÷µÄÓû§ÃüÁîÏàͬµÄÃüÁîºÍÊÂÎñ±êʶ·û£®
Ä¿Ç°£¬ON SELECT ¹æÔò±ØÐëÊÇÎÞÌõ¼þµÄ INSTEAD ¹æÔò²¢ÇÒ ±ØÐëÓÐÒ»¸öÓÉÒ»Ìõ SELECT ²éѯ×é³ÉµÄ¶¯×÷£® Òò´Ë£¬Ò»Ìõ ON SELECT ¹æÔòÓÐЧµØ°Ñ¶ÔÏó±íת³ÉÊÓͼ£¬ËüµÄ¿É¼ûÄÚÈÝ ÊǹæÔòµÄ SELECT ²éѯ·µ»ØµÄ¼Ç¼¶ø²»ÊÇ´æ´¢ÔÚ±íÖеÄÄÚÈÝ£¨Èç¹ûÓеĻ°£©£® ÎÒÃÇÈÏΪдһÌõ CREATE VIEW ÃüÁî±È´´½¨Ò»¸ö±íÈ»ºó¶¨ÒåÒ»Ìõ ON SELECT ¹æÔòÔÚÉÏÃæµÄ·ç¸ñÒªºÃ£®
ΪÁËÔÚ±íÉ϶¨Òå¹æÔò£¬Äã±ØÐëÓйæÔò¶¨ÒåȨÏÞ£® Óà GRANT ºÍ REVOKE ÐÞ¸ÄȨÏÞ£®
ÓÐÒ»¼þºÜÖØÒªµÄÊÂÇéÊÇÒª±ÜÃâÑ»·¹æÔò£® ±ÈÈ磬¾¡¹ÜÏÂÃæÁ½Ìõ¹æÔò¶¨Ò嶼ÊÇ Postgres ¿ÉÒÔ½ÓÊܵģ¬ select ÃüÁî»áµ¼Ö Postgres ±¨¸æ Ò»Ìõ´íÎóÐÅÏ¢£¬ÒòΪ¸Ã²éѯѻ·ÁËÌ«¶à´Î£º
Example 1. Ñ»·ÖØд¹æÔò×éºÏµÄÀý×Ó£º
CREATE RULE bad_rule_combination_1 AS
ON SELECT TO emp
DO INSTEAD
SELECT * FROM toyemp;
CREATE RULE bad_rule_combination_2 AS
ON SELECT TO toyemp
DO INSTEAD
SELECT * FROM emp;
ÏÂÃæÕâ¸ö¶Ô EMP µÄ²éѯÆóͼ½«µ¼Ö Postgres ²úÉúÒ»¸ö´íÎóÐÅÏ¢£¬ ÒòΪ¸Ã²éѯѻ·ÁËÌ«¶à´Î£º
SELECT * FROM emp;