Calling a Stored Procedure from a BEFORE UPDATE Trigger

[wpellett]

I can not get the SQL compiler to rewrite my SQL UPDATE statement to
include columns being SET in a Stored Procedure being called from a
BEFORE UPDATE trigger.

Example:

create table schema1.emp (
fname varchar(15) not null,
lname varchar(15) not null,
dob date,
created_by varchar(30) not null,
created_on timestamp not null,
modified_by varchar(30) not null,
modified_on timestamp not null,
constraint emp_pk primary key(fname, lname)
)@

create procedure schema1.set_ins_mdata(
out new_created_by varchar(30),
out new_created_on timestamp,
out new_modified_by varchar(30),
out new_modified_on timestamp
)
reads SQL data
language SQL
begin
set new_created_by=current_user;
set new_created_on=current_timestamp;
set new_modified_by=current_user;
set new_modified_on=current_timestamp;
end@
create procedure schema1.set_upd_mdata(
in old_created_by varchar(30),
in old_created_on timestamp,
out new_created_by varchar(30),
out new_created_on timestamp,
out new_modified_by varchar(30),
out new_modified_on timestamp
)
reads SQL data
language SQL
begin
set new_created_by=old_created_by;
set new_created_on=old_created_on;
set new_modified_by=current_user;
set new_modified_on=current_timestamp;
end@

create trigger schema1.emp_mdata_ins_trg
no cascade before insert on schema1.emp
referencing new as new_emp
for each row
call schema1.set_ins_mdata(new_emp.created_by, new_emp.created_on,
new_emp.modified_by, new_emp.modified_on)@

create trigger schema1.emp_mdata_upd_trg
no cascade before update on schema1.emp
referencing old as old_emp new as new_emp
for each row
call schema1.set_upd_mdata(old_emp.created_by, old_emp.created_on,
new_emp.created_by, new_emp.created_on, new_emp.modified_on)@

If I INSERT data into the SCHEMA1.EMP table, the table get populated
fine.

insert into schema1.emp(fname, lname, dob)
values ('Joe', 'Brown', '1970-06-25');

insert into schema1.emp(fname, lname, dob)
values ('Jane', 'Brown', '1975-02-11');

insert into schema1.emp(fname, lname, dob)
values ('Jack', 'Intern', '1996-04-05');

But when I try an UPDATE, only the column(s) on the UPDATE statement
get changed.

update schema1.emp set dob='1980-09-07'
where fname='Jane' and lname='Brown';

I ran an EXPLAIN on the UPDATE statement and the statement was does get
re-written to handle the additional columns being changed in the Stored
Procedure.

If the INSERTs had failed I'd think I was doing something that wasn't
supported.

Any suggestions?

[Serge Rielau]

wp******@prodigy.net wrote:

I can not get the SQL compiler to rewrite my SQL UPDATE statement to
include columns being SET in a Stored Procedure being called from a
BEFORE UPDATE trigger.

Example:

create table schema1.emp (
fname varchar(15) not null,
lname varchar(15) not null,
dob date,
created_by varchar(30) not null,
created_on timestamp not null,
modified_by varchar(30) not null,
modified_on timestamp not null,
constraint emp_pk primary key(fname, lname)
)@

create procedure schema1.set_ins_mdata(
out new_created_by varchar(30),
out new_created_on timestamp,
out new_modified_by varchar(30),
out new_modified_on timestamp
)
reads SQL data
language SQL
begin
set new_created_by=current_user;
set new_created_on=current_timestamp;
set new_modified_by=current_user;
set new_modified_on=current_timestamp;
end@
create procedure schema1.set_upd_mdata(
in old_created_by varchar(30),
in old_created_on timestamp,
out new_created_by varchar(30),
out new_created_on timestamp,
out new_modified_by varchar(30),
out new_modified_on timestamp
)
reads SQL data
language SQL
begin
set new_created_by=old_created_by;
set new_created_on=old_created_on;
set new_modified_by=current_user;
set new_modified_on=current_timestamp;
end@

create trigger schema1.emp_mdata_ins_trg
no cascade before insert on schema1.emp
referencing new as new_emp
for each row
call schema1.set_ins_mdata(new_emp.created_by, new_emp.created_on,
new_emp.modified_by, new_emp.modified_on)@

