|
|
|
|
|
|
|
|
| Re: generate s.no triger [message #441446 is a reply to message #441435] |
Mon, 01 February 2010 04:07   |
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
|
Junior Member |
|
|
i am using this triger syntax
begin
select (nvl(max(id),0)+1)
into :N_SORD_detail.id
from N_SORD_detail
where :N_SORD.id = :N_SORD_DETAIL.id
exception when others then
message(sqlerrm);
end;
but when i compile this trigger showing error message
"encountered the symbol "exception" when expecting one of the following.
|
|
|
|
|
|
| Re: generate s.no triger [message #441463 is a reply to message #441448] |
Mon, 01 February 2010 04:50 |
Supernova11
Messages: 12
Registered: December 2008
Location: Indonesia
|
Junior Member |
|
|
you should create sequence for your id column.
CREATE SEQUENCE ID_SEQ
START WITH 0
INCREMENT BY 1
MINVALUE 0
NOCACHE
NOCYCLE
NOORDER
after that in your trigger
CREATE OR REPLACE TRIGGER ID_VAL
BEFORE INSERT
ON TABLE_NAME
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
v_id integer;
BEGIN
select id_seq.nextval into v_id from dual;
:new.id := v_id;
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END;
|
|
|
|
| Re: generate s.no triger [message #441470 is a reply to message #441435] |
Mon, 01 February 2010 04:58 |
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
|
Junior Member |
|
|
i solved a problem with out creating a squense.
i am using pre_insert triger.
trigger syntax:
begin
select (nvl(max(id),0)+1)
into :N_SORD_detail.id
from N_SORD_detail
where SO_ID = :N_SORD_DETAIL.SO_ID;
exception when others then
message(sqlerrm);
end;
i am also attach a image.
|
|
|
|
| Re: generate s.no triger [message #441473 is a reply to message #441435] |
Mon, 01 February 2010 05:05 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
@Supernova11 - you realise the OP is talking about forms triggers don't you?
@talk4ever - the trouble with your solution is that if two different users run that form at the same time they'll get the same value for :N_SORD_detail.id and then get an ORA-00001 error. Using a sequence would avoid this, which is why everyone is telling you to use one.
|
|
|
|
|
|
|
|
| Auto Generate [message #441592 is a reply to message #441435] |
Tue, 02 February 2010 01:24 |
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
|
Junior Member |
|
|
I want to Auto generate "order_id" No with sysdate and sequence through pre-insert trigger.
for example:
020220101
020220102
020220103
020220104
if system date change
030220101
030220102
030220103
030220104
anyone can help me.
|
|
|
|
|
|
|
|
|
|
| Re: Auto Generate [message #441606 is a reply to message #441592] |
Tue, 02 February 2010 02:23 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Glad to hear it. Now you should probably have have a read of the forum guidelines - especially the bit about formatting code. Using code tags makes your code easier to read and avoids the possibility of smilies appearing in it:
SELECT TO_CHAR(SYSDAtE, 'DDMMRRRR') || NVL(MAX(TO_NUMBER(SUBSTR(ID, 9))), 0) + 1 INTO :N_SORD.id
FROM N_sord
WHERE SUBSTR(ID, 1, 8) = TO_CHAR(SYSDate, 'DDMMRRRR');
So please use them from now on.
|
|
|
|
| Need Trigger Syntax [message #441823 is a reply to message #441435] |
Wed, 03 February 2010 04:57 |
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
|
Junior Member |
|
|
I want to auto generate invoice No. (inv_ID) user manualy punch date in date field (DATE).
for example
when user input date in date field 11-01-2010 and commit the transuction Auto generate (inv_ID) in this format (RRRRMMdd0001).
201001110001
201001110002
201001110003
if user input date in date field 15-11-2009
200911150004
200911150005
200911150006
Advance Thanks for yor cooperation
talk4ever
|
|
|
|
|
|
| Re: How to generate a serial number? [message #441834 is a reply to message #441828] |
Wed, 03 February 2010 05:35 |
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
|
Junior Member |
|
|
i solved a problem
here is a trigger syntax
select to_number(to_char(:DATE, 'YYYYMMDD')) || Lpad (nvl(max(substr(inv_ID,9,12))o) + 1,4,'0')
into :N_pord.inv_ID
from N_pord
where where inv_ID = :N_PORD_DETAIL.PO_ID;
@cookiemonster it's another form & and another task
you can try this syntax and my last trigger syntax.
both triger are generate different result.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
= TO_CHAR(SYSDate, 'DDMMRRRR');