添加触发器

• 复制
  复制成功

postgres=# create table t_trigger(f1 int,f2 int);
CREATE TABLE
postgres=# CREATE OR REPLACE FUNCTION t_trigger_insert_trigger_func () RETURNS trigger AS
$body$
BEGIN
   if NEW.f2
           NEW.f2=0;
       endif;    
   RETURN NEW;
  END;
$body$
LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# CREATE TRIGGER t_trigger_insert_trigger BEFORE INSERT ON t_trigger FOR EACH ROW EXECUTE PROCEDURE t_trigger_insert_trigger_func();
CREATE TRIGGER
上面触发器的作用是在插入记录时，如果f2字段值小于0时，则将f2的值修改成0，效果如下

postgres=# insert into t_trigger values(1,-1);
INSERT 0 1
postgres=# select * from t_trigger;        
 f1 |f2 
----+----
  1|  0

• 复制
  复制成功

postgres=# CREATE OR REPLACE FUNCTION t_trigger_update_trigger_func () RETURNS trigger AS
$body$
BEGIN
   if NEW.f2
        NEW.f2=OLD.f2;
       endif;    
   RETURN NEW;
END;
$body$
LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# CREATE TRIGGER t_trigger_update_trigger BEFORE UPDATE ON t_trigger FOR EACH ROW EXECUTE PROCEDURE t_trigger_update_trigger_func();
CREATE TRIGGER
postgres=#

上面触发器的作用是在修改记录时，如果f2字段值小于0时，则将f2的值修改成原值，效果如下

postgres=# update t_trigger set f2=-1 where f1=1;
UPDATE 1
postgres=# select * from t_trigger;        
 f1 |f2 
----+----
  1|  0

postgres=# update t_trigger set f2=1 where f1=1;  
UPDATE 1
postgres=# select * from t_trigger;        
 f1 |f2 
----+----
  1|  1

• 复制
  复制成功

postgres=# CREATE OR REPLACE FUNCTION t_trigger_delete_trigger_func () RETURNS trigger AS
$body$
BEGIN
    if OLD.f2=0 then 
        RETURN NULL;
       endif;    
    RETURN OLD;    
END;
$body$
LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# CREATE TRIGGER t_trigger_delete_trigger BEFORE DELETE ON t_trigger FOR EACH ROW EXECUTE PROCEDURE t_trigger_delete_trigger_func();
CREATE TRIGGER

上面触发器的作用是在删除记录时，如果f2字段值等于0时，则不删除记录，效果如下

postgres=# delete from t_trigger where f2=0;
DELETE 0
postgres=# select * from t_trigger;        
 f1 |f2 
----+----
  1|  0

postgres=# update t_trigger set f2=10;
UPDATE 1
postgres=# select * from t_trigger;        
 f1 |f2 
----+----
  1 |10
(1 row)

postgres=# delete from t_trigger where f1=1;
DELETE 1
postgres=# select * from t_trigger;        
 f1 |f2 
----+----
(0 rows)

• 复制
  复制成功

postgres=# create table t_trigger_mulevent(f1 int,f2 int);
CREATE TABLE
CREATE OR REPLACE FUNCTION t_trigger_mulevent_func () RETURNS trigger AS
$body$
BEGIN
   if NEW.f2
           NEW.f2=0;
       endif;    
   RETURN NEW;
END;
$body$
LANGUAGE plpgsql;
CREATE FUNCTION
postgres=#

postgres=# CREATE TRIGGER t_trigger_insert_update_trigger BEFORE INSERT OR UPDATE ON t_trigger_mulevent FOR EACH ROW EXECUTE PROCEDURE t_trigger_mulevent_func();
CREATE TRIGGER

上面触发器的作用是在插入和更新记录时，如果f2字段值小于0时，则f2值重置为0，效果如下

postgres=# insert into t_trigger_mulevent values(1,-10);
INSERT 0 1
postgres=# select * from t_trigger_mulevent;   
 f1 |f2 
----+----
  1|  0
(1 row)

postgres=# update t_trigger_mulevent setf2=-10;
UPDATE 1
postgres=# select * from t_trigger_mulevent;   
 f1 |f2 
----+----
  1|  0
(1 row)