create trigger schema1.emp_mdata_upd_trg
no cascade before update on schema1.emp
referencing old as old_emp new as new_emp
for each row
call schema1.set_upd_mdata(old_emp.created_by, old_emp.created_on,
new_emp.created_by, new_emp.created_on, new_emp.modified_on)@

If I INSERT data into the SCHEMA1.EMP table, the table get populated
fine.

insert into schema1.emp(fname, lname, dob)
values ('Joe', 'Brown', '1970-06-25');

insert into schema1.emp(fname, lname, dob)
values ('Jane', 'Brown', '1975-02-11');

insert into schema1.emp(fname, lname, dob)
values ('Jack', 'Intern', '1996-04-05');

But when I try an UPDATE, only the column(s) on the UPDATE statement
get changed.

update schema1.emp set dob='1980-09-07'
where fname='Jane' and lname='Brown';

I ran an EXPLAIN on the UPDATE statement and the statement was does get
re-written to handle the additional columns being changed in the Stored
Procedure.

If the INSERTs had failed I'd think I was doing something that wasn't
supported.

Any suggestions?

Yes... add the missing argument to the procedure call. When you do that
your trigger will create successfully. And only a created trigger can
actually work. :-)

Cheers
Serge

PS: You got me scared there for a moment.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[wpellett]

Serge Rielau wrote:

wp******@prodigy.net wrote:

I can not get the SQL compiler to rewrite my SQL UPDATE statement to
include columns being SET in a Stored Procedure being called from a
BEFORE UPDATE trigger.

Example:

create table schema1.emp (
fname varchar(15) not null,
lname varchar(15) not null,
dob date,
created_by varchar(30) not null,
created_on timestamp not null,
modified_by varchar(30) not null,
modified_on timestamp not null,
constraint emp_pk primary key(fname, lname)
)@

create procedure schema1.set_ins_mdata(
out new_created_by varchar(30),
out new_created_on timestamp,
out new_modified_by varchar(30),
out new_modified_on timestamp
)
reads SQL data
language SQL
begin
set new_created_by=current_user;
set new_created_on=current_timestamp;
set new_modified_by=current_user;
set new_modified_on=current_timestamp;
end@
create procedure schema1.set_upd_mdata(
in old_created_by varchar(30),
in old_created_on timestamp,
out new_created_by varchar(30),
out new_created_on timestamp,
out new_modified_by varchar(30),
out new_modified_on timestamp
)
reads SQL data
language SQL
begin
set new_created_by=old_created_by;
set new_created_on=old_created_on;
set new_modified_by=current_user;
set new_modified_on=current_timestamp;
end@

create trigger schema1.emp_mdata_ins_trg
no cascade before insert on schema1.emp
referencing new as new_emp
for each row
call schema1.set_ins_mdata(new_emp.created_by, new_emp.created_on,
new_emp.modified_by, new_emp.modified_on)@

create trigger schema1.emp_mdata_upd_trg
no cascade before update on schema1.emp
referencing old as old_emp new as new_emp
for each row
call schema1.set_upd_mdata(old_emp.created_by, old_emp.created_on,
new_emp.created_by, new_emp.created_on, new_emp.modified_on)@

If I INSERT data into the SCHEMA1.EMP table, the table get populated
fine.

insert into schema1.emp(fname, lname, dob)
values ('Joe', 'Brown', '1970-06-25');

insert into schema1.emp(fname, lname, dob)
values ('Jane', 'Brown', '1975-02-11');

insert into schema1.emp(fname, lname, dob)
values ('Jack', 'Intern', '1996-04-05');

But when I try an UPDATE, only the column(s) on the UPDATE statement
get changed.

update schema1.emp set dob='1980-09-07'
where fname='Jane' and lname='Brown';

I ran an EXPLAIN on the UPDATE statement and the statement was does get
re-written to handle the additional columns being changed in the Stored
Procedure.

If the INSERTs had failed I'd think I was doing something that wasn't
supported.

Any suggestions?

Yes... add the missing argument to the procedure call. When you do that
your trigger will create successfully. And only a created trigger can
actually work. :-)

Cheers
Serge

PS: You got me scared there for a moment.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

In my cutting, pasting, and re-formatting, I left off the sixth
arguement in the CALL procedure. In my testing, I did not leave this
arguement off and I did not get the results I expected.

[Serge Rielau]

wp******@prodigy.net wrote:

Serge Rielau wrote:

>wp******@prodigy.net wrote:

