473,225 Members | 1,247 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

need help with the trigger

Hi everybody!
I have an INSERT stattement like this:

insert into ELIGIBLE_PAY
select from ELIGIBLE_PAY_B
where cust_id = 999999;

after insert i would like to delete row from source table by creating the
following TRIGGER:

CREATE TRIGGER DEL_FROM_ELIGIBLE_PAY_B
AFTER INSERT
ON ELIGIBLE_PAY
REFERENCING NEW_TABLE AS newtab
FOR EACH STATEMENT
MODE DB2SQL
DELETE FROM ELIGIBLE_PAY_B
WHERE CUST_ID = newtab.CUST_ID;

and i got an error message:
SQL0206N "NEWTAB.CUST_ID " is not valid in the context where it is used.
Any idea how to fix this trigger?
Thank's in advance.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200807/1

Jul 8 '08 #1
3 1619
lenygold via DBMonster.com wrote:
Hi everybody!
I have an INSERT stattement like this:

insert into ELIGIBLE_PAY
select from ELIGIBLE_PAY_B
where cust_id = 999999;

after insert i would like to delete row from source table by creating the
following TRIGGER:

CREATE TRIGGER DEL_FROM_ELIGIBLE_PAY_B
AFTER INSERT
ON ELIGIBLE_PAY
REFERENCING NEW_TABLE AS newtab
FOR EACH STATEMENT
MODE DB2SQL
DELETE FROM ELIGIBLE_PAY_B
WHERE CUST_ID = newtab.CUST_ID;

and i got an error message:
SQL0206N "NEWTAB.CUST_ID " is not valid in the context where it is used.
Any idea how to fix this trigger?
Thank's in advance.

CREATE TRIGGER DEL_FROM_ELIGIBLE_PAY_B
AFTER INSERT
ON ELIGIBLE_PAY
REFERENCING NEW AS new
FOR EACH ROW
MODE DB2SQL
DELETE FROM ELIGIBLE_PAY_B
WHERE CUST_ID = newtab.CUST_ID;
That should help.

Cheers
Serge

PS: The MODE DB2SQL stuff shouldn't be needed anymore unless you are on
a really old version.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jul 8 '08 #2
Thak you very much it is working.

lenygold wrote:
>Hi everybody!
I have an INSERT stattement like this:

insert into ELIGIBLE_PAY
select from ELIGIBLE_PAY_B
where cust_id = 999999;

after insert i would like to delete row from source table by creating the
following TRIGGER:

CREATE TRIGGER DEL_FROM_ELIGIBLE_PAY_B
AFTER INSERT
ON ELIGIBLE_PAY
REFERENCING NEW_TABLE AS newtab
FOR EACH STATEMENT
MODE DB2SQL
DELETE FROM ELIGIBLE_PAY_B
WHERE CUST_ID = newtab.CUST_ID;

and i got an error message:
SQL0206N "NEWTAB.CUST_ID " is not valid in the context where it is used.
Any idea how to fix this trigger?
Thank's in advance.
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200807/1

Jul 8 '08 #3
An INSERT followed by a DELETE looks like an UPDATE, or a MERGE to
me. Why the extra code instead of a single statement?
Jul 9 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: ZRexRider | last post by:
Hi, I have trigger that enforces the creation of a sortorder that is always 1 digit higher than the current highest on Inserts. This trigger works great if I add one row at a time so I think...
7
by: Lucio Chiessi | last post by:
Hi for all on this... I'm using MS SQL Server 7.0 SP4 in some customers to store some data from an aplication developed by me. I created an trigger to run on update. When I run an update...
2
by: Ken | last post by:
I got an Access database that need to be converted to Oracle 9i. Somehow the Trigger we created to simulate the "AUTO NUMBER" on Access could not create the sequence number as soon as the value has...
4
by: Peter Lin | last post by:
Dear all; I need to monitor a xml file so that I can update my data in hashtable, but the problem is it will trigger more than one event for a lastwrite action(I trigger this action by add...
5
by: Raphael Bauduin | last post by:
Hi, I'm looking at the logging of a database we'll put in production soon. I've seen some posts on this list about history tables, like mentioned in...
2
by: TJ Talluto | last post by:
Instead of putting the same 12 columns on every table (these 12 columns contain info about who created the record and when, for example)... It may be more efficient to make a new table to hold that...
0
by: Michael L | last post by:
Hi Guys(I apologize for the lengty post - Im trying to explain it as best i can) I've been cracking my head on this one for the past 24+ hours and i have tried creating the function in ten...
1
by: nDaKota | last post by:
I am current converting PowerBuilder to C#. Now I'm having problem on triggers. I understand what a triggers does. The problem is what happens if a trigger is being triggered? Example: I execute a...
1
by: candide_sh | last post by:
hello, I googled around some time but I found no solution for this issue (SS2000). I have a table tblB which has to be filled whenever in another table tblA records are inserted, updated or...
12
by: m1st | last post by:
I wanna create delete trigger. Idea is when user deletes the record from table trigger moves this record to another table (this table is copy has the same fields, types) But all this I want to do...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.