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表中拆入对应的数据(经过转化后)
触发器函数:
参考: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.