Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    25

    Lightbulb Unanswered: [Triggers] Where are triggers stored?

    Hi folks,

    I need to lookup my trigger source. Which view or table is used by Oracle to store triggers in ?

    Thx.

    Don.

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    user_triggers

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    The Oracle Database stores information on all system structures that can be accessed using either the all_objects view or sys.dba_objects.


    Select object_name, object_type from that table with a where clause matching the search criteria.

    For example, in this case, you could write the following query:

    HTML Code:
     SQL> select distinct object_name from all_objects 
      2  where object_name like '%TRIGGER%';
    OBJECT_NAME
    ------------------------------
    ALL_INTERNAL_TRIGGERS
    ALL_TRIGGERS
    ALL_TRIGGER_COLS
    DBA_INTERNAL_TRIGGERS
    DBA_TRIGGERS
    DBA_TRIGGER_COLS
    LOADER_TRIGGER_INFO
    TABLE_UPDATE_TRIGGER
    USER_INTERNAL_TRIGGERS
    USER_TRIGGERS
    USER_TRIGGER_COLS
    11 rows selected.
    SQL>
    This way, you can find all the system tables you are interested in.

    Ravi

  4. #4
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    25

    Question

    Is there a maximum size for ALL_TRIGGERS.TRIGGER_BODY ?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes - it is constrained by the maximum length of the LONG datatype. This changes from major release to major release; for 9i it is 2 gigabtytes.

  6. #6
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    25
    So if I create a trigger with 73,000 characters and it is reduced to 8,000 characters it isn't an Oracle problem, but a problem of Centura Team Developer? (programming language with database access)

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes I think so.

    But 73,000 characters is too big for a trigger really. Trigger code should be kept as short as possible, with most of the logic performed in a packaged procedure called from the trigger. That will be more efficient, since SQL in triggers gets parsed each time invoked, whereas in packages it is cached. See this link for details.

  8. #8
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    Triggers are stored in the data dictionary, within the SYSTEM tablespace. Their names and some properties are visible through views like dba_triggers (and all_triggers and user_triggers) and additionally through views dba_objects (and all_objects and user_objects) where object_type = 'TRIGGER'.

    As for their size, I had one trigger that was over 2,000 lines. At an average of 50 chars per line that's 100 KB. According to the Oracle Reference guide, the limit is imposed by the tool executing the stored procedure or trigger, and can be between 2000 and 3000 lines. It does not say how many characters or bytes this equates to, although non-stored SQL statements are limited to 64K characters.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •