Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unanswered: Global Temp tables in Sybase ASE..

    Hello friends,
    I am working on a small program to identify data changes in a particular table. As a part of this, it is also expected that I identify "transaction boundaries" of these operations.

    I decided to use triggers for tracking data changes which would make an insert into the table defined below :

    Code:
    TxnEvent
    --------
    id      integer
    event   varchar (insert/update/delete)
    primary_id  integer (the primary key value of the row being updated)
    I was using the iAnywhere db for my testing and found a nice table type called the global temp table which is txn based. Basically, all data entered into such table would be cleared when the txn commits or rolls back which is exactly what I wanted. So I created such a table with just a single integer column.

    Code:
    CREATE GLOBAL TEMPORARY TABLE "DBA"."TxnIdHolder" (
    	"txn_id" integer NOT NULL DEFAULT autoincrement,
    	PRIMARY KEY ( "txn_id" )
    ) ON COMMIT DELETE ROWS;
    
    
    TxnIdHolder
    -----------
    txn_id  integer
    and modified the TxnEvent table to include the txn_id :

    Code:
    TxnEvent
    --------
    id      integer
    event   varchar (insert/update/delete)
    primary_id  integer (the primary key value of the row being updated)
    txn_id  integer (value from the TxnIdHolder table)
    Thus it becomes simple to find out the operations that happened in a txn.

    Now, I am trying out the same thing in the Sybase ASE DB (v 12.5.3) and it does not work ! :

    Code:
    1> CREATE GLOBAL TEMPORARY TABLE "DBA"."TxnIdHolder" ("txn_id" integer NOT NULL DEFAULT autoincrement,PRIMARY KEY ( "txn_id" )) ON COMMIT DELETE ROWS
    2> go
    Msg 155, Level 15, State 2:
    Server 'Designer_ASE', Line 1:
    'GLOBAL' is not a recognized CREATE option.
    Is this feature not supported in the ASE version at all ? Or is the syntax different ?

    Pl help me out. Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    UK
    Posts
    43
    In sybase ASE whenever any trigger fire on a table, two tables gets created automatically called "Inserted" and "Deleted".

    If suppose there is an insert trigger on a table and any insert happened then the new record will insert into tables called "Inserted" as well.

    If suppose there is a update/delete trigger on a table and any update/delete happened then the new record will insert into tables called "Inserted" and old will be in "Deleted" table.
    These tables has same schema like the table on which the trigger has fired.

    Hope this will solve ur problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •