By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

Before Trigger and UDFs (table)

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.