trigger question

[Alexander Pope]

I am using db2 udb v8.2 AIX

I have created trigger, however I am not confident it meets industry
standards. If I make it fail, I cant tell from the message where it is
failing. what can I add to trap the errors properly?
CREATE TRIGGER myschema.t1_upd _t
AFTER UPDATE OF dt ON myschema.t1
REFERENCING NEW AS N
FOR EACH ROW mode db2sql

when (N.dt is not null)
BEGIN ATOMIC

DECLARE rs INTEGER DEFAULT 0;

insert into myschema.audit
select t2.* from myschema.t2
where t2.id = N.id
and t2.dt = N.dt
and t2.cd = 'X';

insert into myschema.audit2
select t2.* from myschema.t2
where t2.id = N.id
and t2.dt = N.dt
and t2.cd = 'X';
GET DIAGNOSTICS rs = RETURN_STATUS;
values 'failed';
end@

[Serge Rielau]

What error are you getting?

the following two lines won't do anything:
GET DIAGNOSTICS rs = RETURN_STATUS;
values 'failed';
What do you want them to do?

If a trigger in DB2 for LUW encounters an error the whole statement fails.
Use the SIGNAL statement to raise an error.
If you want to do compelx logic, including full condition handling use
the CALL statement in DB2 V8.2 to call out to a store procedure.
The stored procedure can do the heavy lifting for the trigger.

Cheers
Serge

[Alexander Pope]

Serge Rielau <sr*****@ca.ibm .com> wrote in message news:<2t******* ******@uni-berlin.de>...

What error are you getting?

the following two lines won't do anything:
GET DIAGNOSTICS rs = RETURN_STATUS;
values 'failed';
What do you want them to do?

If a trigger in DB2 for LUW encounters an error the whole statement fails.
Use the SIGNAL statement to raise an error.
If you want to do compelx logic, including full condition handling use
the CALL statement in DB2 V8.2 to call out to a store procedure.
The stored procedure can do the heavy lifting for the trigger.

Cheers
Serge

now I recall why I put the 'heavy lifting' in the trigger - I could
not get it to call the proc. I created a generic set of objects and
got the same error.
calling the proc works on its own but not when the trigger fires:

try this:

drop table hist2003;
drop table hist2004;
drop table hist2005;
drop view hist;

drop table archivesource;
drop trigger source_upd_t;

drop table source1;
drop table sourceopen;

drop procedure archive_sp;

create table hist2003(cola integer not null, colb integer not null,
pay_dt timestamp , closed_dt timestamp );
ALTER TABLE hist2003 ADD CONSTRAINT hist2003_chk CHECK (year(pay_dt) =
2003);

create table hist2004(cola integer not null, colb integer not null,
pay_dt timestamp not null, closed_dt timestamp not null );
ALTER TABLE hist2004 ADD CONSTRAINT hist2004_chk CHECK (year(pay_dt) =
2004);

create table hist2005(cola integer not null, colb integer not null,
pay_dt timestamp not null, closed_dt timestamp not null );
ALTER TABLE hist2005 ADD CONSTRAINT hist2005_chk CHECK (year(pay_dt) =
2005);

create view hist (cola, colb, pay_dt, closed_dt) as
select cola, colb, pay_dt, closed_dt from hist2003
union all
select cola, colb, pay_dt, closed_dt from hist2004
union all
select cola, colb, pay_dt, closed_dt from hist2005;
create table source1 (cola integer not null, colb integer not null,
pay_dt timestamp not null, closed_dt timestamp );
create table archivesource (cola integer not null, colb integer not
null, open_dt timestamp not null);

create table sourceopen (cola integer not null, colb integer not null,
open_dt timestamp not null);
insert into source1
values
(100, 5555, '2003-01-01-00.00.00.000000 ',null),
(100, 5556, '2004-01-02-00.00.00.000000 ',null),
(100, 5557, '2005-01-03-00.00.00.000000 ',null);
insert into sourceopen
values
(100, 5555, '2005-01-03-00.00.00.000000 '),
(100, 5556, '2005-01-03-00.00.00.000000 '),
(100, 5557, '2005-01-03-00.00.00.000000 ');

create procedure archive_sp (IN custid integer, IN orderid integer)
Language SQL
Begin
insert into archivesource
select * from sourceopen
where cola = custid
and colb = orderid;
insert into hist
select cola, colb, pay_dt, closed_dt
from source1
where cola = custid
and colb = orderid;
end@

CREATE TRIGGER source_upd_t
AFTER UPDATE OF closed_dt ON source1
REFERENCING NEW AS N
FOR EACH ROW mode db2sql
when (N.closed_dt is not null)
begin atomic
call archive_sp (N.cola, N.colb);
end@
--Test#1:confirm procedure works on its own
delete from hist;
delete from archivesource;
call archive_sp (100,5555);
select * from hist;
select * from archivesource;

--Test#2: confirm update action fires trigger, which calls proc
--confirm hist and archive tables are empty prior to running trigger
fire
delete from hist;
delete from archivesource;

update source1 set closed_dt = current timestamp where colb in (5555,
5556);

--confirm 1 row in each table
select * from hist;
select * from archivesource;
update source1 set closed_dt = current timestamp where colb in (5555,
5556)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0723N An error occurred in a triggered SQL statement in trigger
"SOURCE_". Information returned for the error includes SQLCODE
"-746", SQLSTATE "57053" and message tokens
"ARCHIVE_SP|SQL 041020162010682 |REA". SQLSTATE=09000

[Serge Rielau]

Why do you select from the trigger target?
Simply pass in what you need through the procedure.
You may get a few parameters in your real life scenario, but that will
solve your problem.

Cheers
Serge

[Alexander Pope]

Serge Rielau <sr*****@ca.ibm .com> wrote in message news:<2t******* ******@uni-berlin.de>...

Why do you select from the trigger target?
Simply pass in what you need through the procedure.
You may get a few parameters in your real life scenario, but that will
solve your problem.

Cheers
Serge

Serge, I changed the trigger to not select from the source table.
it worked.

thx,
ap
create procedure archive_sp (IN custid integer, IN orderid integer, IN
paymdt timestamp, IN closedt timestamp)
Language SQL
Begin
FOR z_cursor AS
SELECT source_id,cola, colb, open_dt
FROM sourceopen s
WHERE s.cola = custid
AND s.colb = orderid
DO

insert into archivesource values
(z_cursor.sourc e_id,z_cursor.c ola, z_cursor.colb, z_cursor.open_d t)
;
insert into hist values (z_cursor.cola, z_cursor.colb, paymdt,
closedt) ;
END FOR;
end@