/scripts/ahub.form.attachments.js
0

Table variables don't work in triggers - why not ? (ASE 16)

Oct 14, 2016 at 03:08 PM

198

avatar image
Former Member

Table variables don't work in ASE triggers. (16.0 SP02 PL04 HF1/EBF 26462 SMP)

create table test ( i int )

create trigger testtrig on test for update as

begin

declare @x table ( Factor varchar(10) )

insert into @x (Factor) values ( "T1" )

end

insert into test values ( 100 )

Now run this...

update test set i = i

select "HERE"

No error message, error status is zero.

Will raise this as a bug.

10 |10000 characters needed characters left characters exceeded

Hi Mike,

This appears to be working here.

As for the 287 error, this requires "functionality group" or specifically "enable select into in tran" to be enable create table in a trigger.
More on that here:

http://help.sap.com/saphelp_ase1601/helpdata/en/47/dfe1c1d8f44225bb81757e511db134/frameset.htm

Here's the successful test

1> select @@version
2> go
 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Adaptive Server Enterprise/16.0 SP02 PL04 HF1/EBF 26462 SMP/P/x86_64/Enterprise
          Linux/ase160sp02pl04x/2602/64-bit/FBO/Tue Aug 23 01:58:56 2016     
                      
1> create table test(a int)
2> go
1> create trigger tesstrig on test for update as
2> begin
3> declare @x table (Factor varchar(20))
4> insert @x select "Hello from Trigger"
5> select * from @x
6> end
7> go
1> insert test select 1
2> go
(1 row affected)
1> update test set a = 2
2> go
 Factor
 ----------------------------------------
 Hello from Trigger


(1 row affected)

Dan

0
Former Member
Daniel Thrall

yes - that bit works but not what I was showing -

Using your example

Try this :-

         update test set a = 2
select "DONE"

I get

        Factor 
--------------------
Hello from Trigger

I should see the "DONE" - but I don't

(I'm trying to log this as a fault with SAP support.)

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Daniel Thrall
Oct 20, 2016 at 05:34 PM
1

Thanks Mike for the clarification. I've logged CR 802905 for this
If a trigger declares and updates a local table variable, commands in the same batch that are after the DML used to cause the trigger to fire will fail silently.

Thanks

Dan

A couple other examples for completeness

1> create proc uproc as
2> update test set a = 1
3> select "DONE"
4> go
1> uproc
2> go
 Factor
 ----------------------------------------
 Hello from Trigger

(1 row affected)
(return status = 0)

1> update test set a = 1
2> update test set a = 2
3> go
 Factor
 ----------------------------------------
 Hello from Trigger

(1 row affected)
1> select a from test
2> go
 a
 -----------
           1

(1 row affected)

Share
10 |10000 characters needed characters left characters exceeded
Mark A Parsons Oct 14, 2016 at 03:18 PM
0

When I try to run your code snippet against ASE 16.0 SP02 PL03 I get an error during the creation of the trigger:

==================
Msg 287, Level 16, State 1:
Server 'ASE201', Procedure 'testtrig', Line 3:
CREATE TABLE command not allowed within a trigger.
==================

Did your 'create trigger' succeed? [I'm assuming it failed with 'Msg 287'; so the follow-on insert didn't have a trigger to fire, ie, no error ... ?]

*arg* I hate this brain-dead, liimited editing capability!

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

The create trigger worked...

SP02 PL03 is just so last month - I guess the ability to create temp tables must have been added in PL04 HF1

I've converted the code to this

create trigger .....

as

create table #x ( Factor varchar(10) )

insert into #x values ("T1"

And it all works ok,

Looking at other bugs around table variables ... I suspect its related to inserting into @table causing a commit.

0
Skip to Content