problem with trigger

dipankar.sarma30 · #1 (**permalink**) 07-14-12, 03:00

i have created a trigger and use this

referencing new_table as inserted;
begin
set "asd"=(select col1 from inserted);

end

error i am getting -

The definition of trigger "name" includes an invalid use of correlation name or transition table name "INSERTED". Reason code="4".. SQLCODE=-696, SQLSTATE=42898, DRIVER=3.63.108

tonkuma · #2 (**permalink**) 07-14-12, 03:23

Please see manuals

SQL0696N

Code:

Explanation

The trigger definition included an invalid use of name.
 The value of reason-code identifies the roblem:

...
...
...

4
OLD TABLE name and NEW TABLE name are not allowed in a trigger
 if the trigger is defined using a compound SQL (compiled) statement.

The statement cannot be processed.

...

Anyway,
why do you want to use "NEW TABLE" instead of "NEW"?

dipankar.sarma30 · #3 (**permalink**) 07-14-12, 03:26

hi

i am using ibm data studio.
when i use begin atomic instead of begin then i am getting this error

An unexpected token "set "asd" = (select "col1" from" was found following "ey" decimal(16,0)". Expected tokens may include: "
".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.63.108

tonkuma · #4 (**permalink**) 07-14-12, 03:38

Please publish your DB2 version/release/fixpack and platform OS
and whole of your CREATE TRIGGER statement(and CREATE TABLE statement, if necessary).

dipankar.sarma30 · #5 (**permalink**) 07-14-12, 03:46

hi

version is 9.7v and os is windows 7
sorry cant share the code because it is confidential.

papadi · #6 (**permalink**) 07-14-12, 19:49

Quote:

sorry cant share the code because it is confidential.

Data is often confidential - meta-data, maybe not so much.

Even so, change the "confidential" stuff to something generic, try that, and post anything that is a surprise or unwanted.

tonkuma · #7 (**permalink**) 07-14-12, 20:26

Root cause of a syntax error(SQLCODE=-104) was sometimes laid in earlier than the phrase reported by error message.

Internet Services

Web Development

Digital Marketing

Consumer Tech