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.

inserting text into another table in trigger

4 posts in General Discussion Last posting was on 2009-12-01 23:15:09.0Z
Jim R. Posted on 2009-12-01 19:31:14.0Z
From: "Jim R." <junk@hotmail.com>
Newsgroups: sybase.public.ase.general
Subject: inserting text into another table in trigger
Lines: 29
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b156f02$1@forums-1-dub>
Date: 1 Dec 2009 11:31:14 -0800
X-Trace: forums-1-dub 1259695874 10.22.241.152 (1 Dec 2009 11:31:14 -0800)
X-Original-Trace: 1 Dec 2009 11:31:14 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28720
Article PK: 77962

I have an instead-of-trigger on a view that has a text column. I need to
insert the text value into another table. If I reference inserted.<col> in
the INSERT INTO I get "the name 'empdoc' is illegal in this context. Only
constants, constant expressions, or variables allowed here. Column names are
illegal." Triggers don't support text type variables so I can't assign
inserted.empdoc to a variable and use the variable in an insert.

Is there a way for me to take the incoming text column and insert the value
into another table?


create trigger employeefile_iot
on employeefile
instead of insert
as

BEGIN

insert into employeedocstemp values (@@spid, inserted.empdoc)

... more stuff

END


Thanks
Jim


Neal Stack [Sybase] Posted on 2009-12-01 21:11:46.0Z
From: "Neal Stack [Sybase]" <nstack@nospam.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: inserting text into another table in trigger
References: <4b156f02$1@forums-1-dub>
In-Reply-To: <4b156f02$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b158692@forums-1-dub>
Date: 1 Dec 2009 13:11:46 -0800
X-Trace: forums-1-dub 1259701906 10.22.241.152 (1 Dec 2009 13:11:46 -0800)
X-Original-Trace: 1 Dec 2009 13:11:46 -0800, vip152.sybase.com
Lines: 47
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28722
Article PK: 77963

Hello,

The following works for me on ASE 15.0.3 ESD#1.

In ASE1:
========
create table text_test (id integer, ts timestamp, tf text NULL)
go

create unique index TEXT_TEST_UNIQUE_IDX on text_test (id)
go

In ASE2:
=========
create proxy_table TEXT_TEST_PROXY at "ase1.pubs2.dbo.text_test"
go

create view TEXT_TEST_VIEW
as select
id,
ts,
tf
from TEXT_TEST_PROXY
go


create trigger TEXT_TEST_INS_IT
on TEXT_TEST_VIEW
instead of insert
as
BEGIN
insert into TEXT_TEST_PROXY select
id,
null,
tf
from inserted
END --trigger

go

-- the following statement works:
insert into TEXT_TEST_VIEW (id,tf) values (4,'view insert')
go


Regards,
Neal


Jim R. Posted on 2009-12-01 23:15:09.0Z
From: "Jim R." <junk@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <4b156f02$1@forums-1-dub> <4b158692@forums-1-dub>
Subject: Re: inserting text into another table in trigger
Lines: 59
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b15a37d$1@forums-1-dub>
Date: 1 Dec 2009 15:15:09 -0800
X-Trace: forums-1-dub 1259709309 10.22.241.152 (1 Dec 2009 15:15:09 -0800)
X-Original-Trace: 1 Dec 2009 15:15:09 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28724
Article PK: 77966

ah, I had the syntax wrong. I was trying to reference "inserted" like a
variable rather than selecting the value. doh!

Thanks
Jim

"Neal Stack [Sybase]" <nstack@nospam.com> wrote in message
news:4b158692@forums-1-dub...
> Hello,
>
> The following works for me on ASE 15.0.3 ESD#1.
>
> In ASE1:
> ========
> create table text_test (id integer, ts timestamp, tf text NULL)
> go
>
> create unique index TEXT_TEST_UNIQUE_IDX on text_test (id)
> go
>
> In ASE2:
> =========
> create proxy_table TEXT_TEST_PROXY at "ase1.pubs2.dbo.text_test"
> go
>
> create view TEXT_TEST_VIEW
> as select
> id,
> ts,
> tf
> from TEXT_TEST_PROXY
> go
>
>
> create trigger TEXT_TEST_INS_IT
> on TEXT_TEST_VIEW
> instead of insert
> as
> BEGIN
> insert into TEXT_TEST_PROXY select
> id,
> null,
> tf
> from inserted
> END --trigger
>
> go
>
> -- the following statement works:
> insert into TEXT_TEST_VIEW (id,tf) values (4,'view insert')
> go
>
>
> Regards,
> Neal


Bret Halford Posted on 2009-12-01 22:41:33.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: inserting text into another table in trigger
References: <4b156f02$1@forums-1-dub>
In-Reply-To: <4b156f02$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b159b9d$1@forums-1-dub>
Date: 1 Dec 2009 14:41:33 -0800
X-Trace: forums-1-dub 1259707293 10.22.241.152 (1 Dec 2009 14:41:33 -0800)
X-Original-Trace: 1 Dec 2009 14:41:33 -0800, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28723
Article PK: 77965


Jim R. wrote:
> I have an instead-of-trigger on a view that has a text column. I need to
> insert the text value into another table. If I reference inserted.<col> in
> the INSERT INTO I get "the name 'empdoc' is illegal in this context. Only
> constants, constant expressions, or variables allowed here. Column names are
> illegal." Triggers don't support text type variables so I can't assign
> inserted.empdoc to a variable and use the variable in an insert.
>
> Is there a way for me to take the incoming text column and insert the value
> into another table?
>
>
> create trigger employeefile_iot
> on employeefile
> instead of insert
> as
>
> BEGIN
>
> insert into employeedocstemp values (@@spid, inserted.empdoc)
>
> ... more stuff
>
> END
>
>
> Thanks
> Jim
>
>

try

insert into employeedocstemp select @@spid, i.empdoc from inserted i


But note that although it actually currently works using plain SQL, it
does fail when using Java and the documentation explicitly says that you
cannot select text or image values from the inserted or deleted
tables, so it probably isn't a good idea to rely on the behavior working.

Feature request CR 593730 is open for formal support for selecting text
and image from inserted and deleted.