468,556 Members | 2,135 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,556 developers. It's quick & easy.

trigger updating another table gives SQL0206N

Hi All,

I am trying to a get a trigger retrieved from Oracle to work on DB2
UDB 8.1. I am getting the following error when trying to create the
trigger. How would I resolve this?

create table dept (
dept# int not null primary key,
deptname varchar(30))
@

create table rep (
rep# int not null primary key,
dept# int ,
repname varchar(30))
@

create trigger brd_dept no cascade
before delete on dept
for each row
mode db2sql
begin atomic
--similar to cascade set null
update rep set dept# = null where dept# = old.dept#;
end
@

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0206N "OLD.DEPT#" is not valid in the context where it is used.
LINE
NUMBER=7. SQLSTATE=42703

thanks,
Nov 12 '05 #1
3 6428
Prince Kumar <gs**@yahoo.com> wrote:
Hi All,

I am trying to a get a trigger retrieved from Oracle to work on DB2
UDB 8.1. I am getting the following error when trying to create the
trigger. How would I resolve this?

create table dept (
dept# int not null primary key,
deptname varchar(30))
@

create table rep (
rep# int not null primary key,
dept# int ,
repname varchar(30))
@

create trigger brd_dept no cascade
before delete on dept
You need to specify a correlation name for OLD here like this:

REFERENCING OLD AS old
for each row
mode db2sql
begin atomic
--similar to cascade set null
update rep set dept# = null where dept# = old.dept#;
end
@


You can also not update the other table in a BEFORE trigger, so use an AFTER
trigger instead.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #2
Thanks for the response.

I don't think neither of the above solutions work for me.

1, referencing OLD as old is the default and hence I do not need to
explicitly specify.

2, I must use before trigger. The trigger I posted is a simple sample.
I have many such oracle before triggers (insert/update etc), I want to
convert. All of them gives the same error. May be there is a simple
way of rewriting them.

Thanks,

Knut Stolze <st****@de.ibm.com> wrote in message news:<bn**********@fsuj29.rz.uni-jena.de>...
Prince Kumar <gs**@yahoo.com> wrote:
Hi All,

I am trying to a get a trigger retrieved from Oracle to work on DB2
UDB 8.1. I am getting the following error when trying to create the
trigger. How would I resolve this?

create table dept (
dept# int not null primary key,
deptname varchar(30))
@

create table rep (
rep# int not null primary key,
dept# int ,
repname varchar(30))
@

create trigger brd_dept no cascade
before delete on dept


You need to specify a correlation name for OLD here like this:

REFERENCING OLD AS old
for each row
mode db2sql
begin atomic
--similar to cascade set null
update rep set dept# = null where dept# = old.dept#;
end
@


You can also not update the other table in a BEFORE trigger, so use an AFTER
trigger instead.

Nov 12 '05 #3
Prince Kumar <gs**@yahoo.com> wrote:
Thanks for the response.

I don't think neither of the above solutions work for me.

1, referencing OLD as old is the default and hence I do not need to
explicitly specify.
Hmmm... where did you find that information? In the SQL Reference I use I
cannot find anything stating that REFERENCING OLD AS old would be the
default. So I believe you might be mistaken.
2, I must use before trigger. The trigger I posted is a simple sample.
I have many such oracle before triggers (insert/update etc), I want to
convert. All of them gives the same error. May be there is a simple
way of rewriting them.
Why exactly do you need this to be a BEFORE trigger? What's the technical
reason?
Knut Stolze <st****@de.ibm.com> wrote in message
news:<bn**********@fsuj29.rz.uni-jena.de>...
Prince Kumar <gs**@yahoo.com> wrote:
> Hi All,
>
> I am trying to a get a trigger retrieved from Oracle to work on DB2
> UDB 8.1. I am getting the following error when trying to create the
> trigger. How would I resolve this?
>
> create table dept (
> dept# int not null primary key,
> deptname varchar(30))
> @
>
> create table rep (
> rep# int not null primary key,
> dept# int ,
> repname varchar(30))
> @
>
> create trigger brd_dept no cascade
> before delete on dept


You need to specify a correlation name for OLD here like this:

REFERENCING OLD AS old
> for each row
> mode db2sql
> begin atomic
> --similar to cascade set null
> update rep set dept# = null where dept# = old.dept#;
> end
> @


You can also not update the other table in a BEFORE trigger, so use an
AFTER trigger instead.


--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by db2admin | last post: by
9 posts views Thread by Chico Che | last post: by
3 posts views Thread by lenygold via DBMonster.com | last post: by
1 post views Thread by UniDue | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.