学习心得

触发器是对应用动作的响应机制，当应用对一个对象发起DML操作时，就会产生一个触发事件（Event），如果该对象上拥有该事件对应的触发器，那么就会检查触发器的触发条件（Condition）是否满足，如果满足触发条件，那么就会执行触发动作（Action）
CREATE TRIGGER
FOR EACH ROW EXECUTE PROCEDURE
ALTER TABLE DISABLE TRIGGER
dS+ <tablename>

0.进入系统

su - omm
gsql -r
复制

1.创建源表和触发表，在源表上创建insert触发器，创建操作触发表的触发器函数

CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
RETURN NEW;
END
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER insert_trigger
BEFORE INSERT ON test_trigger_src_tbl
FOR EACH ROW
EXECUTE PROCEDURE tri_insert_func();
复制

• 回显

CREATE TRIGGER
复制

2.在源表上执行insert操作，查看触发操作是否生效；禁用触发器后，再次查看触发操作是否生效

执行INSERT后查看

INSERT INTO test_trigger_src_tbl VALUES(1,1,1);
SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;
复制

• 回显

 id1 | id2 | id3
-----+-----+-----
   1 |   1 |   1
(1 row)

 id1 | id2 | id3
-----+-----+-----
   1 |   1 |   1
(1 row)
复制

禁用触发器后再执行INSERT, 查看

ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;
INSERT INTO test_trigger_src_tbl VALUES(10,10,10);
SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;
复制

• 回显

 id1 | id2 | id3
-----+-----+-----
   1 |   1 |   1
  10 |  10 |  10
(2 rows)

 id1 | id2 | id3
-----+-----+-----
   1 |   1 |   1
(1 row)
复制

新插入数据没有反应在test_trigger_des_tbl中.

3.使用系统表PG_TRIGGER和\dS+查看触发器

PG_TRIGGER

select * from PG_TRIGGER;
复制

• 回显

 tgrelid |     tgname     | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdefe
rrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------+----------------+--------+--------+-----------+--------------+---------------+---------------+--------------+-------
-------+----------------+---------+--------+--------+--------+---------
   16401 | insert_trigger |  16400 |      7 | D         | f            |             0 |             0 |            0 | f
       | f              |       0 |        | \x     |        |      10
(1 row)
复制

dS+

\dS+ test_trigger_src_tbl;
复制

• 回显

                Table "public.test_trigger_src_tbl"
 Column |  Type   | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
 id1    | integer |           | plain   |              |
 id2    | integer |           | plain   |              |
 id3    | integer |           | plain   |              |
Disabled triggers:
    insert_trigger BEFORE INSERT ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_insert_func()
Has OIDs: no
Options: orientation=row, compression=no
复制

4.重命名触发器

ALTER TRIGGER insert_trigger ON test_trigger_src_tbl RENAME TO insert_trigger_renamed;
复制

• 回显

ALTER TRIGGER
复制

5.删除触发器

DROP TRIGGER insert_trigger_renamed ON test_trigger_src_tbl;
select * from PG_TRIGGER;
复制

• 回显

DROP TRIGGER
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable |
 tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------+--------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+
----------------+---------+--------+--------+--------+---------
(0 rows)
复制