How to find the SQL of the Alert created.

How to find the SQL of the Alert created

When an event alert is created on specific database objects,a trigger is created in the database and it can be viewed by querying the below query.

SELECT DBA_TRIGGERS.TABLE_NAME, 

               DBA_TRIGGERS.TRIGGER_NAME, 

               DBA_TRIGGERS.STATUS, USER_OBJECTS.STATUS, 

               DBA_TRIGGERS.TRIGGER_BODY 

FROM    USER_OBJECTS, 

               DBA_TRIGGERS

WHERE  DBA_TRIGGERS.TRIGGER_NAME = USER_OBJECTS.OBJECT_NAME 

AND       USER_OBJECTS.OBJECT_TYPE = 'TRIGGER' 

AND       USER_OBJECTS.OBJECT_NAME LIKE 'ALR_%'

Example :

1) If an alert is created on the table ALR_DISTRIBUTION_LISTS then a trigger with the name ALR_ALR_DISTRIBUTION_LISTS_IAR will be created in the database.

2) If an alert is created on the table PO_ACTION_HISTORY then a trigger with the name ALR_PO_ACTION_HISTORY_UAR will be created in the database.

If you observe the above two examples clearly,you can see that the triggers are created with the table names on which alerts are created on,with ALR,IAR and UAR appended to the table names.

Where,

ALR specifices that it is an trigger created for ALERTS.

IAR and UAR specifices that the trigger is created for INSERT and UPDATE operations respectively on the database table.         

Challa.