Topic
3 replies Latest Post - ‏2012-09-12T05:38:58Z by B.Hauser
ZachJ
ZachJ
13 Posts
ACCEPTED ANSWER

Pinned topic SQL Trigger and *LIBL

‏2012-09-10T18:54:46Z |
When using an SQL Trigger, is there something I can do to force the trigger action to search the library list for unqualified tables at trigger run time? In other words to behave like *SYS naming vs *SQL naming.

I have found that I can issue an OVRDBF command from the QCMDEXC procedure to achieve this behavior. I'm not sure this is a good idea but it does behave how I would like it to. Any info would be appreciated.
Updated on 2012-09-12T05:38:58Z at 2012-09-12T05:38:58Z by B.Hauser
  • B.Hauser
    B.Hauser
    223 Posts
    ACCEPTED ANSWER

    Re: SQL Trigger and *LIBL

    ‏2012-09-11T04:36:24Z  in response to ZachJ
    You can't, unqualified database objects in static SQL statements in trigger programs are resolved at compile time and stored in the (trigger) program object.

    The library list set at runtime is ignored when executing static SQL statements.

    The only way to circumvent this behaviour is to use dynamic SQL. Depending on the naming convention to be used for compiling the (trigger) program either the default/current schema (SQL naming) or library list (System Naming) is searched for unqualified data access.

    For additional information please check the following articles:
    SQL versus System Naming Part 1 and Part 2
    http://www.ibm.com/developerworks/ibmi/library/i-system_sql2/index.html
    http://www.ibm.com/developerworks/ibmi/library/i-sqlnaming/index.html

    Birgitta
    • ZachJ
      ZachJ
      13 Posts
      ACCEPTED ANSWER

      Re: SQL Trigger and *LIBL

      ‏2012-09-11T13:55:32Z  in response to B.Hauser
      Than you Brigitta. Initially I was using dynamic SQL. I thought, after reading chapter 10.11 in the trigger redbook, that dynamic sql would search the library for unqualified tabels. The trigger was created with RUNSQLSTM NAMING(*SYS) and it still used the default schema. I knew the CREATE TRIGGER statement created a C object and switched to SQL naming so I assumed I was misinterpreting what I had read and moved on to another solution. So you are saying that I should be able to use dynamic SQL? If that is the case that would be fantastic.

      What about the OVRDBF in the trigger body with static SQL? That is working as desired. Do you have any thoughts on that?

      Zach
      • B.Hauser
        B.Hauser
        223 Posts
        ACCEPTED ANSWER

        Re: SQL Trigger and *LIBL

        ‏2012-09-12T05:38:58Z  in response to ZachJ
        Please check the article SQL versus System-Naming - Part 2:
        For static and dynamic SQL statements in SQL procedures and triggers.

        Do also a reverse engineering and check the SET OPTION statement whether a default schema is set and whether this default schema must be used for dynamic SQL statement.

        If a default or current schema is set, this schema will be searched even though you are working with System Naming.

        Birgitta