RE: CRTDUPOBJ of file with trigger renders trigger inoperative

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Charles, I think you deduced correctly that the problem is with referenced
files.  Here is some text from CREATE TRIGGER that seems to apply directly:

===========================================================================
Operations that invalidate triggers: An invalid trigger is a trigger that is
no longer available to be activated. If a trigger becomes invalid, no
INSERT, UPDATE or DELETE operations will be allowed on the subject table. A
trigger becomes invalid if: 

* The SQL statements in the triggered-action reference the subject table,
the trigger is a self-referencing trigger, and the table is duplicated using
the system CRTDUPOBJ CL command, or 
* If the SQL statements in the triggered-action reference tables or views in
the from library and the objects are not found in the new library when the
table is duplicated using the system CRTDUPOBJ CL command, or 
* If the table is restored to a new library using the system RSTOBJ or
RSTLIB CL commands, and the triggered-action references the subject-table,
the trigger is a self-referencing trigger.

An invalid trigger must first be dropped before it can be recreated by
issuing a CREATE TRIGGER statement. Note that dropping and recreating a
trigger will affect the activation order of a trigger if multiple triggers
for the same triggering operation and activation time are defined for the
subject table.
===========================================================================

And here is the URL I found it at:

http://tinyurl.com/ck3qg

Looks like they recommend dropping and recreating the trigger.  

Hmmmm....  I figured DB2 could handle it, but this shows again, never
assume.

Good luck.

Elvis

-----Original Message-----
Subject: CRTDUPOBJ of file with trigger renders trigger inoperative

I'm trying to use CRTDUPOBJ to create a duplicate of a file into a new
library.

The file has a trigger attached and I actually do want the trigger to be
on the duplicate file.

The problem is that while it seems that DB2 creates a duplicate trigger
in the new library and associates the duplicate trigger with the
duplicate file, the trigger's status is *INOPERATIVE.

>From what I could find online, in the infocenter and via google,
duplicating the file and attached trigger should work.

Why doesn't it?

I couple of things I found lead to be think that perhaps it was because
the trigger references two files that weren't in the new library.
However, the trigger's status is still *INOPERATIVE even after
duplicating those two referenced files prior to duplicating the file
with the trigger.

Anybody know what is going on?

This is on v5r3.

Thanks,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121