| Sequence number update [message #53465] |
Mon, 23 September 2002 05:37  |
Jenny
Messages: 7
Registered: November 2001
|
Junior Member |
|
|
I have an ID field that I want to automatically update. Can't remember the syntax - can anyone help?
Many thanks
|
|
|
|
| Re: Sequence number update [message #53485 is a reply to message #53465] |
Mon, 23 September 2002 13:56  |
Trifon Anguelov
Messages: 514
Registered: June 2002
|
Senior Member |
|
|
1. Create a sequence:
CREATE SEQUENCE user.test_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 20
NOORDER
/
2. In your code include:
INSERT into table VALUES (test_seq.netxval, ..., ..., ...);
COMMIT;
3. Or create before insert trigger to do that for you:
CREATE OR REPLACE TRIGGER user.trigger_insert
BEFORE INSERT
ON user.table
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (new.type = 1)
declare
....
begin
..
..
..
end
/
Then use :
INSERT into table VALUES (NULL, ..., ..., ...);
COMMIT;
Hope that helps,
clio_usa
OCP - DBA
Visit our Web site
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
