Before delete triggers

[uninfmx]

Hi

If one or mode records get deleted from t1 (see below), I'd like
delete all the corresponding records from t2. There is no foreign key
relationship between t2 and t1, so cascading delete is not an option.
V8.2 AIX

create table t1
(
x int
);
create table t2
(
y int
);

insert into t1 values (2);
insert into t2 values (2);
Can this be accomplished using before delete trigger:

create trigger some_del
no cascade before delete on t1
referencing old as old_t for each row
delete from t2 where y= old_t.x
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0696N The definition of trigger "AAA.some_del " includes an invalid
use
of correlation name or transition table name "old". Reason code="1".
LINE
NUMBER=3. SQLSTATE=42898
Thanks

[Serge Rielau]

un*****@yahoo.com wrote:

Hi

If one or mode records get deleted from t1 (see below), I'd like
delete all the corresponding records from t2. There is no foreign key
relationship between t2 and t1, so cascading delete is not an option.
V8.2 AIX

create table t1
(
x int
);
create table t2
(
y int
);

insert into t1 values (2);
insert into t2 values (2);
Can this be accomplished using before delete trigger:

create trigger some_del
no cascade before delete on t1
referencing old as old_t for each row
delete from t2 where y= old_t.x
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0696N The definition of trigger "AAA.some_del " includes an invalid
use
of correlation name or transition table name "old". Reason code="1".
LINE
NUMBER=3. SQLSTATE=42898
Thanks

Use an AFTER DELETE trigger.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[uninfmx]

Thanks. The 3rd party software requires that this be a before delete
trigger - don't know why. I did mention after delete trigger to them.
Serge Rielau wrote:

un*****@yahoo.com wrote:

Hi

If one or mode records get deleted from t1 (see below), I'd like
delete all the corresponding records from t2. There is no foreign key relationship between t2 and t1, so cascading delete is not an option. V8.2 AIX

create table t1
(
x int
);
create table t2
(
y int
);

insert into t1 values (2);
insert into t2 values (2);
Can this be accomplished using before delete trigger:

create trigger some_del
no cascade before delete on t1
referencing old as old_t for each row
delete from t2 where y= old_t.x
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0696N The definition of trigger "AAA.some_del " includes an invalid use
of correlation name or transition table name "old". Reason code="1". LINE
NUMBER=3. SQLSTATE=42898
Thanks

Use an AFTER DELETE trigger.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

un*****@yahoo.com wrote:

Thanks. The 3rd party software requires that this be a before delete
trigger - don't know why. I did mention after delete trigger to them.
Serge Rielau wrote:

un*****@yahoo.com wrote:

Hi

If one or mode records get deleted from t1 (see below), I'd like
delete all the corresponding records from t2. There is no foreign
key
relationship between t2 and t1, so cascading delete is not an
option.
V8.2 AIX

create table t1
(
x int
);
create table t2
(
y int
);

insert into t1 values (2);
insert into t2 values (2);
Can this be accomplished using before delete trigger:

create trigger some_del
no cascade before delete on t1
referencing old as old_t for each row
delete from t2 where y= old_t.x
DB21034E The command was processed as an SQL statement because it
was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0696N The definition of trigger "AAA.some_del " includes an
invalid
use
of correlation name or transition table name "old". Reason
code="1".
LINE
NUMBER=3. SQLSTATE=42898
Thanks

Use an AFTER DELETE trigger.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

*sigh*There are good semantic reasons why the SQL Standard prohibits
updates in before triggers....
Anyway. Ask the vendor whether they are aware of the "ISV enablement
team" of IBM. Porting help can be arranged.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab