SEO

#1 (**permalink**) 02-26-2008, 10:15 AM

hallo!

i have the following questing:

is it possible to reference the trigger transition tables (OLD_TABLE,
NEW_TABLE) inside a procedure which is called from a statement trigger?

e.g. i need a trigger
create trigger trig_u_a after update on a
referencing NEW_TABLE as new OLD_TABLE as old
for each statement
mode db2sql
begin atomic
values(proc_u_a(new, old));
end

the stored procedure for example should look like

create procedure proc_u_a(IN new tabletype, IN old tabletype)
language sql
....
insert into tab_c select new.col1, old.col1 ... from new, old
....

is this possible?

thanks
andreas lederer

#2 (**permalink**) 02-26-2008, 10:17 AM

On DB2 z/Series you can pass the transition table via a table-locator.
DB2 i/Series I don't know.
DB2 for LUW you can't do it directly.
Here is a trick:

CREATE SEQUENCE uid;
CREATE TABLE T1_NT AS (SELECT 1 AS id, T1.* FROM T1) DEFINITION ONLY;

CREATE TRIGGER ... AFTER .. ON T1 REFERENCING NEW_TABLE AS nt ....
BEGIN ATOMIC
INSERT INTO T1_NT SELECT NEXT VALUE FOR uid, NT.* FROM Nt;
VALUES (proc(PREVIOUS VALUE FOR uid));
DELETE FROM T1_NT WHERE id = PREVIOUS VALUE FOR uid;
END

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode