개요

SM 소스를 수정할 때 트리거를 이용해 기존 PSM 소스를 자동으로 백업하는 방법입니다.
 

방법

PSM 소스 수정시 trigger 이용하여 원본 백업해 놓는 스크립트 샘플입니다.

해당 트리거 생성시 PSM 소스를 자동으로 백업하여 PSM 작성자의 편의성을 제공해주는 트리거입니다.

아래 절차를 통해 수정시 PSM 소스를 백업하는 걸 확인가능합니다.

create or replace trigger ddl_trigger_before
before create or alter on database
declare
curr_owner varchar(1024);
curr_name varchar(1024);
curr_type varchar(1024);
curr_login_user varchar(1024);

begin
curr_type := TB_DICT_OBJ_TYPE;
curr_owner := TB_DICT_OBJ_OWNER;
curr_name := TB_DICT_OBJ_NAME;
insert into tibero.ddl_trigger_log
select sysdate as curr_date, owner as curr_owner, name as curr_name, type as curr_type, text as
curr_source
from dba_source
where 1=1
and owner=curr_owner
and name=curr_name
and type=curr_type;
end;
/

트리거 사용예시

1. 파라미터 변경

   alter system set _DDL_TRIGGER_ENABLE=y;

    

2. log table 생성

   create table ddl_trigger_log(
   curr_date date , 
   curr_owner varchar (100),
   curr_name varchar (100), 
   curr_type varchar(100), 
   curr_source blong
   );

    

3. 샘플 procedure 생성

   create or replace procedure my_proc is
   begin
   dbms_output.put_line ('aa');
   end my_proc;
   /

    

4. trigger 생성

   create or replace trigger ddl_trigger_before
   before create or alter on database
   declare
   curr_owner varchar(1024);
   curr_name varchar(1024);
   curr_type varchar(1024);
   curr_login_user varchar(1024);
   
   begin
   curr_type := TB_DICT_OBJ_TYPE;
   curr_owner := TB_DICT_OBJ_OWNER;
   curr_name := TB_DICT_OBJ_NAME;
   -- curr_login_user :=TB_LOGIN_USER;
   insert into tibero.ddl_trigger_log
   select sysdate as curr_date, owner as curr_owner, name as curr_name, type as curr_type, text as
   curr_source
   from dba_source
   where 1=1
   and owner=curr_owner
   and name=curr_name
   and type=curr_type;
   end;
   /

    

5. 샘플 procedure 수정 

   
   create or replace procedure my_proc is
   begin
   dbms_output.put_line ('bbb');
   end my_proc;
   /

    

6. 백업 조회

   
   select * from tibero.ddl_trigger_log;

    

7. 조회 결과 예시

   set long 99999
   set linesize 150
   col CURR_DATE for a15
   col CURR_OWNER for a9
   col CURR_NAME for a9
   col CURR_TYPE for a10
   col CURR_SOURCE for a30
   
   select * from tibero.ddl_trigger_log;
   
   CURR_DATE       CURR_OWNE CURR_NAME CURR_TYPE  CURR_SOURCE
   --------------- --------- --------- ---------- ------------------------------
   2026/04/14      SYS       MY_PROC   PROCEDURE  create or replace procedure my
                                                  _proc is
   
   2026/04/14      SYS       MY_PROC   PROCEDURE  begin
   2026/04/14      SYS       MY_PROC   PROCEDURE  dbms_output.put_line ('aa');
   2026/04/14      SYS       MY_PROC   PROCEDURE  end my_proc;