Question

pgsql触发器:当向一张表中插入或更新一条记录时，同时向另一张表也插入或更新一条记录

两张表的表结构是一样的（字段数和字段类型都一样），但是有的字段名不一样。如表a(aid,aname,time)，表b(id,name,time)，现在给a表插入一条数据的同时给b表也插入一条相同的记录。如给a表插入(0001,'张三','2013-01-23')则b表也插入(0001,'张三','2013-01-23');update时一样，我用的数据库是PostgreSQL9.2，求高手赐教，谢谢。

Answer 1

digoal=# create table a (aid int primary key, aname text, time timestamp);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE
digoal=# create table b (id int primary key, name text, time timestamp);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for table "b"
CREATE TABLE
digoal=# create or replace function tg_a () returns trigger as $$
declare
begin
case TG_OP
when 'INSERT' then
insert into b(id,name,time) values (NEW.aid, NEW.aname, NEW.time);
when 'UPDATE' then
update b set id=NEW.aid, name=NEW.aname, time=NEW.time where id=OLD.aid;
when 'DELETE' then
delete from b where id=OLD.aid;
when 'TRUNCATE' then
truncate b;
else
return NULL;
end case;
return NULL;
end;
$$ language plpgsql;
digoal=# create trigger tg_a after INSERT OR DELETE OR UPDATE ON a for each row execute procedure tg_a();
CREATE TRIGGER
digoal=# create trigger tg_a_truncate after truncate ON a for each statement execute procedure tg_a();
CREATE TRIGGER
digoal=# insert into a select generate_series(1,10),'digoal',clock_timestamp();
INSERT 0 10
digoal=# select * from a;
aid | aname | time
-----+--------+----------------------------
1 | digoal | 2013-02-03 18:59:37.592479
2 | digoal | 2013-02-03 18:59:37.592667
3 | digoal | 2013-02-03 18:59:37.592674
4 | digoal | 2013-02-03 18:59:37.592677
5 | digoal | 2013-02-03 18:59:37.59268
6 | digoal | 2013-02-03 18:59:37.592683
7 | digoal | 2013-02-03 18:59:37.592686
8 | digoal | 2013-02-03 18:59:37.59269
9 | digoal | 2013-02-03 18:59:37.592693
10 | digoal | 2013-02-03 18:59:37.592696
(10 rows)
digoal=# select * from b;
id | name | time
----+--------+----------------------------
1 | digoal | 2013-02-03 18:59:37.592479
2 | digoal | 2013-02-03 18:59:37.592667
3 | digoal | 2013-02-03 18:59:37.592674
4 | digoal | 2013-02-03 18:59:37.592677
5 | digoal | 2013-02-03 18:59:37.59268
6 | digoal | 2013-02-03 18:59:37.592683
7 | digoal | 2013-02-03 18:59:37.592686
8 | digoal | 2013-02-03 18:59:37.59269
9 | digoal | 2013-02-03 18:59:37.592693
10 | digoal | 2013-02-03 18:59:37.592696
(10 rows)
digoal=# delete from a where aid=1;
DELETE 1
digoal=# select * from b;
id | name | time
----+--------+----------------------------
2 | digoal | 2013-02-03 18:59:37.592667
3 | digoal | 2013-02-03 18:59:37.592674
4 | digoal | 2013-02-03 18:59:37.592677
5 | digoal | 2013-02-03 18:59:37.59268
6 | digoal | 2013-02-03 18:59:37.592683
7 | digoal | 2013-02-03 18:59:37.592686
8 | digoal | 2013-02-03 18:59:37.59269
9 | digoal | 2013-02-03 18:59:37.592693
10 | digoal | 2013-02-03 18:59:37.592696
(9 rows)
digoal=# select * from a;
aid | aname | time
-----+--------+----------------------------
2 | digoal | 2013-02-03 18:59:37.592667
3 | digoal | 2013-02-03 18:59:37.592674
4 | digoal | 2013-02-03 18:59:37.592677
5 | digoal | 2013-02-03 18:59:37.59268
6 | digoal | 2013-02-03 18:59:37.592683
7 | digoal | 2013-02-03 18:59:37.592686
8 | digoal | 2013-02-03 18:59:37.59269
9 | digoal | 2013-02-03 18:59:37.592693
10 | digoal | 2013-02-03 18:59:37.592696
(9 rows)

digoal=# update a set aname='new' where aid=2;
UPDATE 1
digoal=# select * from a where aid=2;
aid | aname | time
-----+-------+----------------------------
2 | new | 2013-02-03 18:59:37.592667
(1 row)
digoal=# select * from b where id=2;
id | name | time
----+------+----------------------------
2 | new | 2013-02-03 18:59:37.592667
(1 row)
digoal=# truncate a;
TRUNCATE TABLE
digoal=# select * from a;
aid | aname | time
-----+-------+------
(0 rows)
digoal=# select * from b;
id | name | time
----+------+------
(0 rows)