Db2 Sql Error Sqlcode 989
Contents |
An INSERT statement was specified in the FROM clause of a https://www.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00989n.html SELECT statement of a subselect or a SELECT INTO statement, but the underlying target base table of the INSERT has an AFTER trigger defined http://www.sqlerror.de/db2_sql_error_-989_sqlstate_560C3.html which modifies the table being inserted. This is disallowed. System action: The statement cannot be processed. Programmer response: Avoid using the INSERT statement within SELECT, or change the trigger so that it does not modify the table that is the target of the INSERT statement. <- BACK TO INDEX This is a free Service. I assume no warranty for misprints, and for the correctness and completeness of information and entries.
Events Forums DB2-LDB2-L ArchivesNA ConferenceEMEA Conference Resources BlogsIDUG eBulletinIDUG NewsroomCommunitiesRegional User Groups Articles&Content IDUG Content ArticlesTech ChannelBeginner's BlogDB2 Hot Topics BlogContent LibraryIDUG YouTubePast Conference ContentCodeplace Library GetInvolved Volunteering with IDUGVolunteer PositionsJoin a Regional User GroupBecome an IDUG MentorSponsorship OpportunitiesJoin a http://www.idug.org/p/fo/et/thread=32619 CommunitySpeaker Community [DB2-L] z/OS DB2v8 - Update Trigger Help Walter Janißen [DB2-L] z/OS DB2v8 - Update Trigger Help August 8, 2008 02:39 AM Sam I think, a before trigger does not work, because it will be fired before the update, so the column has the old value. Did you try: CREATE TRIGGER Trigger AFTER UPDATE OF column-name ON table REFERENCING NEW as N FOR db2 sql EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE table A SET N.SHIP_DATE = N.DUE_DATE ,N.CUST_DATE = CASE A.CUST_DATE WHEN = '01.01.0001' THEN N.DUE_DATE ELSE A.CUST_DATE END WHERE A.key_column1 = N.key_column1 AND .... END; Mit freundlichen Grüßen Walter Janißen ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40ß198 Düsseldorf Tel.: +49 211 477-2928 Fax: +49 211 477-2615 mailto:[login to unmask email] Vorsitzender des Aufsichtsrats: Jürgen Vetter db2 sql error Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön. Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996 ________________________________ Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Sam Baugh Gesendet: Donnerstag, 7. August 2008 22:06 An: [login to unmask email] Betreff: [DB2-L] z/OS DB2v8 - Update Trigger Help I have a situation where I need to create a trigger based on the update of column "due_date". When this column changes, I want to set another column to the same value regardless. I also want to change the value of another column, but only if that column is a specific value. Can this be done as one trigger? My attempts so far have been invalid, and I have little experience writing triggers. Logic: due_date changes set ship_date = due_date if cust_date = '0001-01-01' then set cust_date = due_date Attempted - fails with -797: BEGIN ATOMIC SET NROW.SHIP_DATE = NROW.DUE_DATE; VALUES CASE WHEN OROW.CUST_DATE = '0001-01-01' THEN SET NROW.CUST_DATE = NROW.DUE_DATE; END; Thanks! The information in this communication, including any attachments, is intended only for the person or entity to which it is addr
be down. Please try the request again. Your cache administrator is webmaster. Generated Thu, 06 Oct 2016 14:48:19 GMT by s_hv996 (squid/3.5.20)