Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » DTP » Instead of Trigger Error
Instead of Trigger Error [message #597079] Tue, 26 January 2010 14:25 Go to next message
K Milligan is currently offline K Milligan
Messages: 10
Registered: January 2010
Junior Member
The following Trigger definition fails when executed as part of an SQL script that's creating DB2 for i database objects.

CREATE TRIGGER INACTIVATE_EMPLOYEE
INSTEAD OF UPDATE ON ACTIVE_EMPLOYEES
REFERENCING OLD Old_row NEW New_row FOR EACH ROW
BEGIN ATOMIC
INSERT IGNORE INTO inactive_employees (emp_mast_pk)
select emp_mast_pk
from emp_mast e
where old_row.empno = e.empno;
END
;

The statement fails with an SQL0104 error (Token <end of statement> was not valid.

This SQL statement works fine with other IBM i SQL interfaces.
Re: Instead of Trigger Error [message #597127 is a reply to message #597079] Wed, 27 January 2010 02:03 Go to previous message
Brian Payton is currently offline Brian Payton
Messages: 152
Registered: July 2009
Senior Member
I suspect that the problem is that semi-colon is used to terminate the
INSERT IGNORE INTO statement inside the trigger definition. The run service is
probably dividing the statement into pieces based on the statement
terminator, so it's executing the statement only up to the first
semi-colon. The SQL editor and it's run service needs a
database-specific adapter for DB2 for i to handle this, which
unfortunately isn't included with DTP.

You might want to try the IBM Data Studio product, available here (free
download):
http://www-01.ibm.com/software/data/optim/data-studio/

K Milligan wrote:
> The following Trigger definition fails when executed as part of an SQL
> script that's creating DB2 for i database objects.
>
> CREATE TRIGGER INACTIVATE_EMPLOYEE INSTEAD OF UPDATE ON
> ACTIVE_EMPLOYEES REFERENCING OLD Old_row NEW New_row FOR EACH ROW
> BEGIN ATOMIC
> INSERT IGNORE INTO inactive_employees (emp_mast_pk) select
> emp_mast_pk from emp_mast e where old_row.empno =
> e.empno; END
> ;
>
> The statement fails with an SQL0104 error (Token <end of statement> was
> not valid.
> This SQL statement works fine with other IBM i SQL interfaces.
>
Previous Topic:Data Soruce Explorer, No tables, only Schemas visible
Next Topic:SQLModel doesn't use containment references ?
Goto Forum:
  


Current Time: Fri Sep 19 17:56:15 GMT 2014

Powered by FUDForum. Page generated in 0.01657 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software