课程作业
1.创建源表和触发表，在源表上创建insert触发器，创建操作触发表的触发器函数
omm=# create table t1 (id int,name varchar(20));
CREATE TABLE
omm=# create table t2 (id int,name varchar(20));
CREATE TABLE
omm=# CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
omm-# $$
omm$# DECLARE
omm$# BEGIN
omm$# INSERT INTO t2 VALUES(NEW.id, NEW.name);
omm$# RETURN NEW;
omm$# END
omm$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
omm=# CREATE TRIGGER insert_trigger
omm-# BEFORE INSERT ON t1
omm-# FOR EACH ROW
omm-# EXECUTE PROCEDURE tri_insert_func();
CREATE TRIGGER
2.在源表上执行insert操作，查看触发操作是否生效；禁用触发器后，再次查看触发操作是否生效
omm=# insert into t1 values(1,'lili');
INSERT 0 1
omm=# select * from t1;
id | name
----+------
1 | lili
(1 row)
omm=# select * from t2;
id | name
----+------
1 | lili
(1 row)
omm=# alter table t1 disable trigger all;
ALTER TABLE
omm=#
omm=# insert into t1 values(2,'popo');
INSERT 0 1
omm=# select * from t1;
id | name
----+------
1 | lili
2 | popo
(2 rows)
omm=# select * from t2;
id | name
----+------
1 | lili
(1 row)
3.使用系统表PG_TRIGGER和\dS+查看触发器
omm=# select * from pg_trigger;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdefe
rrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------+----------------+--------+--------+-----------+--------------+---------------+---------------+--------------+-------
-------+----------------+---------+--------+--------+--------+---------
16401 | insert_trigger | 16395 | 7 | D | f | 0 | 0 | 0 | f
| f | 0 | | \x | | 10
(1 row)
omm=# \dS+ t1
Table "public.t1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character varying(20) | | extended | |
Disabled triggers:
insert_trigger BEFORE INSERT ON t1 FOR EACH ROW EXECUTE PROCEDURE tri_insert_func()
Has OIDs: no
Options: orientation=row, compression=no
4.重命名触发器
omm=# alter trigger insert_trigger on t1 rename to insert_trigger2;
ALTER TRIGGER
5.删除触发器
omm=# drop trigger insert_trigger2 on t1;
DROP TRIGGER
