Overview

This is a method to automatically back up the existing PSM source using a trigger when modifying SM source.
 

Method

This is a sample script that uses a trigger to back up the original source when modifying PSM source.

The trigger created here automatically backs up the PSM source to provide convenience to the PSM author.

You can verify the backup of the PSM source during modification through the following procedure.

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;
/

Trigger Usage Example

1. Change Parameter

   alter system set _DDL_TRIGGER_ENABLE=y;

    

2. Create 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. Create Sample Procedure

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

    

4. Create 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. Modify Sample Procedure 

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

    

6. View Backup

   
   select * from tibero.ddl_trigger_log;

    

7. Example of Query Result

   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;