>>I can not get the SQL compiler to rewrite my SQL UPDATE statement to
include columns being SET in a Stored Procedure being called from a
BEFORE UPDATE trigger.

Example:

create table schema1.emp (
fname varchar(15) not null,
lname varchar(15) not null,
dob date,
created_by varchar(30) not null,
created_on timestamp not null,
modified_by varchar(30) not null,
modified_on timestamp not null,
constraint emp_pk primary key(fname, lname)
)@

create procedure schema1.set_ins_mdata(
out new_created_by varchar(30),
out new_created_on timestamp,
out new_modified_by varchar(30),
out new_modified_on timestamp
)
reads SQL data
language SQL
begin
set new_created_by=current_user;
set new_created_on=current_timestamp;
set new_modified_by=current_user;
set new_modified_on=current_timestamp;
end@
create procedure schema1.set_upd_mdata(
in old_created_by varchar(30),
in old_created_on timestamp,
out new_created_by varchar(30),
out new_created_on timestamp,
out new_modified_by varchar(30),
out new_modified_on timestamp
)
reads SQL data
language SQL
begin
set new_created_by=old_created_by;
set new_created_on=old_created_on;
set new_modified_by=current_user;
set new_modified_on=current_timestamp;
end@

create trigger schema1.emp_mdata_ins_trg
no cascade before insert on schema1.emp
referencing new as new_emp
for each row
call schema1.set_ins_mdata(new_emp.created_by, new_emp.created_on,
new_emp.modified_by, new_emp.modified_on)@

create trigger schema1.emp_mdata_upd_trg
no cascade before update on schema1.emp
referencing old as old_emp new as new_emp
for each row
call schema1.set_upd_mdata(old_emp.created_by, old_emp.created_on,
new_emp.created_by, new_emp.created_on, new_emp.modified_on)@

If I INSERT data into the SCHEMA1.EMP table, the table get populated
fine.

insert into schema1.emp(fname, lname, dob)
values ('Joe', 'Brown', '1970-06-25');

insert into schema1.emp(fname, lname, dob)
values ('Jane', 'Brown', '1975-02-11');

insert into schema1.emp(fname, lname, dob)
values ('Jack', 'Intern', '1996-04-05');

But when I try an UPDATE, only the column(s) on the UPDATE statement
get changed.

update schema1.emp set dob='1980-09-07'
where fname='Jane' and lname='Brown';

I ran an EXPLAIN on the UPDATE statement and the statement was does get
re-written to handle the additional columns being changed in the Stored
Procedure.

If the INSERTs had failed I'd think I was doing something that wasn't
supported.

Any suggestions?

Yes... add the missing argument to the procedure call. When you do that
your trigger will create successfully. And only a created trigger can
actually work. :-)

Cheers
Serge

PS: You got me scared there for a moment.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

In my cutting, pasting, and re-formatting, I left off the sixth
arguement in the CALL procedure. In my testing, I did not leave this
arguement off and I did not get the results I expected.

Please repost a working (well failing as expected anyway) repro and I'll
look further.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[wpellett]

Serge Rielau wrote:

wp******@prodigy.net wrote:

Serge Rielau wrote:

wp******@prodigy.net wrote:
I can not get the SQL compiler to rewrite my SQL UPDATE statement to
include columns being SET in a Stored Procedure being called from a
BEFORE UPDATE trigger.

Example:

create table schema1.emp (
fname varchar(15) not null,
lname varchar(15) not null,
dob date,
created_by varchar(30) not null,
created_on timestamp not null,
modified_by varchar(30) not null,
modified_on timestamp not null,
constraint emp_pk primary key(fname, lname)
)@

create procedure schema1.set_ins_mdata(
out new_created_by varchar(30),
out new_created_on timestamp,
out new_modified_by varchar(30),
out new_modified_on timestamp
)
reads SQL data
language SQL
begin
set new_created_by=current_user;
set new_created_on=current_timestamp;
set new_modified_by=current_user;
set new_modified_on=current_timestamp;
end@
create procedure schema1.set_upd_mdata(
in old_created_by varchar(30),
in old_created_on timestamp,
out new_created_by varchar(30),
out new_created_on timestamp,
out new_modified_by varchar(30),
out new_modified_on timestamp
)
reads SQL data
language SQL
begin
set new_created_by=old_created_by;
set new_created_on=old_created_on;
set new_modified_by=current_user;
set new_modified_on=current_timestamp;
end@

