Postgres触发器

postgres的触发器需要一个触发器函数，返回值为trigger，在pgadim3上是看不到触发器函数的，只有在ems上才能看见两个。

 

触发器是一个特殊的存储过程
常见的触发器有三种：分别应用于Insert , Update , Delete 事件
Update有 new,old   (deleted,inerted)
Insert只有new      (inerted)
Delete只有old       (deleted)

 

 

两种插入语句的差别

 

select * from talbename    ------------------选择

 

select * into tablename1

from tablename2   

 --------------------------插入不存在的表

 

 

insert into tablename1(field1,field2)

select * from talbename2

---------------------------插入已经存在的表

 

 

更新行和更新列

delete *                           --------对行作用
from tablename
where ID=5;

 

 

 update tablename          ---------对列作用
set S001=null
where "ID"=5;

 

 

当pressureData表中拆入数据，在sensorPressure表中拆入对应的数据(经过转化后)

 

CREATE TRIGGER "pressureDataToSensorPressure" AFTER INSERT ON "public"."pressureData" FOR EACH ROW EXECUTE PROCEDURE "public"."trigerpressureDataToSensorPressure"();

 

触发器函数：

DECLARE v_transactionID integer; v_repositoryID integer; v_OccourTime timestamp without time zone; v_value text; v_index integer; v_temp varchar; v_cgq numeric[]; v_sensorCategory; BEGIN v_index :=1; IF (TG_OP = 'INSERT') THEN --SELECT "TransactionID", "RepositoryID","Value","OccourTime" INTO v_transactionID,v_repositoryID,v_value,v_OccourTime FROM "INSERTED"; v_transactionID :=NEW."TransactionID"; v_repositoryID :=NEW."RepositoryID"; v_value :=NEW."Value"; v_occourTime :=NEW."OccourTime"; while v_index <= 60 loop v_temp :=split_part(v_value,'/n',v_index); v_temp :=trim(both '/n/r' from v_temp); v_cgq[v_index] :=hex_to_int(v_temp); --项目新增了温度传感器，和多个仓的不同类型传感器，需要修改存储过程sensorCategory代表传感器类型 select "sensorCategory" into v_sensorCategory where "repositoryID"=v_repositoryID and "sensorCal"='重量' if v_sensorCategory = 1 then v_cgq[v_index] :=v_cgq[v_index]/100*5-20; end if; v_index :=v_index+1; end loop; INSERT INTO "sensorPressure"( "TransactionID","RepositoryID","OccourTime","S001", "S002", "S003", "S004", "S005", "S006", "S007", "S008", "S009", "S010", "S011", "S012", "S013", "S014", "S015", "S016", "S017", "S018", "S019", "S020", "S021", "S022", "S023", "S024", "S025", "S026", "S027", "S028", "S029", "S030", "S031", "S032", "S033", "S034", "S035", "S036", "S037", "S038", "S039", "S040", "S041", "S042", "S043", "S044", "S045", "S046", "S047", "S048", "S049", "S050", "S051", "S052", "S053", "S054", "S055", "S056", "S057", "S058", "S059", "S060") VALUES (v_transactionid,v_repositoryID,v_OccourTime,v_cgq[1], v_cgq[2], v_cgq[3], v_cgq[4], v_cgq[5], v_cgq[6], v_cgq[7], v_cgq[8], v_cgq[9], v_cgq[10], v_cgq[11], v_cgq[12], v_cgq[13], v_cgq[14], v_cgq[15], v_cgq[16], v_cgq[17], v_cgq[18], v_cgq[19], v_cgq[20], v_cgq[21], v_cgq[22], v_cgq[23], v_cgq[24], v_cgq[25], v_cgq[26], v_cgq[27], v_cgq[28], v_cgq[29], v_cgq[30], v_cgq[31], v_cgq[32], v_cgq[33], v_cgq[34], v_cgq[35], v_cgq[36], v_cgq[37], v_cgq[38], v_cgq[39], v_cgq[40], v_cgq[41], v_cgq[42], v_cgq[43], v_cgq[44], v_cgq[45], v_cgq[46], v_cgq[47], v_cgq[48], v_cgq[49], v_cgq[50], v_cgq[51], v_cgq[52], v_cgq[53], v_cgq[54], v_cgq[55], v_cgq[56], v_cgq[57], v_cgq[58], v_cgq[59], v_cgq[60]); END IF; RETURN NEW; END;

 

 

参考：http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html 

 

 

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers.

OLD

Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers.

TG_NAME

Data type name; variable that contains the name of the trigger actually fired.

TG_WHEN

Data type text; a string of either BEFORE or AFTER depending on the trigger's definition.

TG_LEVEL

Data type text; a string of either ROW or STATEMENT depending on the trigger's definition.

TG_OP

Data type text; a string of INSERT, UPDATE, or DELETE telling for which operation the trigger was fired.

TG_RELID

Data type oid; the object ID of the table that caused the trigger invocation.

TG_RELNAME

Data type name; the name of the table that caused the trigger invocation.

TG_NARGS

Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement.

TG_ARGV[]

Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indices (less than 0 or greater than or equal to tg_nargs) result in a null value.