触发器函数

postgres=# CREATE TABLE t_trigger
postgres-# (
postgres(#     id integer NOT NULL,
postgres(#     nc text NOT NULL
postgres(# );
CREATE TABLE
postgres=# CREATE OR REPLACE FUNCTION t_trigger_insert_trigger_func() RETURNS trigger AS 
postgres-# $$
postgres$# BEGIN
postgres$#     IF NEW.nc = '' THEN    
postgres$#         NEW.nc = 'tdsql_pg_' || random()::text;
postgres$#     END IF;
postgres$#     RETURN NEW;
postgres$# END;
postgres$# $$ 
postgres-# 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
postgres=# INSERT INTO t_trigger values(1,'');
INSERT 0 1
postgres=# SELECT * FROM t_trigger ;
 id |           nc            
----+-------------------------
  1 | tdsql_pg_0.426093454472721
(1 row)

postgres=# CREATE OR REPLACE FUNCTION t_trigger_update_trigger_func() RETURNS trigger AS 
postgres-# $$
postgres$# BEGIN
postgres$#     --不准许t_trigger.nc值为 tdsql_pg
postgres$#     IF NEW.nc = 'tdsql_pg' THEN    
postgres$#         NEW.nc = OLD.nc ;
postgres$#     END IF;    
postgres$#     RETURN NEW;
postgres$# END;
postgres$# $$ 
postgres-# 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=# UPDATE t_trigger SET nc='tdsql_pg' WHERE id=1;
UPDATE 1
postgres=# SELECT * FROM t_trigger ;
 id |           nc            
----+-------------------------
  1 | tdsql_pg_0.426093454472721
(1 row)

postgres=# 

postgres=# CREATE OR REPLACE FUNCTION t_trigger_delete_trigger_func() RETURNS trigger AS 
postgres-# $$
postgres$# BEGIN
postgres$#     --不准许t_trigger.nc值为 tdsql_pg
postgres$#     IF OLD.nc = 'tdsql_pg' THEN    
postgres$#         RETURN NULL;
postgres$#         --RAISE EXCEPTION 'tdsql_pg不能被删除';
postgres$#     END IF;    
postgres$#     RETURN OLD;
postgres$# END;
postgres$# $$ 
postgres-# 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
postgres=# INSERT INTO t_trigger VALUES(2,'tdsql_pg');
INSERT 0 1
postgres=# SELECT * t_trigg

postgres=# SELECT * FROM t_trigger ;
 id |           nc            
----+-------------------------
  1 | tdsql_pg_0.426093454472721
  2 | tdsql_pg
(2 rows)
postgres=# DELETE FROM t_trigger WHERE id=2;
DELETE 0
postgres=# SELECT * FROM t_trigger ;
 id |           nc            
----+-------------------------
  1 | tdsql_pg_0.426093454472721
  2 | tdsql_pg
(2 rows)

postgres=# drop TRIGGER t_trigger_insert_trigger on t_trigger;
DROP TRIGGER