avatar image
Former Member

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

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.

Add comment
10|10000 characters needed 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

  • 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.)

  • Get RSS Feed

2 Answers

  • Oct 20, 2016 at 05:34 PM

    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)
    
    
    Add comment
    10|10000 characters needed characters exceeded

  • Oct 14, 2016 at 03:18 PM

    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!

    Add comment
    10|10000 characters needed 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.

Skip to Content