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

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 ...


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

Post a Comment

Who ever writes Inappropriate/Vulgar comments to context, generally want to be anonymous …So I hope U r not the one like that?
For lazy logs u can at least use Name/URL option which don’t even require any sign-in, good thing is that it can accept your lovely nick name also and URL is not mandatory too.
Thanks for your patience
~Krishna(I love "Transparency")
వీలయితే నాల్గు పోస్టులు ...కుదురితే ఒక కామెంటూ ...

Translate

Enter your email address:

Buffs ...

Visitors


hits counter
View My StatsCheck Google Page Rank

Add to Google Reader or Homepage

Bookmark and Share

Tags


Powered by WidgetsForFree