学习openGauss的第十八天
主要内容是学习openGauss触发器
触发器是对应用动作的响应机制，当应用对一个对象发起DML操作时，就会产生一个触发事件（Event），如果该对象上拥有该事件对应的触发器，那么就会检查触发器的触发条件（Condition）是否满足，如果满足触发条件，那么就会执行触发动作（Action）
连接数据库
su - omm
gsql -r
1.创建源表和触发表，在源表上创建insert触发器，创建操作触发表的触发器函数
create table tab1(col1 int,col2 char(20),col3 char(20));
create table tab2(col1 int,col2 char(20),col3 char(20));
CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
INSERT INTO tab2 VALUES(NEW.col1, NEW.col2, NEW.col3);
RETURN NEW;
END
$$ LANGUAGE PLPGSQL;
create trigger tri_test
before insert on tab1
for each row
execute procedure tri_insert_func();
omm=# create table tab1(col1 int,col2 char(20),col3 char(20));
CREATE TABLE
omm=# create table tab2(col1 int,col2 char(20),col3 char(20));
CREATE TABLE
omm=# CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
omm-# $$
omm$# DECLARE
omm$# BEGIN
omm$# INSERT INTO tab2 VALUES(NEW.col1, NEW.col2, NEW.col3);
omm$# RETURN NEW;
omm$# END
$$ LANGUAGE PLPGSQL;
omm$# CREATE FUNCTION
omm=# omm=#
omm=# create trigger tri_test
omm-# before insert on tab1
omm-# for each row
omm-# execute procedure tri_insert_func();
CREATE TRIGGER
2.在源表上执行insert操作，查看触发操作是否生效；禁用触发器后，再次查看触发操作是否生效
insert into tab1 values(1,'a','b') ;
select * from tab2 ;
alter table tab1 disable trigger tri_test;
insert into tab1 values(2,'c','d') ;
select * from tab2 ;
omm=# insert into tab1 values(1,'a','b') ;
INSERT 0 1
omm=# select * from tab2 ;
col1 | col2 | col3
------+----------------------+----------------------
1 | a | b
(1 row)
omm=# alter table tab1 disable trigger tri_test;
ALTER TABLE
omm=# insert into tab1 values(2,'c','d') ;
INSERT 0 1
omm=# select * from tab2 ;
col1 | col2 | col3
------+----------------------+----------------------
1 | a | b
(1 row)
3.使用系统表PG_TRIGGER和\dS+查看触发器
select * from pg_trigger ;
\dS+ tab1
\dS+
omm=# select * from pg_trigger ;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrab
le | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------+----------+--------+--------+-----------+--------------+---------------+---------------+--------------+-----------
---+----------------+---------+--------+--------+--------+---------
16389 | tri_test | 16395 | 7 | D | f | 0 | 0 | 0 | f
| f | 0 | | \x | | 10
(1 row)
omm=# \dS+ tab1
Table "public.tab1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
col1 | integer | | plain | |
col2 | character(20) | | extended | |
col3 | character(20) | | extended | |
Disabled triggers:
tri_test BEFORE INSERT ON tab1 FOR EACH ROW EXECUTE PROCEDURE tri_insert_func()
Has OIDs: no
Options: orientation=row, compression=no
omm=# \dS+
List of relations
Schema | Name | Type | Owner | Size | Storage | Descriptio
n
------------+-----------------------------------+-------+-------+------------+----------------------------------+-----------
--
pg_catalog | get_global_prepared_xacts | view | omm | 0 bytes | |
pg_catalog | gs_all_control_group_info | view | omm | 0 bytes | |
pg_catalog | gs_asp | table | omm | 0 bytes | |
pg_catalog | gs_auditing | view | omm | 0 bytes | |
pg_catalog | gs_auditing_access | view | omm | 0 bytes | |
pg_catalog | gs_auditing_policy | table | omm | 0 bytes | |
pg_catalog | gs_auditing_policy_access | table | omm | 0 bytes | |
pg_catalog | gs_auditing_policy_filters | table | omm | 0 bytes | |
pg_catalog | gs_auditing_policy_privileges | table | omm | 0 bytes | |
pg_catalog | gs_auditing_privilege | view | omm | 0 bytes | |
pg_catalog | gs_client_global_keys | table | omm | 0 bytes | |
pg_catalog | gs_column_keys | table | omm | 0 bytes | |
pg_catalog | gs_column_keys_args | table | omm | 0 bytes | |
pg_catalog | gs_encrypted_columns | table | omm | 0 bytes | |
pg_catalog | gs_file_stat | view | omm | 0 bytes | |
pg_catalog | gs_client_global_keys_args | table | omm | 0 bytes | |
pg_catalog | gs_cluster_resource_info | view | omm | 0 bytes | |
pg_catalog | gs_get_control_group_info | view | omm | 0 bytes | |
pg_catalog | gs_global_config | table | omm | 40 kB | |
pg_catalog | gs_instance_time | view | omm | 0 bytes | |
pg_catalog | gs_labels | view | omm | 0 bytes | |
pg_catalog | gs_masking | view | omm | 0 bytes | |
pg_catalog | gs_masking_policy | table | omm | 0 bytes | |
pg_catalog | gs_masking_policy_actions | table | omm | 0 bytes | |
pg_catalog | gs_masking_policy_filters | table | omm | 0 bytes | |
pg_catalog | gs_matview | table | omm | 0 bytes | |
pg_catalog | gs_matview_dependency | table | omm | 0 bytes | |
pg_catalog | gs_matviews | view | omm | 0 bytes | |
pg_catalog | gs_opt_model | table | omm | 0 bytes | |
pg_catalog | gs_os_run_info | view | omm | 0 bytes | |
pg_catalog | gs_policy_label | table | omm | 0 bytes | |
pg_catalog | gs_redo_stat | view | omm | 0 bytes | |
pg_catalog | gs_obsscaninfo | table | omm | 0 bytes | |
pg_catalog | gs_session_cpu_statistics | view | omm | 0 bytes | |
pg_catalog | gs_session_memory | view | omm | 0 bytes | |
pg_catalog | gs_session_memory_context | view | omm | 0 bytes | |
pg_catalog | gs_session_memory_detail | view | omm | 0 bytes | |
pg_catalog | gs_session_memory_statistics | view | omm | 0 bytes | |
pg_catalog | gs_session_stat | view | omm | 0 bytes | |
pg_catalog | gs_session_time | view | omm | 0 bytes | |
pg_catalog | gs_shared_memory_detail | view | omm | 0 bytes | |
pg_catalog | gs_sql_count | view | omm | 0 bytes | |
pg_catalog | gs_stat_session_cu | view | omm | 0 bytes | |
pg_catalog | gs_thread_memory_context | view | omm | 0 bytes | |
pg_catalog | gs_total_memory_detail | view | omm | 0 bytes | |
pg_catalog | gs_total_nodegroup_memory_detail | view | omm | 0 bytes | |
pg_catalog | gs_wlm_cgroup_info | view | omm | 0 bytes | |
pg_catalog | gs_wlm_ec_operator_history | view | omm | 0 bytes | |
pg_catalog | gs_wlm_ec_operator_info | table | omm | 8192 bytes | {orientation=row,compression=no} |
pg_catalog | gs_wlm_ec_operator_statistics | view | omm | 0 bytes | |
pg_catalog | gs_wlm_instance_history | table | omm | 8192 bytes | {orientation=row,compression=no} |
pg_catalog | gs_wlm_operator_history | view | omm | 0 bytes | pg_catalog | gs_wlm_rebuild_user_resource_pool | view | omm | 0 bytes | |
pg_catalog | gs_wlm_resource_pool | view | omm | 0 bytes | |
pg_catalog | gs_wlm_session_history | view | omm | 0 bytes | |
|
pg_catalog | gs_wlm_operator_info | table | omm | 8192 bytes | {orientation=row,compression=no} |
pg_catalog | gs_wlm_operator_statistics | view | omm | 0 bytes | |
pg_catalog | gs_wlm_plan_encoding_table | table | omm | 8192 bytes | {orientation=row,compression=no} |
pg_catalog | gs_wlm_plan_operator_history | view | omm | 0 bytes | |
pg_catalog | gs_wlm_plan_operator_info | table | omm | 8192 bytes | {orientation=row,compression=no} |
pg_catalog | gs_wlm_session_info_all | view | omm | 0 bytes | |
pg_catalog | gs_wlm_session_query_info_all | table | omm | 8192 bytes | {orientation=row,compression=no} |
pg_catalog | gs_wlm_session_statistics | view | omm | 0 bytes | |
--More-- pg_catalog | gs_wlm_session_info | view | omm | 0 bytes | |
pg_catalog | gs_wlm_user_info | view | omm | 0 bytes | |
pg_catalog | gs_wlm_user_resource_history | table | omm | 8192 bytes | {orientation=row,compression=no} |
pg_catalog | gs_wlm_workload_records | view | omm | 0 bytes | |
pg_catalog | mpp_tables | view | omm | 0 bytes | |
pg_catalog | pg_attribute | table | omm | 1376 kB | |
pg_catalog | pg_auth_history | table | omm | 40 kB | |
pg_catalog | pg_aggregate | table | omm | 48 kB | |
pg_catalog | pg_am | table | omm | 40 kB | |
pg_catalog | pg_amop | table | omm | 88 kB | |
pg_catalog | pg_amproc | table | omm | 56 kB | |
pg_catalog | pg_app_workloadgroup_mapping | table | omm | 40 kB | |
pg_catalog | pg_attrdef | table | omm | 48 kB | |
pg_catalog | pg_authid | table | omm | 40 kB | |
pg_catalog | pg_available_extension_versions | view | omm | 0 bytes | |
pg_catalog | pg_available_extensions | view | omm | 0 bytes | |
pg_catalog | pg_class | table | omm | 384 kB | |
pg_catalog | pg_collation | table | omm | 40 kB | |
pg_catalog | pg_comm_delay | view | omm | 0 bytes | |
pg_catalog | pg_comm_recv_stream | view | omm | 0 bytes | |
pg_catalog | pg_comm_send_stream | view | omm | 0 bytes | |
pg_catalog | pg_auth_members | table | omm | 0 bytes | |
pg_catalog | pg_comm_status | view | omm | 0 bytes | |
--More-- pg_catalog | pg_cast | table | omm | 56 kB | |
pg_catalog | pg_constraint | table | omm | 48 kB | |
pg_catalog | pg_control_group_config | view | omm | 0 bytes | |
pg_catalog | pg_conversion | table | omm | 56 kB | |
pg_catalog | pg_cursors | view | omm | 0 bytes | |
pg_catalog | pg_database | table | omm | 40 kB | |
pg_catalog | pg_db_role_setting | table | omm | 8192 bytes | |
pg_catalog | pg_default_acl | table | omm | 0 bytes | |
pg_catalog | pg_depend | table | omm | 456 kB | |
pg_catalog | pg_description | table | omm | 176 kB | |
pg_catalog | pg_directory | table | omm | 0 bytes | |
pg_catalog | pg_enum | table | omm | 0 bytes | |
pg_catalog | pg_ext_stats | view | omm | 0 bytes | |
pg_catalog | pg_extension_data_source | table | omm | 0 bytes | |
pg_catalog | pg_foreign_data_wrapper | table | omm | 40 kB | |
pg_catalog | pg_foreign_server | table | omm | 40 kB | |
pg_catalog | pg_get_invalid_backends | view | omm | 0 bytes | |
pg_catalog | pg_get_senders_catchup_time | view | omm | 0 bytes | |
pg_catalog | pg_group | view | omm | 0 bytes | |
pg_catalog | pg_extension | table | omm | 40 kB | |
pg_catalog | pg_gtt_attached_pids | view | omm | 0 bytes | {security_barrier=true} |
pg_catalog | pg_gtt_relstats | view | omm | 0 bytes | {security_barrier=true} |
pg_catalog | pg_gtt_stats | view | omm | 0 bytes | {security_barrier=true} |
--More-- pg_catalog | pg_foreign_table | table | omm | 0 bytes | |
pg_catalog | pg_hashbucket | table | omm | 8192 bytes | |
pg_catalog | pg_index | table | omm | 80 kB | |
pg_catalog | pg_indexes | view | omm | 0 bytes | |
pg_catalog | pg_inherits | table | omm | 0 bytes | |
pg_catalog | pg_job | table | omm | 0 bytes | |
pg_catalog | pg_job_proc | table | omm | 0 bytes | |
pg_catalog | pg_language | table | omm | 40 kB | |
pg_catalog | pg_largeobject | table | omm | 0 bytes | |
pg_catalog | pg_node_env | view | omm | 0 bytes | |
pg_catalog | pg_opclass | table | omm | 56 kB | |
pg_catalog | pg_largeobject_metadata | table | omm | 0 bytes | |
pg_catalog | pg_locks | view | omm | 0 bytes | |
pg_catalog | pg_namespace | table | omm | 40 kB | |
pg_catalog | pg_object | table | omm | 8192 bytes | |
pg_catalog | pg_obsscaninfo | table | omm | 0 bytes | |
pg_catalog | pg_operator | table | omm | 152 kB | |
pg_catalog | pg_opfamily | table | omm | 48 kB | |
pg_catalog | pg_os_threads | view | omm | 0 bytes | |
pg_catalog | pg_partition | table | omm | 8192 bytes | |
pg_catalog | pg_pltemplate | table | omm | 40 kB | |
pg_catalog | pg_prepared_statements | view | omm | 0 bytes | |
pg_catalog | pg_prepared_xacts | view | omm | 0 bytes | |
pg_catalog | pg_proc | table | omm | 896 kB | |
pg_catalog | pg_range | table | omm | 40 kB | |
pg_catalog | pg_replication_slots | view | omm | 0 bytes | |
pg_catalog | pg_resource_pool | table | omm | 40 kB | |
pg_catalog | pg_rewrite | table | omm | 1376 kB | |
pg_catalog | pg_rlspolicies | view | omm | 0 bytes | |
pg_catalog | pg_rlspolicy | table | omm | 8192 bytes | |
pg_catalog | pg_roles | view | omm | 0 bytes | |
pg_catalog | pg_rules | view | omm | 0 bytes | |
pg_catalog | pg_running_xacts | view | omm | 0 bytes | |
pg_catalog | pg_seclabel | table | omm | 8192 bytes | |
pg_catalog | pg_seclabels | view | omm | 0 bytes | |
pg_catalog | pg_session_iostat | view | omm | 0 bytes | |
pg_catalog | pg_session_wlmstat | view | omm | 0 bytes | |
pg_catalog | pg_settings | view | omm | 0 bytes | |
pg_catalog | pg_shadow | view | omm | 0 bytes | |
pg_catalog | pg_shdepend | table | omm | 40 kB | |
pg_catalog | pg_shdescription | table | omm | 48 kB | |
pg_catalog | pg_shseclabel | table | omm | 0 bytes | |
pg_catalog | pg_stat_activity | view | omm | 0 bytes | |
pg_catalog | pg_stat_activity_ng | view | omm | 0 bytes | |
pg_catalog | pg_stat_all_indexes | view | omm | 0 bytes | |
pg_catalog | pg_stat_all_tables | view | omm | 0 bytes | |
pg_catalog | pg_stat_bad_block | view | omm | 0 bytes | |
pg_catalog | pg_stat_bgwriter | view | omm | 0 bytes | |
pg_catalog | pg_stat_database | view | omm | 0 bytes | |
pg_catalog | pg_stat_database_conflicts | view | omm | 0 bytes | |
pg_catalog | pg_stat_replication | view | omm | 0 bytes | |
pg_catalog | pg_stat_sys_indexes | view | omm | 0 bytes | |
pg_catalog | pg_stat_sys_tables | view | omm | 0 bytes | |
pg_catalog | pg_stat_user_functions | view | omm | 0 bytes | |
pg_catalog | pg_stat_user_indexes | view | omm | 0 bytes | |
pg_catalog | pg_stat_user_tables | view | omm | 0 bytes | |
pg_catalog | pg_stat_xact_all_tables | view | omm | 0 bytes | |
pg_catalog | pg_stat_xact_sys_tables | view | omm | 0 bytes | |
pg_catalog | pg_stat_xact_user_functions | view | omm | 0 bytes | |
pg_catalog | pg_statio_all_indexes | view | omm | 0 bytes | |
pg_catalog | pg_statio_all_sequences | view | omm | 0 bytes | |
pg_catalog | pg_stat_xact_user_tables | view | omm | 0 bytes | |
pg_catalog | pg_statio_all_tables | view | omm | 0 bytes | |
pg_catalog | pg_statio_sys_indexes | view | omm | 0 bytes | |
pg_catalog | pg_statio_sys_sequences | view | omm | 0 bytes | |
pg_catalog | pg_statio_sys_tables | view | omm | 0 bytes | |
pg_catalog | pg_statio_user_indexes | view | omm | 0 bytes | |
pg_catalog | pg_statio_user_sequences | view | omm | 0 bytes | |
pg_catalog | pg_statio_user_tables | view | omm | 0 bytes | |
pg_catalog | pg_statistic | table | omm | 256 kB | |
pg_catalog | pg_statistic_ext | table | omm | 8192 bytes | |
pg_catalog | pg_stats | view | omm | 0 bytes | |
pg_catalog | pg_synonym | table | omm | 0 bytes | |
pg_catalog | pg_tables | view | omm | 0 bytes | |
pg_catalog | pg_tablespace | table | omm | 40 kB | |
pg_catalog | pg_tde_info | view | omm | 0 bytes | |
pg_catalog | pg_thread_wait_status | view | omm | 0 bytes | |
pg_catalog | pg_timezone_abbrevs | view | omm | 0 bytes | |
pg_catalog | pg_timezone_names | view | omm | 0 bytes | |
pg_catalog | pg_total_memory_detail | view | omm | 0 bytes | |
pg_catalog | pg_total_user_resource_info | view | omm | 0 bytes | |
pg_catalog | pg_total_user_resource_info_oid | view | omm | 0 bytes | |
pg_catalog | pg_trigger | table | omm | 16 kB | |
pg_catalog | pg_ts_config | table | omm | 40 kB | |
pg_catalog | pg_ts_config_map | table | omm | 48 kB | |
pg_catalog | pg_user | view | omm | 0 bytes | |
pg_catalog | pg_user_mapping | table | omm | 0 bytes | |
pg_catalog | pg_user_mappings | view | omm | 0 bytes | |
pg_catalog | pg_user_status | table | omm | 8192 bytes | |
--More-- pg_catalog | pg_ts_dict | table | omm | 40 kB | |
pg_catalog | pg_ts_parser | table | omm | 40 kB | |
pg_catalog | pg_ts_template | table | omm | 40 kB | |
pg_catalog | pg_type | table | omm | 184 kB | |
pg_catalog | pg_variable_info | view | omm | 0 bytes | |
pg_catalog | pg_views | view | omm | 0 bytes | |
pg_catalog | pg_wlm_statistics | view | omm | 0 bytes | |
pg_catalog | pg_workload_group | table | omm | 40 kB | |
compression=no} |
pg_catalog | streaming_cont_query | table | omm | 0 bytes | |
pg_catalog | streaming_reaper_status | table | omm | 0 bytes | |
pg_catalog | streaming_stream | table | omm | 0 bytes | |
pg_catalog | pgxc_class | table | omm | 8192 bytes | |
pg_catalog | pgxc_group | table | omm | 8192 bytes | |
pg_catalog | pgxc_node | table | omm | 0 bytes | |
pg_catalog | pgxc_prepared_xacts | view | omm | 0 bytes | |
pg_catalog | pgxc_slice | table | omm | 0 bytes | |
pg_catalog | pgxc_thread_wait_status | view | omm | 0 bytes | |
pg_catalog | plan_table | view | omm | 0 bytes | |
pg_catalog | plan_table_data | table | omm | 8192 bytes | {orientation=row,compression=no} |
pg_catalog | statement_history | table | omm | 16 kB | {orientation=row, pg_catalog | sys_dummy | view | omm | 0 bytes | |
public | tab1 | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | tab2 | table | omm | 8192 bytes | {orientation=row,compression=no} |
(221 rows)
4.重命名触发器
alter trigger tri_test on tab1 rename to tri_test1 ;omm=# alter trigger tri_test on tab1 rename to tri_test1 ;
ALTER TRIGGER
5.删除触发器
drop trigger tri_test1 on tab1 ;omm=# drop trigger tri_test1 on tab1 ;
DROP TRIGGER
