CREATE TRIGGER
目录
CREATE TRIGGER
NAMESYNOPSIS
DESCRIPTION æè¿°
PARAMETERS åæ°
NOTES 注æ
EXAMPLES ä¾å
COMPATIBILITY å¼å®¹æ§
SEE ALSO åè§
è¯è
è·
NAME
CREATE TRIGGER - å®ä¹ä¸ä¸ªæ°ç触åå¨
SYNOPSIS
CREATE TRIGGER
name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )
DESCRIPTION æè¿°
CREATE TRIGGER å建ä¸ä¸ªæ°ç触åå¨ã 触åå¨å°ä¸æå®è¡¨å³è并ä¸å°å¨ç¹å®äºä»¶åçæ¶æ§è¡å£°æçå½æ° funcã
触åå¨å¯ä»¥å£°æ为å¨å¯¹è®°å½è¿è¡æä½ä¹åï¼å¨æ£æ¥çº¦æä¹åå INSERTï¼UPDATE æ DELETE æ§è¡åï¼ææä½å®æä¹åï¼å¨æ£æ¥çº¦æä¹ååå®æ INSERTï¼ UPDATE æ DELETE æä½ï¼è§¦åã å¦æ触åå¨å¨äºä»¶ä¹åï¼è§¦åå¨å¯è½ç¥è¿å½åè®°å½çæä½ææ¹å被æå¥çï¼å½åï¼è®°å½ï¼åªå¯¹ INSERT å UPDATE æä½ææï¼ã å¦æ触åå¨å¨äºä»¶ä¹åï¼æææ´æ¹ï¼åæ¬æåçæå¥ï¼ æ´æ°æå é¤å¯¹è§¦åå¨é½æ¯"å¯è§"çã
ä¸ä¸ª FOR EACH ROW æ§è¡æå®æä½ç触åå¨ä¸ºæä½ä¿®æ¹çæ¯ä¸è¡é½è°ç¨ä¸æ¬¡ãæ¯å¦ï¼ä¸ä¸ªå½±å 10 è¡ç DELETE å°å¯¼è´ä»»ä½å¨ç®æ å³ç³»ä¸ç ON DELETE 触åå¨ç¬ç«è°ç¨ 10 æ¬¡ï¼ æ¯ä¸ªè¢«å é¤çè¡ä¸æ¬¡ãç¸æ¯ä¹ä¸ï¼ä¸ä¸ªä¸ºæå®æä½ FOR EACH STATEMENT ç触åå¨åªæ¯ä¸ºä»»ä½æä½æ§è¡ä¸æ¬¡ï¼ä¸ç®¡æå¤å°è¡è¢«ä¿®æ¹ã ï¼ç¹å«æ¯ï¼ä¸ä¸ªä¿®æ¹é¶è¡çæä½ä»ç¶ä¼å¯¼è´ä»»ä½åéç FOR EACH STATEMENT 触åå¨çæ§è¡ãï¼
å¦æå¤ä¸ªåç±»åç触åå¨ä¸ºåæ ·äºä»¶åäºå®ä¹ï¼ é£ä¹å®ä»¬å°æç§å- æ¯é¡ºåºè¢«æ¿æ´»ã
SELECT 并ä¸æ´æ¹ä»»ä½è¡ï¼å æ¤ä½ ä¸è½å建 SELECT 触åå¨ãè¿ç§åºåä¸è§ååè§å¾æ´åéäºã
请åè Part V ‘‘Triggers’’ è·åæ´å¤ä¿¡æ¯ã
PARAMETERS åæ°
|
name |
èµäºæ°è§¦åå¨çå称ãå®å¿éåä»»ä½ä½ç¨äºåä¸è¡¨ç触åå¨ä¸åã
|
BEFORE |
||
|
AFTER |
å³å®è¯¥å½æ°æ¯å¨äºä»¶ä¹åè¿æ¯ä¹åè°ç¨ã
|
event |
INSERTï¼DELETE æ UPDATE å¶ä¸ä¹ä¸ã å®å£°æå»å触åå¨çäºä»¶ãå¤ä¸ªäºä»¶å¯ä»¥ç¨ OR 声æã | ||
|
table |
触åå¨ä½ç¨ç表å称ï¼å¯ä»¥ç¨æ¨¡å¼ä¿®é¥°ï¼ã |
FOR EACH ROW
FOR EACH STATEMENT
è¿äºé项声æ触åå¨è¿ç¨æ¯å¦ä¸ºè§¦åå¨äºä»¶å½±åçæ¯ä¸ªè¡è§¦åä¸æ¬¡ï¼ è¿æ¯åªä¸ºæ¯æ¡ SQL è¯å¥è§¦åä¸æ¬¡ãå¦æé½æ²¡æ声æï¼ FOR EACH STATEMENT æ¯ç¼ºçã
|
func |
ä¸ä¸ªç¨æ·æä¾çå½æ°ï¼å®å£°æ为ä¸æ¥ååæ°å¹¶ä¸è¿å trigger ç±»åã |
arguments
ä¸ä¸ªå¯éçç¨éå·åéçåæ°å表ï¼å®å°å¨è§¦åå¨æ§è¡çæ¶åæä¾ç»å½æ°ï¼ è¿äºåæ°æ¯ææ¬å串常éãä¹å¯ä»¥å¨è¿éåç®åçåå- åæ°å¼å¸¸éï¼ä½æ¯å®ä»¬ä¼è¢«è½¬æ¢æå串ã 请æ£æ¥å®ç°è¯è¨ä¸- å³äºå¦ä½å¨è§¦åå¨å½æ°ä¸è®¿é®è§¦åå¨åæ°çæè¿°ï¼ è¿äºåæ°å¯è½åæ®éçå½æ°åæ°ä¸åã
NOTES 注æ
è¦å¨ä¸ä¸ªè¡¨ä¸å建ä¸ä¸ªè§¦åå¨ï¼ç¨æ·å¿éå¨è¯¥è¡¨ä¸æ TRIGGER æéã
å¨ PostgreSQL çæ¬ 7.3 以åï¼ æ们å¿é¡»æ触åå¨å½æ°å£°æ为è¿åå ä½ç±»å opaqueï¼ èä¸æ¯ triggerã为äºæ¯æè£è½½èç转å¨æä»¶ï¼ CREATE TRIGGER å°æ¥åä¸ä¸ªå£°æ为è¿å opaque çå½æ°ï¼ ä½æ¯å®å°ååºä¸æ¡ NOTICE 并ä¸æå½æ°å£°æçè¿åç±»åæ¹æ triggerã
ç¨ DROP TRIGGER [drop_trigger(7)] è·åå¦ä½å é¤è§¦åå¨çä¿¡æ¯ã
EXAMPLES ä¾å
Section 33.4 ‘‘Triggers’’ åå«ä¸ä¸ªå®æ´çä¾åã
COMPATIBILITY å¼å®¹æ§
CREATE TRIGGER éç CREATE TRIGGER è¯å¥å®ç°äºä¸ä¸ª SQL99 æ åçåéã ï¼SQL92 é没æ触åå¨ï¼ ç®åä»ç¶ç¼ºå°ä¸é¢çåè½â¶
|
• |
SQL99 å许触åå¨ä¸ºæå®çå段çæ´æ°è¿è¡è§¦åï¼ä¹å°±æ¯è¯´ï¼AFTER UPDATE OF col1, col2ï¼ã | ||
|
• |
SQL99 åè®¸ä½ ä¸º "old" å "new" è¡æè表å®ä¹å«åï¼ç¨äºå®ä¹è§¦åå¨çå¨ä½ï¼ä¹å°±æ¯è¯´ï¼ CREATE TRIGGER ... ON tablename REFERENCING OLD ROW AS somename NEW ROW AS othername ...)ãå 为 PostgreSQL å许触åå¨è¿ç¨ä»¥ä»»ææ°éçç¨æ·å®ä¹è¯- è¨è¿è¡ä¹¦åï¼æ以访é®æ°æ®çå·¥ä½æ¯ç¨åè¯è¨ç¸å³çæ¹æ³å®ç°çã | ||
|
• |
PostgreSQL åªå许为触åçå¨ä½æ§è¡åå¨çè¿ç¨ãSQL99 å许æ§è¡ä¸äºå¶ä»ç SQL å½ä»¤ï¼ æ¯å¦é£ CREATE TABLE ä½ä¸ºè§¦åå¨å¨ä½ã è¿ä¸ªéæ- ¢å¹¶ä¸é¾ç»å¼ï¼åªè¦å建ä¸ä¸ªæ§è¡è¿äºå½ä»¤çåå¨è¿ç¨å³å¯ã |
SQL99 è¦æ±å¤ä¸ªè§¦åå¨åºè¯¥ä»¥å建çæ¶é´é¡ºåºæ§è¡ã PostgreSQL éç¨çæ¯æç§åå顺åºï¼ æ们认为è¿æ ·æ´å æ¹ä¾¿ã
ç¨ OR ç»ä¸ä¸ªè§¦åå¨å£°æå¤ä¸ªå¨ä½æ¯ PostgreSQL 对æ åçæ©å±ã
SEE ALSO åè§
CREATE FUNCTION [create_function(7)], ALTER TRIGGER [alter_trigger(l)], DROP TRIGGER [drop_trigger(l)]
è¯è
Postgresql ä¸æç½ç« ä½ä¼å¹³ <laser@pgsqldb.org>
è·
æ¬é¡µé¢ä¸æçç±ä¸æ
man
æå页计åæä¾ã
ä¸æ man
æå页计åï¼https://github.com/man-pages-zh/manpages-zh