473,558 Members | 2,876 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Calling a Stored Procedure from a BEFORE UPDATE Trigger

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_timesta mp;
set new_modified_by =current_user;
set new_modified_on =current_timest amp;
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_timest amp;
end@

create trigger schema1.emp_mda ta_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.modifie d_by, new_emp.modifie d_on)@

create trigger schema1.emp_mda ta_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.modifie d_on)@

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

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

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

insert into schema1.emp(fna me, 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?

Aug 22 '06 #1
5 5341
wp******@prodig y.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_timesta mp;
set new_modified_by =current_user;
set new_modified_on =current_timest amp;
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_timest amp;
end@

create trigger schema1.emp_mda ta_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.modifie d_by, new_emp.modifie d_on)@

create trigger schema1.emp_mda ta_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.modifie d_on)@

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

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

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

insert into schema1.emp(fna me, 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/
Aug 22 '06 #2

Serge Rielau wrote:
wp******@prodig y.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_timesta mp;
set new_modified_by =current_user;
set new_modified_on =current_timest amp;
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_timest amp;
end@

create trigger schema1.emp_mda ta_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.modifie d_by, new_emp.modifie d_on)@

create trigger schema1.emp_mda ta_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.modifie d_on)@

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

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

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

insert into schema1.emp(fna me, 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.

Aug 22 '06 #3
wp******@prodig y.net wrote:
Serge Rielau wrote:
>wp******@prodig y.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_timesta mp;
set new_modified_by =current_user;
set new_modified_on =current_timest amp;
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_timest amp;
end@

create trigger schema1.emp_mda ta_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.modifie d_by, new_emp.modifie d_on)@

create trigger schema1.emp_mda ta_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.modifie d_on)@

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

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

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

insert into schema1.emp(fna me, 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/
Aug 23 '06 #4

Serge Rielau wrote:
wp******@prodig y.net wrote:
Serge Rielau wrote:
wp******@prodig y.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_timesta mp;
set new_modified_by =current_user;
set new_modified_on =current_timest amp;
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_timest amp;
end@

create trigger schema1.emp_mda ta_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.modifie d_by, new_emp.modifie d_on)@

create trigger schema1.emp_mda ta_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.modifie d_on)@

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

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

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

insert into schema1.emp(fna me, 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_timesta mp;
set new_modified_by =current_user;
set new_modified_on =current_timest amp;
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_timest amp;
end@

create trigger schema1.emp_mda ta_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.modifie d_by, new_emp.modifie d_on)@

create trigger schema1.emp_mda ta_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.modifie d_by,
new_emp.modifie d_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

Aug 23 '06 #5
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/
Aug 23 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4224
by: Alvin | last post by:
Hi All I need opinions on how to approach my task. I currently have 3 tables: the master table, the archive and a temp table. MASTER: has 3 fields ProductID and ProductNo and Released ARCHIVE: Has 3 ProductID, ProductNo, SoldDate TEMP: ProductID, ProductNo, SoldDate I have a trigger on the master table upon deletion to archive. This is
7
3240
by: Alex | last post by:
Hi all, I am trying to install a java stored procedure via the windows development centre. The linux box is running 8.1 FP4 as is the windoze platform. If I am on the linux box i can install the sample jdbc stored procedures o.k. For the purpose of this test I created a sample procedure that executes "select * from department" when...
2
4682
by: Prem via DBMonster.com | last post by:
hi, i am working on a migration project from Oracle to db2 8.1. i was able to migrate the stored procedures and functions in oracle to db2 with the help of the migration tool kit. now finally when i came to the trigger i am having problems. the trigger is not getting compiled. Triggers call the stored procedures and functions in them. For...
0
1430
by: api | last post by:
Hello, I never worked with SP/triggers in MySQL, I have this problem. On webpage I'd like to show online users, so idea is very simple: in one select (SP) make all necesary things. I begin with: CREATE TRIGGER online_insert AFTER UPDATE ON subs_online FOR EACH ROW DELETE FROM subs_online WHERE ts < (unix_timestamp() - 180);
2
11492
by: syntego | last post by:
We commonly use triggers to log changes to our main tables to historical log tables. In the trigger, we create a concatenated string of the old values by casting them as follows: CAST(O.MYDATE AS CHAR(30)) When directly updating date fields in the main table, the logged value gets saved in the format YYYY-MM-DD as expected.
2
5409
by: Al Willis | last post by:
Hello, I've written an insert trigger to fill in data on 5 columns based on the key field column after a record is added to a table. The trigger works fine. But what I also want to do is to write a stored procedure that will update the 5 columns for the entire table based on the table key field column. I'm new to both triggers and stored...
0
5266
by: Johan Neidenmark | last post by:
When i try to run this SQL statements in iSeries Access for windows (against my customers db2) i get: SQL State: 42904 Vendor Code: -7032 Message: SQL procedure, function, or trigger GET_INVENTORY_SEQUENCE in BPCSAX_PRD not created. Cause . . . . . : SQL procedure, function, or trigger GET_INVENTORY_SEQUENCE in BPCSAX_PRD was not...
3
6190
by: yinzara | last post by:
I have the following trigger that calls a DB2 stored procedure: DROP TRIGGER GGWU.TRI_A_MULTI_PROP@ CREATE TRIGGER GGWU.TRI_A_MULTI_PROP AFTER INSERT ON GGWU.MULTIPLIER_PROPERTY REFERENCING NEW AS POST FOR EACH ROW MODE DB2SQL BEGIN ATOMIC CALL GGWU.PKG_MULT_PROP_INSERT(POST.C_PROPERTY_CODE,POST.I_MULTIPLIER_ID); END@
3
26635
by: Constantine AI | last post by:
Hi we have created a stored procedure to check the dates entered into a lease table does not overlap dates already stored for a lease. However when inserting overlapping lease dates, it allows us to insert this entry into the lease table, we was thinking of doing a trigger to execute the stored procedure to prevent a row being inserted into the...
0
7629
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7549
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7835
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7593
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7914
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6183
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5455
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1164
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.