openGauss每日一练第18天-触发器

omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# \d
No relations found.
omm=# create table t1(a int,b varchar(100));
CREATE TABLE
omm=# create table t2(a int,b varchar(100));
omm=# CREATE TABLE
CREATE OR REPLACE FUNCTION t1_insert_func() RETURNS TRIGGER AS
omm-# $$
omm$# BEGIN
omm$# DECLARE
omm$# INSERT INTO t2 VALUES(NEW.a, NEW.b);
END
omm$# omm$# RETURN NEW;
omm$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
omm=# CREATE TRIGGER insert_trigger
omm-# omm-# BEFORE INSERT ON t1
omm-# FOR EACH ROW
EXECUTE PROCEDURE t1_insert_func();
CREATE TRIGGER
omm=#
omm=# select * from t1;
a | b
---+---
(0 rows)

omm=# select * from t2;
a | b
---+---
(0 rows)

omm=# insert into t1 values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'g'),(8,'h');
INSERT 0 8
omm=# select * from t1;
a | b
---+---
1 | a
2 | b
3 | c
4 | d
5 | e
6 | f
7 | g
8 | h
(8 rows)

select * from t2;omm=#
3 | c
4 | d
5 | e
6 | f
7 | g
8 | h
(8 rows)

omm=# a | b
---+---
1 | a
2 | b

omm=# alter table t1 disable trigger insert_trigger;
ALTER TABLE
omm=# insert into t1 values(9,'i'),(10,'j');
INSERT 0 2
omm=# select * from t1;
a | b
----+---
1 | a
2 | b
3 | c
4 | d
5 | e
6 | f
7 | g
select * from t2; 8 | h
9 | i
10 | j
(10 rows)

omm=#
a | b
---+---
1 | a
2 | b
3 | c
4 | d
5 | e
6 | f
7 | g
8 | h
(8 rows)

omm=# select * from PG_TRIGGER;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdefer
rable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------+----------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------
------+----------------+---------+--------+--------+--------+---------
16425 | insert_trigger | 16431 | 7 | D | f | 0 | 0 | 0 | f
| f | 0 | | \x | | 10
(1 row)

omm=# alter trigger insert_trigger on t1 rename to insert_trigger_new;
ALTER TRIGGER
omm=# select * from PG_TRIGGER;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgd
eferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------+--------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+----
----------+----------------+---------+--------+--------+--------+---------
16425 | insert_trigger_new | 16431 | 7 | D | f | 0 | 0 | 0 | f
| f | 0 | | \x | | 10
(1 row)

omm=# drop trigger insert_trigger on t1;
ERROR: trigger "insert_trigger" for table "t1" does not exist
omm=# drop trigger insert_trigger_new on t1;
DROP TRIGGER
omm=# drop function t1_insert_func;
DROP FUNCTION
omm=# drop table t1;
DROP TABLE
omm=# drop table t2;
DROP TABLE
omm=#