How to show stored trigger in sql?

Question

I was working on a trigger, now I want to make some changes and see the code, is it possible to see, if so then how? the trigger is working fine, is there any command in plsql to where I can check out the code? I am using sql command line

score 0 · Answer 1 · answered Apr 25 '19 at 11:31

user_triggers (or all_triggers) have the trigger source code. Similarly user_source (or all_source) have other source code.

General rule though that I follow is to always always always have a file structure where I keep the source. 100% rule that no one in the team is allowed to violate ever. Then I view the act of creating trigger equivalent to "compiling" in traditional programming. In that model, you would have a directory tree for example

<project>/src
<project>/src/plsql
<project>/src/plsql/<folder for each package>/<files>
<project>/src/plsql/<folder for each table>/<triggers>

And then "modifying" is simply changing them here and "compiling" again (compiling will imply running these via sqlplus - or still better creating a shell script.

In this model, you can easily incorporate several available version control tools as well.

score 0 · Answer 2 · answered Apr 25 '19 at 20:57

GUI would display it prettier, but SQL*Plus can do it as well. Here's an example:

Creating a sample trigger:

SQL> create or replace trigger trg_update_percentage
  2    after update or insert on item
  3    for each row
  4  begin
  5    insert into students_percentage (sid, total_per)
  6      select sid, total from student_report
  7      where sid = :new.sid;
  8  end;
  9  /

Trigger created.

Fetch its description from USER_TRIGGERS; as the body is stored into the LONG datatype column, set long should be used (otherwise you won't see the complete code).

SQL> set long 4000
SQL> select trigger_name, trigger_type, triggering_event, table_name, trigger_body
  2  from user_Triggers where trigger_name = upper('trg_update_percentage');

TRIGGER_NAME              TRIGGER_TYPE         TRIGGERING_EVENT     TABLE_NAME
------------------------- -------------------- -------------------- ----------
TRIGGER_BODY
--------------------------------------------------------------------------------
TRG_UPDATE_PERCENTAGE     AFTER EACH ROW       INSERT OR UPDATE     ITEM
begin
  insert into students_percentage (sid, total_per)
    select sid, total from student_report
    where sid = :new.sid;
end;


SQL>

How to show stored trigger in sql?

2 Answers2