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
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
answered 15 Jan '11, 16:11