Topic
  • 1 reply
  • Latest Post - ‏2013-04-16T23:52:34Z by krmilligan
MTharp
MTharp
2 Posts

Pinned topic SQL triggers and iSeries command RNMLIB

‏2013-04-05T20:04:22Z |
I have a table in a given library that has an SQL defined trigger attached. The trigger program resides in the same library as the table it is attached to. If iSeries command RNMLIB is issued for the given library, the trigger remains attached to the table as expected. However, the library qualification of the trigger program is not changed to the newly renamed value. We tried two different methods to remove the trigger: SQL's Drop Trigger and iSeries command RMVPFTRG. While the trigger is dropped from table residing in the newly renamed library, the duplicated trigger program is not deleted. The program existence causes error when the attempts are made to "Create or Replace" the trigger in the renamed library. Outside of manually deleting the trigger program object, are there any thoughts on how to resolve the issue? Thanks in advance for any input.
  • krmilligan
    krmilligan
    450 Posts

    Re: SQL triggers and iSeries command RNMLIB

    ‏2013-04-16T23:52:34Z  

    Figured out that there was a defect preventing the CREATE OR REPLACE TRIGGER option for cleaning up the program left over by the Rename command.

    SQL Standard requires an SQL Trigger to have all object references explicitly qualified at the time that the SQL Trigger is created. Thus, you normally need to recreate the SQL Trigger for it to work properly in a new schema or library.