473,573 Members | 3,030 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_R TM
before insert on CAD.TBL_MACHINE
REFERENCING NEW as N
for EACH ROW
begin ATOMIC

insert into CAD.T_MACHINE_R TMDB 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 3471
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_R TM
before insert on CAD.TBL_MACHINE
REFERENCING NEW as N
for EACH ROW
begin ATOMIC

insert into CAD.T_MACHINE_R TMDB 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_R TM
before insert on CAD.TBL_MACHINE
REFERENCING NEW as N
for EACH ROW
begin ATOMIC

insert into CAD.T_MACHINE_R TMDB 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_R TM
before insert on CAD.TBL_MACHINE
REFERENCING NEW as N
for EACH ROW
begin ATOMIC

insert into CAD.T_MACHINE_R TMDB 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
22189
by: DTB | last post by:
I am having trouble creating an INSTEAD OF trigger in SQL Server to replicate a BEFORE UPDATE trigger from ORACLE. Here is a sample of the ORACLE BEFORE UPDATE trigger: CREATE TRIGGER myTRIGGER ON MYTABLE begin :new.DT := SYSDATE; if :new.NM is NULL then :new.NM := USER; end if; end myTRIGGER;
3
2345
by: Andrew Mayo | last post by:
There is something very strange going on here. Tested with ADO 2.7 and MSDE/2000. At first, things look quite sensible. You have a simple SQL query, let's say select * from mytab where col1 = 1234 Now, let's write a simple VB program to do this query back to an MSDE/2000 database on our local machine. Effectively, we'll
1
4067
by: Simon Holmes | last post by:
Hi, I am having trouble calling a UDF from a 'before update' trigger whereas I have no problems calling it from the 'after update' trigger. The trigger is as below : CREATE TRIGGER foo NO CASCADE
9
3197
by: Raquel | last post by:
Following is a simple trigger definition: CREATE TRIGGER TRIG_EMPLOYEE_1 AFTER UPDATE OF SALARY ON DB2ADMIN.EMPLOYEE FOR EACH STATEMENT MODE DB2SQL CALL DB2ADMIN.SQLSP10( ) This gives an error: SQL0104N An unexpected token "CALL" was found following "ATEMENT MODE
3
8400
by: uninfmx | last post by:
Hi If one or mode records get deleted from t1 (see below), I'd like delete all the corresponding records from t2. There is no foreign key relationship between t2 and t1, so cascading delete is not an option. V8.2 AIX create table t1 ( x int
7
1572
by: Rhino | last post by:
I am updating some Java UDFs from DB2GENERAL to DB2JAVA as suggested in the manuals for DB2 Version 8 but I'm having problems with setSQLstate() and setSQLmessage(). If I'm reading the manuals correctly, they are only supported in UDFs that use DB2GENERAL. Is that right? If it is, is there any equivalent to these methods for DB2JAVA UDFs?...
2
6427
by: 73blazer | last post by:
Perhaps my thinking is wrong but this is what I have: 1 table (Tab1) with 1 attribute (Attr1) Attr1 char(16) for bit data ----------------------------------------------- create trigger check no cascade before insert on Tab1 referencing new as N
5
5343
by: wpellett | last post by:
I can not get the SQL compiler to rewrite my SQL UPDATE statement to include columns being SET in a Stored Procedure being called from a BEFORE UPDATE trigger. Example: create table schema1.emp ( fname varchar(15) not null, lname varchar(15) not null, dob date,
1
4028
by: gauravupreti | last post by:
Hi All, I have a table with a col. that accepts number. CREATE TABLE A (ID NUMBER NOT NULL) and another table with two columns as shown: CREATE TABLE B (ID NUMBER NOT NULL,
0
7783
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7701
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8029
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8068
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5600
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5293
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3735
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1307
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1044
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.