| pre_insert [message #685705] |
Thu, 10 March 2022 02:51  |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
Quote:
Table name: test
Fields
tid number;
tdate date;
composite primary key(tid,tdate);
Pre-insert Trigger code:
select nvl(max(tid),0)+1 into :test.tid from test;
|
|
|
|
| Re: pre_insert [message #685714 is a reply to message #685705] |
Thu, 10 March 2022 14:59  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
If table is empty, code you wrote will return 1 as a result:
SQL> select * from test;
no rows selected
SQL> select nvl(max(id), 0) + 1 from test;
NVL(MAX(ID),0)+1
----------------
1
SQL>
Anyway, what you're doing - you're doing it wrong. It'll work in a single user environment, but not when many users use the same form at the same time. Sooner or later, two of them will fetch the same MAX + 1 value, and that's probably not what you wanted.
Consider using a sequence instead, either in a form or in a BEFORE INSERT database trigger. As you're on 10g, you can't use an identity column.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
