I am using DB2 V8.1 Express Edition


Is it possible to execute an sql statement using EXEC SQL or EXEC IMMEDIAT
Then can I do this instead:

CREATE TRIGGER DB2INST1.TEST AFTER UPDATE OF totoId
ON DB2INST1.TOTO REFERENCING OLD AS od NEW AS nw OLD_TABLE AS OLD NEW_TABLE AS NEW
FOR EACH ROW MODE DB2SQL
WHEN ( od.totoId <> nw.totoId )
BEGIN ATOMIC
DECLARE query VARCHAR(256);
SET query = 'update toto set (totoId)=(3) where totoId=1'
EXEC SQL :query;
END@

I got the following error still:
[db2inst1@hs204 SCRIPTS]$ db2 -td@ -vf schema_test_trigger.db2
CREATE TRIGGER DB2INST1.TEST AFTER UPDATE OF totoId ON DB2INST1.TOTO REFERENCING OLD AS od NEW AS nw OLD_TABLE AS OLD NEW_TABLE AS NEW FOR EACH ROW MODE DB2SQL WHEN ( od.totoId <> nw.totoId ) BEGIN ATOMIC DECLARE query VARCHAR(256); SET query = 'update toto set (totoId)=(3) where totoId=1'; EXEC SQL :query; END
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "SQL" was found following "here totoId=1';
EXEC". Expected tokens may include: "JOIN <joined_table>". LINE NUMBER=1.
SQLSTATE=42601

There is no EXEC command in SQL. To execute Dynamic SQL, you use
PREPARE and EXECUTE. But I do not think you need dynamic in this instance anyway. What happens if you try:

CREATE TRIGGER DB2INST1.TEST AFTER UPDATE OF totoId
ON DB2INST1.TOTO REFERENCING OLD AS od NEW AS nw OLD_TABLE AS OLD NEW_TABLE AS NEW
FOR EACH ROW MODE DB2SQL
WHEN ( od.totoId <> nw.totoId )
BEGIN ATOMIC
update toto set totoId=3 where totoId=1;
END@

Andy

Thanks Andy for the reply,

I just that in DB2 book that I have that one of the restrictions when developping SQL triggers

- PREPARE, EXECUTE and EXECUTE IMMEDIATE statements are not allowed in triggers and UDFs. Trigger and UDF SQL is inherently dynamic.
- Stored procedure calls from triggers and UDFs are not currently supported.

So I don't know what's my solution now. If I use your code it will work but it is not what i want to do.

I would like to construct the query string dynamicly then execute the statement.
DECLARE query CHAR;
SET query='column1,colomn2,colomn5';

update toto set (query) = (value1,value2,value5);

And that is not working

Can you explain what you want you are trying to accomplish?

Andy

I would like to construct the query string dynamiquelly then execute the statement, for example:

DECLARE query CHAR;
SET query='colomn=1';

update toto set query where totoId=1;

My query string here could contain two or three or more colomn o update

I could have also:

SET query='colomn=1,colomn2=0';

update toto set query where totoId=1;


Is it ok ??

You cannot do it dynamically. Please explain, in english, what you want the trigger to do?

Andy

A suggestion would be to build a function that can be used to build your SQL statement and execute it. Functions can be called within triggers (stored procedures cannot if using UDB linux/unix/windows).