create trigger schema1.emp_mdata_ins_trg
no cascade before insert on schema1.emp
referencing new as new_emp
for each row
call schema1.set_ins_mdata(new_emp.created_by, new_emp.created_on,
new_emp.modified_by, new_emp.modified_on)@

create trigger schema1.emp_mdata_upd_trg
no cascade before update on schema1.emp
referencing old as old_emp new as new_emp
for each row
call schema1.set_upd_mdata(old_emp.created_by, old_emp.created_on,
new_emp.created_by, new_emp.created_on, new_emp.modified_on)@

If I INSERT data into the SCHEMA1.EMP table, the table get populated
fine.

insert into schema1.emp(fname, lname, dob)
values ('Joe', 'Brown', '1970-06-25');

insert into schema1.emp(fname, lname, dob)
values ('Jane', 'Brown', '1975-02-11');

insert into schema1.emp(fname, lname, dob)
values ('Jack', 'Intern', '1996-04-05');

But when I try an UPDATE, only the column(s) on the UPDATE statement
get changed.

update schema1.emp set dob='1980-09-07'
where fname='Jane' and lname='Brown';

I ran an EXPLAIN on the UPDATE statement and the statement was does get
re-written to handle the additional columns being changed in the Stored
Procedure.

If the INSERTs had failed I'd think I was doing something that wasn't
supported.

Any suggestions?
Yes... add the missing argument to the procedure call. When you do that
your trigger will create successfully. And only a created trigger can
actually work. :-)

Cheers
Serge

PS: You got me scared there for a moment.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

In my cutting, pasting, and re-formatting, I left off the sixth
arguement in the CALL procedure. In my testing, I did not leave this
arguement off and I did not get the results I expected.

Please repost a working (well failing as expected anyway) repro and I'll
look further.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Serge,

Here's the updated DDL to re-create this problem.

create table schema1.emp (
fname varchar(15) not null,
lname varchar(15) not null,
dob date,
created_by varchar(30) not null,
created_on timestamp not null,
modified_by varchar(30) not null,
modified_on timestamp not null,
constraint emp_pk primary key(fname, lname)
)@

create procedure schema1.set_ins_mdata(
out new_created_by varchar(30),
out new_created_on timestamp,
out new_modified_by varchar(30),
out new_modified_on timestamp
)
reads SQL data
language SQL
begin
set new_created_by=current_user;
set new_created_on=current_timestamp;
set new_modified_by=current_user;
set new_modified_on=current_timestamp;
end@
create procedure schema1.set_upd_mdata(
in old_created_by varchar(30),
in old_created_on timestamp,
out new_created_by varchar(30),
out new_created_on timestamp,
out new_modified_by varchar(30),
out new_modified_on timestamp
)
reads SQL data
language SQL
begin
set new_created_by=old_created_by;
set new_created_on=old_created_on;
set new_modified_by=current_user;
set new_modified_on=current_timestamp;
end@

create trigger schema1.emp_mdata_ins_trg
no cascade before insert on schema1.emp
referencing new as new_emp
for each row
call schema1.set_ins_mdata(new_emp.created_by, new_emp.created_on,
new_emp.modified_by, new_emp.modified_on)@

create trigger schema1.emp_mdata_upd_trg
no cascade before update on schema1.emp
referencing old as old_emp new as new_emp
for each row
call schema1.set_upd_mdata(old_emp.created_by, old_emp.created_on,
new_emp.created_by, new_emp.created_on, new_emp.modified_by,
new_emp.modified_on)@

The only change was to the UPDATE Trigger.

If I move the stored procedure logic into the trigger body, everything
works fine.

Thanks.
Bill

[Serge Rielau]

I can reproduce.
The problem occurs on BEFORE UPDATE triggers if the transition variable
wasn't in the SET clause of the UPDATE.
The general workaround is to "prime" the new transition variables using
regular SET statements (non sensical in you trivial example I understand):

CREATE TRIGGER ... BEFORE UPDATE ...
REFERENCING NEW AS n
BEGIN ATOMIC
SET n.c1 = '', n.c2 = 0;
CALL proc(n.c1, n.c2);
END

Can you open a PMR please? I can confirm that this is a defect and it
requires an APAR. Please ask support to route it straight to Level 3.
Have them contact me for details :-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/