• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2282
  • Last Modified:

Oracle2Sybase: sequence and systime equivs?

I'm new to Sybase, but very familiar with Oracle. What is the Sybase equivalent of the following:

1) I want to write a trigger that updates/inserts a datetime field in a table with the current date. In Oracle I'd use systime from dual. I know that there's a "getdate()" function in Sybase, but what's the syntax for using in a INSERT/UPDATE statement?

2) Maintaining an ID in a table. I use sequences in Oracle to keep the counter going. What's the best way in Sybase to get the next ID (primary key) value in a table for an INSERT statements?

1 Solution
1. The syntax for creating trigger  is
   create trigger [owner.] trigger_name on [owner,] table_name
   for {insert,update,delete}
   as SQL Statements

or using the if update clause :

 create trigger [owner.] trigger_name on [owner,] table_name
   for {insert,update,delete}
if update (column_name)
     {{and| or) update(column_name)]...]..
  SQL Statements

The SQL staements are same as Oracle PL/SQL. I believe you know the insert and update statements the same way as oracle. Here you need to check against the trigger tested tables  i,e inserted. And with satisfied condition you can insert the date filed as getdate() or set the date field to getdate() in case of update. You can refer Transact-SQL User's guid for Sybase SQL Server for examples. Good examples there how to write triggers. If you still have questions , you can write comments , so that I can write in detail.

2. ID can be generated or maintained by using column data type 'Identity' in the table. Unfortunately Sybase does not maintain sequence like Oracle.
The maintenance to data on  Identity column is not easy all the time. You can even use general programming technique like maintaining a look up table with one column and incremenated value can be generated to use in the regular table. The choice depends on you.

Hope this will work for you....


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now