I was looking for a SQL query which can display the Trigger description, like while creating a trigger what condition was used especially which key word used like new or old. The below SQL query helped me to know such details.

How to see created trigger description and SQl stament , to confirm new or old is used ?

Launch the SQL prompt and connect to you database as sysdba.

Then run the below commands to know about the trigger you created ...

SQL> drop trigger TSIN_TR;

Trigger dropped.

SQL> CREATE OR REPLACE TRIGGER TSIN_TR
  2  AFTER INSERT ON TARGET_SERVER
  3  FOR EACH ROW
  4  BEGIN
  5  INSERT INTO ADJUST_DISTRIBUTION (PACKAGE_ID) SELECT PACKAGE_ID FROM TARGETLIST_MAP T
  6  WHERE T.TARGETLIST_ID = :NEW.TARGETLIST_ID;
  7  END;
  8  /

Trigger created.

SQL> set linesize 200
SQL> set pagesize 500
SQL> desc user_source;
 Name                                                                                                              Null?    Type
 ---------------------------------------------------------------------------------------------------
 NAME                                                                                                                       VARCHAR2(30)
 TYPE                                                                                                                       VARCHAR2(12)
 LINE                                                                                                                       NUMBER
 TEXT                                                                                                                       VARCHAR2(4000)

SQL> select distinct type from user_source;

TYPE
------------
TRIGGER

SQL> select text from user_source where name='TSIN_TR' order by line asc;

TEXT
----------------------------------------------------------------------------------------------------
TRIGGER TSIN_TR
AFTER INSERT ON TARGET_SERVER
FOR EACH ROW
BEGIN
INSERT INTO ADJUST_DISTRIBUTION (PACKAGE_ID) SELECT PACKAGE_ID FROM TARGETLIST_MAP T
WHERE T.TARGETLIST_ID = :NEW.TARGETLIST_ID;
END;

7 rows selected.

SQL> select DESCRIPTION, TRIGGER_BODY from user_triggers where trigger_name = 'TSIN_TR';

DESCRIPTION
----------------------------------------------------------------------------------------------------
TRIGGER_BODY
--------------------------------------------------------------------------------
TSIN_TR
AFTER INSERT ON TARGET_SERVER
FOR EACH ROW
BEGIN
INSERT INTO ADJUST_DISTRIBUTION (PACKAGE_ID) SELECT PACKAGE_ID FROM TARGET

SQL >

Hope this helps ...