Dear all, I've got table trans_comp with column name 'day1' to 'day31' and batch_no as primary key , I do this in powerscript and It works well

ls_colday = '4'
li_tothour = 10
ls_sql = 'update trans_comp set day' + string(ls_colday) + ' = ' + 
string(li_tothour) + " where batch_no = '" +  a_batchno  + "';"
    execute immediate: ls_sql;

The problem is I want to write it down as a trigger in SQLAnywhere10 DB, is there any way to do this? How to write this syntax into a sql statement ? Thanks for all your help

asked 15 Jan '11, 10:51

ekologi's gravatar image

ekologi
11111
accept rate: 0%

edited 15 Mar '13, 18:40

Mark%20Culp's gravatar image

Mark Culp
22.6k9129265


Please explain why you need a trigger.

If you are asking "How do I do EXECUTE IMMEDIATE in SQL Anywhere 10?" here is a demonstration using a stored procedure:

CREATE TABLE trans_comp (
   batch_no INTEGER NOT NULL PRIMARY KEY,
   day1     INTEGER NOT NULL DEFAULT 0,
   day2     INTEGER NOT NULL DEFAULT 0,
   day3     INTEGER NOT NULL DEFAULT 0,
   day4     INTEGER NOT NULL DEFAULT 0,
   day5     INTEGER NOT NULL DEFAULT 0,
   day6     INTEGER NOT NULL DEFAULT 0,
   day7     INTEGER NOT NULL DEFAULT 0 );

INSERT trans_comp ( batch_no ) VALUES ( 1 );
INSERT trans_comp ( batch_no ) VALUES ( 2 );
INSERT trans_comp ( batch_no ) VALUES ( 3 );
COMMIT;

CREATE PROCEDURE update_day ( 
   IN @batch_no  INTEGER,
   IN @colday    VARCHAR ( 2 ),
   IN @tothour   INTEGER )
BEGIN
   DECLARE @sql LONG VARCHAR;
   SET @sql = STRING (
      'UPDATE trans_comp SET day',
      @colday,
      ' = ',
      @tothour,
      ' WHERE batch_no = ',
      @batch_no );
   EXECUTE IMMEDIATE @sql;
   COMMIT;
END;

CALL update_day ( 2, '4', 10 );
SELECT * FROM trans_comp ORDER BY batch_no;

batch_no,day1,day2,day3,day4,day5,day6,day7
1,0,0,0,0,0,0,0
2,0,0,0,10,0,0,0
3,0,0,0,0,0,0,0
permanent link

answered 15 Jan '11, 16:11

Breck%20Carter's gravatar image

Breck Carter
27.0k424582830
accept rate: 21%

Thanks alot, this is the answer that I'm looking for.

(16 Jan '11, 13:05) ekologi

can we use EXECUTE IMMEDIATE in triggers ?

(16 Jan '11, 17:36) ekologi
Comment Text Removed

Yes. A test I ran back in July showed a problem in Adaptive Server Anywhere Database Engine Version 9.0.2.3575 Correlaton name 'new_name' not found SQLCODE=-142, ODBC 3 State="42S02". Further tests in 10.0.1.3415, 11.0.1.2276 and 12.0.0.2589 did not have that problem. I do not know about earlier versions, but it should work.

(18 Jan '11, 13:01) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×59
×9
×4
×2

question asked: 15 Jan '11, 10:51

question was seen: 1,206 times

last updated: 15 Mar '13, 18:40