Before Trigger and UDFs (table)

[Michel Esber]

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

[Michel Esber]

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

[Knut Stolze]

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

[Michel Esber]

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.