postgresql触发器记录表记录的更改

moluowangzi

1. SELECT * INTO card_record FROM card where false;
   
2. alter table card_record add time numeric;
   
3. alter table card_record add flag character(1);
   
4. 
   
5. CREATE OR REPLACE FUNCTION record_card()
   
6.   RETURNS trigger AS
   
7. $BODY$
   
8. begin
   
9.     if(TG_OP='INSERT')then
   
10.         insert into card_record select NEW.*,EXTRACT(EPOCH from current_timestamp(0)),'I';
    
11.     elseif(TG_OP='DELETE')then
    
12.         insert into card_record select OLD.*,EXTRACT(EPOCH from current_timestamp(0)),'D';
    
13.     elseif(TG_OP='UPDATE')then
    
14.         insert into card_record select OLD.*,EXTRACT(EPOCH from current_timestamp(0)),'U';
    
15.     end if;
    
16. return null;
    
17. end;
    
18. $BODY$
    
19.   LANGUAGE 'plpgsql';
    
20. 
    
21. CREATE TRIGGER trig_record_card
    
22.   AFTER INSERT OR UPDATE OR DELETE
    
23.   ON card
    
24.   FOR EACH ROW
    
25.   EXECUTE PROCEDURE record_card();