Link to home
Start Free TrialLog in
Avatar of jalvarezca
jalvarezca

asked on

How to avoid cascading in an insert DB2 Trigger

I need to avoid subsecuent Insert in an Insert trigger to the same table.

I have a table with the PK : OrigenID and DestinoID.

Every time a row is inserted I need to create another row with the Primary Key cwitched.

This trigger works, but sends an error message to my program.

CREATE TRIGGER VYKDTBN.TR_INSERTARDISTANCIADESTINO 
	AFTER INSERT ON VYKDTBN.TDISTANCIAS 
	REFERENCING NEW AS N 
	FOR EACH ROW 
	MODE DB2ROW 
	BEGIN ATOMIC 
 
	INSERT INTO VYKDTBN . TDISTANCIAS ( ORIGENID , DESTINOID , KMS , USUACREA , FECHACREA , USUAMODI , FECHAMODI ) 
	VALUES ( N . DESTINOID , N . ORIGENID , N . KMS , N . USUACREA , N . FECHACREA , N . USUAMODI , N . FECHAMODI ) ; 
 
	END  ;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Gary Patterson, CISSP
Gary Patterson, CISSP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi jalvarezca,

I believe that the easiest way to solve this will be with the trigger itself.  One of the qualifiers on the CREATE TRIGGER statement is 'NO CASCADE'.  That should prevent the firing of other triggers, including the recursive call of this trigger that seems to be causing the problem.  Here's the IBM documentation on CREATE TRIGGER.  Note that the document is from the LUW pages, but the statement should be the same.

  http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0000931.htm

You'll wind up with a statement like the one below.


Good Luck,
Kent

CREATE TRIGGER VYKDTBN.TR_INSERTARDISTANCIADESTINO
         NO CASCADE
	AFTER INSERT ON VYKDTBN.TDISTANCIAS 
	REFERENCING NEW AS N 
	FOR EACH ROW 
	MODE DB2ROW 
	BEGIN ATOMIC 
 
	INSERT INTO VYKDTBN . TDISTANCIAS ( ORIGENID , DESTINOID , KMS , USUACREA , FECHACREA , USUAMODI , FECHAMODI ) 
	VALUES ( N . DESTINOID , N . ORIGENID , N . KMS , N . USUACREA , N . FECHACREA , N . USUAMODI , N . FECHAMODI ) ; 
 
	END  ;

Open in new window


Two more things:

  The 'NO CASCADE' clause will prevent all other triggers from firing due to changes made by this trigger.  If you need the other triggers to fire, you'll need a different solution.

  It's a very odd database design.  I propose that the design DBA needs to readdress how the indexes (keys) are structured.


Kent
NO CASCADE will do the trick if this is the only trigger on this table, but will create a problem if there are other current or future triggers that need to fire.  

I think I prefer the handler approach in this particular case.  

One caveat:  Since this approach causes an exception, there may be a performance impact in situations where there are a large number of rows inserted on a heavily-loaded system.  NO CASCADE would perform somewhat better.

- Gary Patterson
Hi Gary,

Better yet would be a proper database design, but that may be out of his control.  :(

I've got an issue with the premise of replicating the row, but swapping the two data points that make up the composite key.  Better would be to put the two items in their own table along with a "link" column, and add an IDENTITY column to the original table.  When the two data points are stored (in either order) link the row back to the original table.


Kent
Avatar of jalvarezca
jalvarezca

ASKER

Ok, thanks for all the comments. Didn´t expect so many in a short time.

1. The trigger create a duplicate key exception, because of the recursive call. Thats why I need to desable the trigger before the Insert instruction.

2. I can't use NO CASCADE clause in an AFTER INSERT TRIGGER.
(Page 321 in the Redbook)
"The SQL standard prevents a BEFORE trigger from ever activating another BEFORE trigger. That activation is prevented by the NO CASCADE clause. Therefore, data modification statements, such as
INSERT, UPDATE, DELETE, any CREATE, are not allowed in a BEFORE trigger."

3. I have total control in the library of this application but wath I want to achive is to mantain consitency in data. Maybe is an odd design and ther is another way.

I Would like to try the handler approach, the performance is not  a problem, this table has no more than 200 rows, is used to store distances between two points and It is almost static (no insert or delete after application setup).

Gary, can You tell me who to manage this error?

Thanks

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial