Re: sql syntax help

From: M. Armaghan Saqib <armaghan_at_yahoo.com>
Date: 2000/03/20
Message-ID: <8b5ktc$ior$1@nnrp1.deja.com>#1/1

You could also use a trigger on database server to do this job. This trigger
also locks the row so that no one else updates which your trans is still not
committed.

SQL> EXEC S2.BLDSEQ('SEQ_DEPTNO', 'TABLE', 'DEPT', 'DEPTNO')
| ----------------------------------------------------------------------

---

-
| CREATE TABLE seq_table(
| seq_name VARCHAR2(30) PRIMARY KEY,
| next_value NUMBER,
| inc_by NUMBER);
|
| INSERT INTO seq_table VALUES('SEQ_DEPTNO', 1, 1);
|
| CREATE OR REPLACE TRIGGER DEPT_ins_trg1
| BEFORE INSERT
| ON DEPT
| FOR EACH ROW
| BEGIN
| SELECT next_value
| INTO :NEW.DEPTNO
| FROM seq_table
| WHERE seq_name='SEQ_DEPTNO' FOR UPDATE;
| UPDATE seq_table
| SET next_value = next_value + inc_by
| WHERE seq_name='SEQ_DEPTNO';
| END;
| /

regards,
M. Armaghan Saqib

+---------------------------------------------------------------


| 1. SQL PlusPlus => Add power to SQL Plus command line

| 2. SQL Link for XL => Integrate Oracle with XL
| 3. Oracle CBT with sample GL Accounting System
| Download free: http://www.geocities.com/armaghan/

 +---------------------------------------------------------------

| SQLPlusPlus now on http://www.ioug.org/
| "PL/SQL package that extends SQL*Plus to another dimension.
| Contains a PL/SQL code generator and set of extremely useful
| utilites with extensive documentation." IOUG Web Site

+---------------------------------------------------------------

Ashwath Kakhandiki <ashwath_at_bluedog.com> wrote in message news:sd5e13gtfja96_at_corp.supernews.com...

> Hi everyone,
>
> I have two SQL query statements like this:
>
> SELECT ID FROM ID_TABLE
>
> set variable id = ID in Java code
>
> UPDATE ID_TABLE SET ID= "+ (id+1) + " WHERE ID=" + (id);
>
> Is there a safe way to do this so that it will succeed
>
> for all callers? It is supposed to return a unique,
>
> increasing number every time it's called.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Mar 20 2000 - 00:00:00 CST