psql-08表:触发器

时间：2021-02-18 00:04:56

语句级触发器与行为触发器

//创建一个对student表的操作记录表

create table log(

  update_time timestamp,      //操作时间

  db_user varchar(40),          //操作的数据库用户名

  opr_type varchar(6)           //操作类型

);

//创建触发器函数

create function log_trigger()

returns trigger as

$$

begin

  insert into log values(now(), user, tg_op);           //user即为当前用户名; to_op是触发器函数中的特殊变量，代表DML操作类型

  return null;

end;

$$

language 'plpgsql';
  • 语句级触发器:执行每个SQL时,只执行一次;
//创建一个语句级触发器

create trigger log_trigger

  after insert or delete or update on student

  for statement execute procedure log_trigger();   //statement

之后一个SQL语句无论是插入多条数据，删除多条数据或者更新删除不存在的数据;只要执行成功一个SQL语句，就会触发一次;
  • 行级触发器: 以SQL语句操作后作用到的n行而触发n次;
create trigger log_trigger2

  after insert or delete or update on student

  for row execute procedure log_trigger();

before触发器和after触发器

  • 语句级别的before触发器在语句开始做任何事情之前就触发;
  • 语句级别的after触发器在语句结束时触发;
  • 行级别的before触发器在特定进行操作之前触发;
  • 行级别的after触发器在语句结束时触发，但会在任何语句级别的after触发器触发之前触发;
//before触发器可以直接修改new值以改变实际的更新值

create function student_new_name_trigger()

returns trigger as

'     //$$

begin

  new.student_name = new.student_name || new.student_no;

  return new;

end;

'    //$$

language 'plpgsql';

//

create trigger new_name_trigger

  before insert or update on student

  for each row execute procedure student_new_name_trigger();

//保存的数据student_name会变成要输入的值拼接上student_no

触发器的行为

  • 触发器函数都有返回值;
  • 语句级别的触发器总是返回null,即写为return null;如果没有写会导致出错;
  • 行级before和instead of这类行级触发器:

    * 如果返回null,则忽略对当前行的操作;

    * 如果返回非null的行,对insert,update操作来说，返回的行为将成为插入或更新的行;
  • 行级after触发器，其返回值会被忽略;
  • 如果同一事件上有多个触发器,按触发器的名字顺序来触发;
  • 对于行级before和instead of这类行级触发器:

    * 每个触发器返回的行将成为下一个触发器的输入;

    * 返回内容为空的话，该行上其他行触发器也不会被触发;

事件触发器

  • 用于弥补PostgreSQL以前版本不支持DDL触发器功能;目前，其支持三种DDL事件;

    * ddl_command_start: 一个DDL开始执行前被触发;

    * ddl_command_end: 一个DDL执行完之后被触发;

    * sql_drop: 删除一个数据库对象前被触发;
  • 由于事件触发器权限较大,所以只有超级用户才能创建修改事件触发器;

创建事件触发器

  • 事件触发器函数的返回类型是event_trigger与普通触发器函数返回类型(trigger)不同;
//一个禁止所有DDL语句的例子

create or replace function abort_any_command()

  returns event_trigger

language plpgsql

as

$$

begin

  raise exception 'command % is disabled', tg_tag;

end;

$$;

//

create event trigger abort_ddl on ddl_command_start

execute procedure abort_any_command();

//在postgresq中truncate事件是使用普通触发器触发的,事件触发器不会触发truncate table;

//禁止上面定义的事件触发器

alter event trigger abort_ddl disable;
  • 可以从表pg_evnet_trigger中查看已经定义的触发器;

变量

  • 事件触发器函数仅仅支持以下变量
  • tg_event:为ddl_command_start,ddl_command_end,sql_drop之一;
  • tg_tag: 具体的哪种DDL操作,如:create table, drop table等;

修改事件触发器

alter event trigger name [disable | enable];

alter event trigger name enable [replica | always];

alter event trigger name owner to new_owner;

alter event trigger name rename to new_name;

事件触发器记录数据库中对象删除的审计日志

//记录信息表

create table log_drop_objects (

  op_time timestamp,

  ddl_tag text,

  classid oid,

  objid oid,

  objectsubid oid,

  object_type text,

  schme_name text,

  object_name text,

  object_identity text

);

//

create function event_trigger_log_drops()

  returns event_trigger

  language plpgsql as

$$

declare

   obj record;

begin

  insert into log_drop_objects select now(), tg_tag, classid, objid, objsubid, object_type,

      schema_name, object_name, object_identity from pg_event_trigger_dropped_objects();

end

$$;

//

create event trigger event_trigger_log_drops

  on sql_drop

  execute procedure event_trigger_log_drops();

//创建一张测试表

create table test (id int primary key, note varchar(20));

//修改字段或删除表再查看记录表中的记录

alter table test drop column note;

表空间

  • 表空间实际上是为表指定一个存储的目录;
  • 创建数据库时可以为数据库指定默认的表空间；创建表和索引时可以指定表空间;

使用

  • 创建:
create tablespace tablespace_name [owner user_name] location 'directory';

//

create tablespace tbs_data location '/data/pgdata';
  • 创建数据库时指定
create database db01 set tablespace tbs_data;
  • 改变数据库默认空间
//在此操作时保证没有其他人连接此数据库,否则会报错;

alter database db01 set tablespace tbs_data;

//数据中已经有的表的表空间不会改变;
  • 建表的时候指定表空间
create table test01 (id int, note text) tablespace tbs_data;
  • 创建索引时指定表空间
create index idx_test01_id on test01(id) tablespace tbs_data;
  • 建唯一约束时可以指定约束索引的表空间
alter table test01 add constraint unique_test01_id unique(id) using index tablespace tbs_data;
  • 增加主键时也可以指定主键索引的表空间
alter table test01 add constraint pk_test01_id primary key(id) using index tablespace tbs_data;
  • 把表从一个空间移动到另一个表空间
//注意在移动表时候会锁表，此时所有对表的操作都会被阻塞;

alter table test01 set tablespace pg_default;

相关文章