Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Create table and columns from Trigger.

3 posts in General Discussion Last posting was on 2005-11-18 15:39:20.0Z
Leopoldo Taylhardat Posted on 2005-11-18 15:31:34.0Z
Sender: 471f.437df1c7.846930886@sybase.com
From: Leopoldo Taylhardat
Newsgroups: ianywhere.public.general
Subject: Create table and columns from Trigger.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <437df240.472e.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 18 Nov 2005 07:24:48 -0800, 10.22.241.42
Lines: 65
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 18 Nov 2005 07:24:49 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 18 Nov 2005 07:31:34 -0800
X-Trace: forums-1-dub 1132327894 10.22.108.75 (18 Nov 2005 07:31:34 -0800)
X-Original-Trace: 18 Nov 2005 07:31:34 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4968
Article PK: 17469

I need create a table from a trigger when the user insert
rows on other table.
If the table doesn't exist, and add a column to it from some
values from the row that fires the trigger.
How can I do it?
Example:
A new row in the table A with A.id_1, A.col_2, A.col2_value
and so on columns.
If the table named “table_id1” doesn't exists, I need
create the table.
After it,
Depending value of column id_2 I need verify if the column
exists.
… If the value of A.id_2 is 1 then the columns
“table_id1.col_2_1” and “table_id1.col_2_1Value”
(from col2_value ) must be created.
If the value of A.id_2 is 2 then the columns
“table_id1.col_2_2” and “table_id1.col_2_2Value”
(from col2_value ) must be created.

The meaning of it is create a "flat" table with columns
id_2, id_3, id_4 and so on... with the values from the rows
giving from table A.

I had tried with it:

The trigger ...

alter trigger dba.TIA_RESPUESTA before insert order 1
on DBA.RESPUESTAS
referencing new as nuevo
for each row
begin
declare @texto varchar(64);
declare @Nombre varchar(64);
select TIPO_ENCUESTA.APOCOPE_ENCUESTA into @Nombre
from TIPO_ENCUESTA where
TIPO_ENCUESTA.ENCUESTA=nuevo.encuesta;
set @texto='ENCUESTA_'+@Nombre;
--
call sp_crear_tabla(@texto)
--
end

The Procedure:

alter procedure dba.sp_crear_tabla(in tablename varchar(64))
begin
execute immediate ' CREATE TABLE '
||tablename||' ( ENCUESTA integer NOT NULL, ANO_ENCUESTA
smallint NOT NULL, NUMERO_ENCUESTA integer NOT NULL,
PREGUNTA integer NOT NULL, SUB_PREGUNTA integer NOT NULL,
CONSECUTIVO_RESPUESTA integer NOT NULL DEFAULT 0,
CODIGO_NEGOCIO integer NULL, CODIGO_VIVIENDA integer NULL )'
end

Message from PowerBuilder Application:

SQLSTATE = 37000
[Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error
or access violation: COMMIT/ROLLBACK not allowed within
atomic operation


Best regards.


"Chris Keating(iAnywhere Solutions)" <NoSpamPlease_k_e_a_t_i_n_g Posted on 2005-11-18 15:39:20.0Z
From: "Chris Keating(iAnywhere Solutions)" <NoSpamPlease_k_e_a_t_i_n_g@i_A_n_y_w_h_e_r_e.com>
Organization: iAnywhere Solutions
User-Agent: Thunderbird 1.5 (Windows/20051025)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Create table and columns from Trigger.
References: <437df240.472e.1681692777@sybase.com>
In-Reply-To: <437df240.472e.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: keating-dm20.sybase.com
Message-ID: <437df405@forums-2-dub>
X-Original-Trace: 18 Nov 2005 07:32:21 -0800, keating-dm20.sybase.com
Lines: 27
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 18 Nov 2005 07:32:33 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 18 Nov 2005 07:39:20 -0800
X-Trace: forums-1-dub 1132328360 10.22.108.75 (18 Nov 2005 07:39:20 -0800)
X-Original-Trace: 18 Nov 2005 07:39:20 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4972
Article PK: 8624

A CREATE TABLE statement has an implicit commit. There cannot be a
commit/rollback within the context of a trigger.


--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

*****************************************************************************
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

*****************************************************************************

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use Case Express to report bugs http://case-express.sybase.com

*****************************************************************************


krisztian pinter Posted on 2005-11-18 15:36:16.0Z
Newsgroups: ianywhere.public.general
Subject: Re: Create table and columns from Trigger.
References: <437df240.472e.1681692777@sybase.com>
Message-ID: <ops0fz4honwwfehv@karwst_pint>
From: "krisztian pinter" <pinterkr@freemail.hu>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-2
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
User-Agent: Opera M2/7.54 (Win32, build 3869)
X-Original-NNTP-Posting-Host: b178.dummy.pool.eol.hu
X-Original-Trace: 18 Nov 2005 07:29:27 -0800, b178.dummy.pool.eol.hu
Lines: 9
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 18 Nov 2005 07:29:28 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 18 Nov 2005 07:36:16 -0800
X-Trace: forums-1-dub 1132328176 10.22.108.75 (18 Nov 2005 07:36:16 -0800)
X-Original-Trace: 18 Nov 2005 07:36:16 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4971
Article PK: 8625


On 18 Nov 2005 07:31:34 -0800, <Leopoldo Taylhardat> wrote:

> COMMIT/ROLLBACK not allowed within atomic operation

if you look up the help about create table or alter table, you
find a note about side effect: automatic commit.

put these together: you are not allowed to modify or create a
table from a trigger. you have to find some other solution.