469,602 Members | 1,984 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

Before Trigger and UDFs (table)

Hello

Environment: DB2 V8 LUW FP12.

I have a function that returns a table. I am trying to use it inside a
before trigger:
create trigger TRG.T_MACHINE_RTM
before insert on CAD.TBL_MACHINE
REFERENCING NEW as N
for EACH ROW
begin ATOMIC

insert into CAD.T_MACHINE_RTMDB select * from
TABLE(AFU.TABLE_DB_MID('x')) as A;

end@

When I run this I get the following error:

SQL0797N The trigger "TRG.T_MACHINE_RTM" is defined with an
unsupported
triggered SQL statement. LINE NUMBER=10. SQLSTATE=42987

I have also tried a variation: I converted the function to return a
VARCHAR, and changed my insert statement to use VALUES (x,y,
AFU.function). But I also got the same error.

Is there any way to make this work?

Thanks in advance,

Michel

Sep 1 '06 #1
3 3253
I got it to work. Seems like I canīt user before triggers.

Thanks,

Michel Esber escreveu:
Hello

Environment: DB2 V8 LUW FP12.

I have a function that returns a table. I am trying to use it inside a
before trigger:
create trigger TRG.T_MACHINE_RTM
before insert on CAD.TBL_MACHINE
REFERENCING NEW as N
for EACH ROW
begin ATOMIC

insert into CAD.T_MACHINE_RTMDB select * from
TABLE(AFU.TABLE_DB_MID('x')) as A;

end@

When I run this I get the following error:

SQL0797N The trigger "TRG.T_MACHINE_RTM" is defined with an
unsupported
triggered SQL statement. LINE NUMBER=10. SQLSTATE=42987

I have also tried a variation: I converted the function to return a
VARCHAR, and changed my insert statement to use VALUES (x,y,
AFU.function). But I also got the same error.

Is there any way to make this work?

Thanks in advance,

Michel
Sep 1 '06 #2
Michel Esber wrote:
Hello

Environment: DB2 V8 LUW FP12.

I have a function that returns a table. I am trying to use it inside a
before trigger:
create trigger TRG.T_MACHINE_RTM
before insert on CAD.TBL_MACHINE
REFERENCING NEW as N
for EACH ROW
begin ATOMIC

insert into CAD.T_MACHINE_RTMDB select * from
TABLE(AFU.TABLE_DB_MID('x')) as A;

end@
The table function is fine. As long as that function is defined as CONTAINS
or READS SQL DATA (the default), you can use it in a before trigger. But
the problem is in the INSERT. The long explanation of the SQL0797 error
message says:

However, the triggered SQL statements in a BEFORE trigger
cannot include:

- a table function that modifies SQL data

- a nested DELETE, INSERT, MERGE, or UPDATE statement
Is there any way to make this work?
Use an AFTER INSERT trigger instead.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 1 '06 #3
Michel Esber wrote:
>
Hello

Environment: DB2 V8 LUW FP12.

I have a function that returns a table. I am trying to use it inside a
before trigger:
create trigger TRG.T_MACHINE_RTM
before insert on CAD.TBL_MACHINE
REFERENCING NEW as N
for EACH ROW
begin ATOMIC

insert into CAD.T_MACHINE_RTMDB select * from
TABLE(AFU.TABLE_DB_MID('x')) as A;

end@

The table function is fine. As long as that function is defined as CONTAINS
or READS SQL DATA (the default), you can use it in a before trigger. But
the problem is in the INSERT. The long explanation of the SQL0797 error
message says:

However, the triggered SQL statements in a BEFORE trigger
cannot include:

- a table function that modifies SQL data

- a nested DELETE, INSERT, MERGE, or UPDATE statement
Is there any way to make this work?

Use an AFTER INSERT trigger instead.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany


Thanks Knut. It is working fine now.

Sep 4 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Simon Holmes | last post: by
9 posts views Thread by Raquel | last post: by
3 posts views Thread by uninfmx | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.