CREATE RULE

Name

CREATE RULE  --  ¶¨ÒåÒ»¸öеÄÖØд¹æÔò

Synopsis

CREATE [ OR REPLACE ] RULE name AS ON event
    TO object [ WHERE condition ]
    DO [ INSTEAD ] action

ÕâÀï action ¿ÉÒÔÊÇ£º

NOTHING
| query
| ( query ; query ... )
  

ÊäÈë

name

´´½¨µÄ¹æÔòÃû£®

event

ʼþÊÇ SELECT£¬ UPDATE£¬DELETE »ò INSERT Ö®Ò»£®

object

¶ÔÏóÊÇ table »ò table.column£® £¨Ä¿Ç°Ö»ÓÐ table ÐÎʽʵ¼ÊÉÏÊÇʵÏÖÁ˵ģ®

condition

ÈÎÒâ SQL Ìõ¼þ±í´ïʽ(·µ»Ø boolean)£®Ìõ¼þ±í´ïʽ³ýÁËÒýÓà new ºÍ old Ö®Íâ²»ÄÜÒýÓÃÈÎºÎ±í£®

query

×é³É action µÄ²éѯ¿ÉÒÔÊÇÈκΠSQL SELECT£¬INSERT£¬ UPDATE£¬DELETE£¬»ò NOTIFY Óï¾äÖ®Ò»£®

ÔÚ condition ºÍ action À ÌØÊâ±íÃû×Ö new ºÍ old ¿ÉÒÔÓÃÓÚÖ¸ÏòÒýÓÃ±í £¨ object£© ÀïµÄÊýÖµ new ÔÚ ON INSERT ºÍ ON UPDATE ¹æÔòÀï ¿ÉÒÔÖ¸Ïò±»²åÈë»ò¸üеÄÐÂÐУ® old ÔÚ ON UPDATE£¬ºÍ ON DELETE ¹æÔòÀï¿ÉÒÔÖ¸ÏòÏÖ´æµÄ±»¸üУ¬»òÕßɾ³ýµÄÐУ®

Êä³ö

CREATE

³É¹¦´´½¨¹æÔòºóµÄ·µ»ØÐÅÏ¢£®

ÃèÊö

CREATE RULE ¶¨ÒåÒ»¸öÊÊÓÃÓÚÌض¨±í»òÕßÊÓͼµÄйæÔò¡£ CREATE OR REPLACE RULE ҪôÊÇ´´½¨Ò»¸öйæÔò£¬ ҪôÊÇÓÃÒ»¸öͬ±íÉϵÄͬÃû¹æÔòÌæ»»ÏÖÓйæÔò¡£

PostgreSQL ¹æÔòϵͳ ÔÊÐíÎÒÃÇÔÚ´ÓÊý¾Ý¿â»ò±íÖиüУ¬ ²åÈë»òɾ³ý¶«Î÷ʱ¶¨ÒåÒ»¸ö¿ÉÑ¡µÄ¶¯×÷À´Ö´ÐС£Ä¿Ç°£¬¹æÔòÓÃÓÚʵÏÖ±íÊÓͼ¡£

¹æÔòµÄÓïÒâÊÇÔÚÒ»¸öµ¥¶ÀµÄ¼Ç¼Õý±»·ÃÎÊ£¬¸üУ¬²åÈë»òɾ³ýʱ£¬ ½«´æÔÚÒ»¸ö¾É¼Ç¼£¨ÓÃÓÚ¼ìË÷£¬¸üкÍɾ³ý£©ºÍÒ»¸öмǼ £¨ÓÃÓÚ¸üкÍ×·¼Ó£©£® Õâʱ¸ø¶¨Ê¼þÀàÐͺ͸ø¶¨Ä¿±ê¶ÔÏó£¨±í£©µÄËùÓйæÔò¶¼½«±»¼ì²é£¬ £¨Ë³Ðò²»¶¨£©£® Èç¹ûÔÚ WHERE £¨Èç¹ûÓУ©×Ó¾äÀïÃæËùÉùÃ÷µÄ condition£ ΪÕ棬ÄÇô action ²¿·ÖµÄ¹æÔò¾Í±»Ö´ÐУ® Èç¹ûÉùÃ÷ÁË INSTEAD£¬ÄÇô action ¾Í»á´úÌæÔ­À´µÄ²éѯ£»·ñÔò£¬Èç¹ûÊÇ ON INSERT ÄÇôËüÔÚÔ­À´µÄ²éѯ֮ºó Ö´ÐУ¬Èç¹ûÊÇ ON UPDATE »òÕß ON DELETE£¬ÄÇôËüÔÚÔ­À´µÄ²éѯ֮ǰִÐУ® ÔÚ 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 ¹æÔòÔÚÉÏÃæµÄ·ç¸ñÒªºÃ£®

