Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DB2 SQL TRIGGER QUERY

Posted on 2009-04-22
2
Medium Priority
?
304 Views
Last Modified: 2012-05-06
Hi all, I'm having trouble with another trigger within DB2.

I have 3 tables PROD_INFO, MY_PRODUCTS and MY_SUPPLIERS
What I'm trying to do is,
when a line is inserted into MY_SUPPLIERS, MY_PRODUCTS is populated using a SUPPLIER_KEY to search the PROD_INFO table.

The code I have used so far (below) works as required for the first line inserted into MY_SUPPLIERS but I then receive an error (SQLCODE "-803", SQLSTATE "23505") when th trigger next fires.

It is as though it's trying to add the same products a second time (even though I am using the referencing new as n), which is restricted by my table Keys.
CREATE TRIGGER schema."MY_PRODUCTS" 
AFTER  INSERT  ON MY_SUPPLIERS  
REFERENCING  NEW AS n  
FOR EACH ROW  
MODE DB2SQL 
 
BEGIN ATOMIC
INSERT INTO MY_PRODUCTS 
(PROD_KEY, SUPPLIER_KEY, PROD_CODE, PROD_DESC, PRICE)
 
SELECT PROD_KEY, PROD_INFO.SUPPLIER_KEY, PROD_CODE, PROD_DESC, PRICE
 
FROM PROD_INFO, MY_SUPPLIERS
WHERE n.SUPPLIER_KEY = PROD_INFO.SUPPLIER_KEY;
 
END

Open in new window

0
Comment
Question by:taylor99
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 24204155
i think you should try this

CREATE TRIGGER schema."MY_PRODUCTS"
AFTER  INSERT  ON MY_SUPPLIERS  
REFERENCING  NEW AS n  
FOR EACH ROW  
MODE DB2SQL
 
BEGIN ATOMIC
INSERT INTO MY_PRODUCTS
(PROD_KEY, SUPPLIER_KEY, PROD_CODE, PROD_DESC, PRICE)
 
SELECT PROD_KEY, PROD_INFO.SUPPLIER_KEY, PROD_CODE, PROD_DESC, PRICE
 
FROM PROD_INFO
WHERE n.SUPPLIER_KEY = PROD_INFO.SUPPLIER_KEY;
 
END


you had a redundant reference to MY_SUPPLIERS in the from clause
0
 

Author Comment

by:taylor99
ID: 24205318
Of course, that will be why it was attempting to enter the information twice.

Thanks MOMI!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question