Oracle 业务表添加时间戳触发器

发表于 更新于 分类于 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*Oracle 业务表添加时间戳触发器*/  
CREATE OR REPLACE PROCEDURE "P_ADD_KETTLE" (  
  table_name VARCHAR2) AS  
BEGIN  
    DBMS_OUTPUT.PUT_LINE(table_name);  
    --添加时间戳列  
    EXECUTE IMMEDIATE 'Alter Table "'|| table_name || '" add KETTLE_LASTMODIFY TIMESTAMP (6) DEFAULT current_timestamp';  
    COMMIT;  
    --添加触发器  
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER KL_'|| SUBSTR('KL_' || table_name,4,27) || '  
    BEFORE Insert or Update  ON "'|| table_name || '"  
    FOR EACH ROW  
    DECLARE  
      -- local variables here  
    BEGIN  
      :NEW.KETTLE_LASTMODIFY := current_timestamp;  
    END;';  
    COMMIT;  
  DBMS_OUTPUT.PUT_LINE('PROCESS OK...');  
  
END p_add_kettle;