trigger updating another table gives SQL0206N

[Prince Kumar]

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,

[Knut Stolze]

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

[Prince Kumar]

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.

[Knut Stolze]

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