trigger的管理

CREATE TRIGGER
名称
CREATE TRIGGER — 创建一个新触发器

语法
CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
    ON table FOR EACH { ROW | STATEMENT }
    EXECUTE PROCEDURE func ( arguments )
输入
name
触发器名称．
table
表名称．
event
INSERT，DELETE 或 UPDATE 之一．
funcname
一个用户提供的函数．

输出
CREATE
如果触发器成功创建，返回此信息．

描述
CREATE TRIGGER 将向现有数据库中增加一个新的触发器．触发器将与表 table 相联并且将执行声明的函数 funcname．
触发器可以声明为在对记录进行操作之前（在检查约束之前和 INSERT，UPDATE 或 DELETE 执行前）或之后（在检查约束之后和完成了 INSERT，UPDATE 或 DELETE 操作）触发．如果触发器在事件之前，触发器可能略过当前记录的操作或改变被插入的（当前）记录（只对 INSERT 和 UPDATE 操作有效）．如果触发器在事件之后，所有更改，包括最后的插入，更新或删除对触发器都是"可见"的．

请参考 PostgreSQL 程序员手册 中SPI 和触发器章节获取更多信息．

注意
CREATE TRIGGER 是一个 Postgres 语言扩展．
只有表所有者可以就此表创建一个触发器．

在当前的版本（v7.0），STATEMENT 触发器还没有实现．
 

请参考 DROP TRIGGER 获取如何删除触发器的信息．

用法
在插入或更新表 films 之前检查一下声明的分销商代码是否存在于 distributors 表中：
CREATE TRIGGER if_dist_exists
    BEFORE INSERT OR UPDATE ON films FOR EACH ROW
    EXECUTE PROCEDURE check_primary_key ('did', 'distributors', 'did');
在删除或更新一个分销商的内容之前，将所有记录移到表 films 中（译注：好象与例子意义不同）：
CREATE TRIGGER if_film_exists
    BEFORE DELETE OR UPDATE ON distributors FOR EACH ROW
    EXECUTE PROCEDURE check_foreign_key (1, 'CASCADE', 'did', 'films', 'did');
兼容性
SQL92
在 SQL92 里没有 CREATE TRIGGER 语句．
上面第二个例子可以使用一个 FOREIGN KEY 约束实现：

CREATE TABLE distributors (
    did      DECIMAL(3),
    name     VARCHAR(40),
    CONSTRAINT if_film_exists
    FOREIGN KEY(did) REFERENCES films
    ON UPDATE CASCADE ON DELETE CASCADE 
);

--------------------------------------------------------------------我的笔记:

creating database triggers
syntax:
create [or replace] trigger trigger_name
timing
event1 [or event2 or event3]
on table_name
trigger_body
---------
timing:before,after
event:insert,update [of column],delete
table_name: table/view name
trigger body:begining with either declare or begin ending with end,
or a call to a procedure
--------creating DML statement Triggers
create or replace trigger secure_emp
before
insert on employees
begin
  if(to_char(sysdate,'DY')in ('SAT','SUN'))or
    (to_char(sysdate,'HH24:MI')not between '08:00' and '18:00')
      then raise_application_error(-20500,'you may insert into
                                   employees table only during
                                   business hours.');
  end if;
end;
======
create or replace trigger secure_emp
before
insert or update or delete on employees
begin
  if(to_char(sysdate,'DY')in ('SAT','SUN'))or
    (to_char(sysdate,'HH24')not between '08' and '18')
      then
        if deleting then raise_application_error(-20500,'message1');
         elsif inserting then raise_application_error(-20501,'message2');                        
         elsif updating('salary') then raise_application_error(-20502,'message3');                        
         else then raise_application_error(-20503,'message4');
        end if;
  end if;
end;
======
create a DML row trigger
----syntax:
create [or replace] trigger trigger_name
timing
event1 [or event2 or event3]
on table_name
[referencing OLD as old | NEW as new]
for each row
[when condition]
trigger_body
------------example:
create or replace trigger restrict_salary
before insert or update of salary on employees
for each row
begin
if not(:NEW.job_id IN('AD_PRES','AD_VP')) and :NEW.salary>15000
  then raise_application_error(-20500,'message');
end if;
end;
-----------
data Operation             OLD                       NEW
insert                     null                      insert value        
update                     value before update       value after update       
delete                     value before delete       null
================
restricting a row trigger:

create or replace trigger restrict_salary
before insert or update of salary on employees
for each row
when (NEW.job_id='SA_REP')
begin
if inserting
  then :NEW.commission_pct:=0;
elsif :OLD.commission_pct is null
  then :NEW.commission_pct:=0;
else NEW.commission_pct:=:OLD.commission_pct+0.05;
end if;
end;
--------------creating an INSTEAD OF trigger
create [or replace] trigger trigger_name
INSTEAD OF
event1 [or event2 or event3]
on view_name
[referencing OLD as old | NEW as new]
for each row
trigger_body                             
==============managing triggers
disable or reenable a database trigger
syntax:alter trigger trigger_name disable|enable
disable or reenable all trigger for a table
alter table table_name disable|enable ALL TRIGGERS
recompile a trigger for a table:
alter trigger trigger_name compile
removing trigger: drop trigger trigger_name

======================================================
creating database trigger
syntax:
create [or replace] trigger trigger_name
timing
[ddl_event1 or ...] --create,alter,drop
on {database|schema}
trigger_body;

=======create trigger on system events
syntax:
create [or replace] trigger trigger_name
timing
   [database_event1 or ...]
on {database|schema}
trigger_body

database_event:
after servererror
after logon
before logoff
after startup
before shutdown
----------------example:
create or replace trigger log_trig
after logon on schema
begin
  insert into log_trig_table(user_id,log_date,action)
  values(user,sysdate,'logging on');
end;
/
----call statement
create [or replace] trigger trigger_name
timing
event1 [or event2...]
on table_name
[referencing OLD as old | NEW as new]
[for each row]
[when (condition)]
call procedure_name;