By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,310 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,310 IT Pros & Developers. It's quick & easy.

need help with the trigger

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.