CREATE VIEW ´´½¨Ò»¸öÐéÄâ±í(ûÓÐϲãµÄ´æ´¢) ÒÔ¼°Ïà¹ØµÄ ON SELECT ¹æÔò£®ÏµÍ³²»ÔÊÐí¶ÔÊÓͼ½øÐиüУ¬ ÒòΪËüÖªµÀÔÚÊÓͼÉÏûÓÐÕæÕýµÄ±í£®Äã¿ÉÒÔ´´½¨Ò»¸ö¿ÉÒÔ¸üеÄÊÓͼµÄ»Ã¾õ£¬ ·½·¨ÊÇÔÚÊÓͼÉ϶¨Òå ON INSERT£¬ON UPDATE£¬ºÍ ON DELETE ¹æÔò (»òÕßÂú×ãÄãÐèÒªµÄÈκÎÉÏÊö¹æÔòµÄ×Ó¼¯)£¬ÓúÏÊʵĶÔÆäËü±íµÄ¸üÐÂÌæ»» ÔÚÊÓͼÉϸüеĶ¯×÷£®

Èç¹ûÄãÏëÔÚÊÓͼ¸üÐÂÉÏʹÓÃÌõ¼þ¹æÔò£¬ÄÇôÕâÀï¾ÍÓÐÒ»¸ö²¹³ä¡Ã ¶ÔÄãÏ£ÍûÔÚÊÓͼÉÏÔÊÐíµÄÿ¸ö¶¯×÷£¬Ä㶼±ØÐëÓÐÒ»¸ö ÎÞÌõ¼þµÄ INSTEAD ¹æÔò£®Èç¹û¹æÔòÊÇÓÐÌõ¼þµÄ£¬»òÕßËü²»ÊÇ INSTEAD£¬ ÄÇôϵͳÈÔ½«¾Ü¾øÖ´Ðиüж¯×÷µÄÆóͼ£¬ÒòΪËüÈÏΪËü×îÖÕ»áÔÚijÖÖ ³Ì¶ÈÉÏÔÚÐéÄâ±íÉÏÖ´Ðж¯×÷£® Èç¹ûÄãÏë´¦ÀíÌõ¼þ¹æÔòÉϵÄËùÓÉÓÐÓõÄÇé¿ö£¬ÄÇÒ²¿ÉÒÔ£»Ö»ÐèÒªÔö¼Ó Ò»¸öÎÞÌõ¼þµÄ DO INSTEAD NOTHING ¹æÔòÈ·±£ÏµÍ³Ã÷°×Ëü½«¾ö²»»á±» µ÷ÓÃÀ´¸üÐÂÐéÄâ±í¾Í¿ÉÒÔÁË£®È»ºó°ÑÌõ¼þ¹æÔò×ö³É·Ç INSTEAD£» ÔÚÕâÖÖÇé¿öÏ£¬Èç¹ûËüÃDZ»´¥·¢£¬ÄÇôËüÃǾÍÔö¼Óµ½È±Ê¡µÄ INSTEAD NOTHING ¶¯×÷ÖУ®

×¢Òâ

ΪÁËÔÚ±íÉ϶¨Òå¹æÔò£¬Äã±ØÐëÓйæÔò¶¨ÒåȨÏÞ£® Óà GRANT ºÍ REVOKE ÐÞ¸ÄȨÏÞ£®

ÓÐÒ»¼þºÜÖØÒªµÄÊÂÇéÊÇÒª±ÜÃâÑ­»·¹æÔò£® ±ÈÈ磬¾¡¹ÜÏÂÃæÁ½Ìõ¹æÔò¶¨Ò嶼ÊÇ PostgreSQL ¿ÉÒÔ½ÓÊܵģ¬ select ÃüÁî»áµ¼Ö PostgreSQL ±¨¸æ Ò»Ìõ´íÎóÐÅÏ¢£¬ÒòΪ¸Ã²éѯѭ»·ÁËÌ«¶à´Î£º

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µÄ²éѯÆóͼ½«µ¼Ö PostgreSQL ²úÉúÒ»¸ö´íÎóÐÅÏ¢£¬ ÒòΪ¸Ã²éѯѭ»·ÁËÌ«¶à´Î£º

SELECT * FROM emp;

Ä¿Ç°£¬Èç¹ûÒ»¸ö¹æÔò°üº¬Ò»¸ö NOTIFY ²éѯ£¬ÄÇô¸Ã NOTIFY ½«±» ÎÞÌõ¼þÖ´ÐÐ --- Ò²¾ÍÊÇ˵£¬Èç¹û¹æÔò²»Ê©¼Óµ½ÈκÎÐÐÉÏÍ·£¬ ¸Ã NOTIFY Ò²»á±»·¢³ö£®±ÈÈ磬ÔÚ

CREATE RULE notify_me AS ON UPDATE TO mytable DO NOTIFY mytable;

UPDATE mytable SET name = 'foo' WHERE id = 42;
      

Àһ¸ö NOTIFY ʼþ½«ÔÚ UPDATE µÄʱºò·¢³ö£¬²»¹ÜÊÇ·ñÓÐijÐÐµÄ id = 42£®ÕâÊÇÒ»¸öʵÏÖµÄÏÞÖÆ£¬½«À´µÄ°æ±¾Ó¦¸ÃÐÞ²¹Õâ¸ö벡£®

¼æÈÝÐÔ

SQL92

CREATE RULE PostgreSQL ÓïÑÔµÄÀ©Õ¹£® ÔÚSQL92ÀïûÓÐCREATE RULE Óï¾ä£®