PRE-INSERT [message #292677] |
Wed, 09 January 2008 10:34 |
mclarensr
Messages: 5 Registered: January 2008
|
Junior Member |
|
|
Is there a way for me to write a pre-insert trigger on the table by passing a variable from a Form or from a web application or from a VB script.
Here is what I want to do. I want to get the next number in sequence based on the 'type' field (this is a column in the table). For example if the type = 1, I want to insert in the table, the next number that starts with 1XXXXXX. If the type = 2, I want to insert in the table, the next number that starts with 2XXXXXX.
I currently have the pre-nsert trigger that is on the oracle forms which works perfectly, but I want to be able to move it to the table so I can use the same logic for 'in-house' data entry, 'on-line' data entry (done by ASP.net) and 'Off-site' data entry (VB and VB script)
Please let me know if this is possible. Thanks for your help.
|
|
|
|
Re: PRE-INSERT [message #292730 is a reply to message #292677] |
Wed, 09 January 2008 14:45 |
mclarensr
Messages: 5 Registered: January 2008
|
Junior Member |
|
|
Thanks for the response littlefoot. I will try this.
Is there a way to do it using max+1? This column is not the primary key, but is a unique value in the table. This is what I have but does not seem to work.
CREATE OR REPLACE TRIGGER RBI_document
BEFORE INSERT ON DOCUMENT
FOR EACH ROW
BEGIN
if :new.l_type = 1 then
select max(doc_id)+1
into :new.doc_id
from document
where l_type = 1;
elsif :new.l_type = 2 then
select max(doc_id)+1
into :new.doc_id
from document
where l_type = 2;
end if;
end;
The doc_id is 7 digits and starts with 1 for type = 1 and starts with 2 for type = 2.
Thanks for your response.
[Updated on: Wed, 09 January 2008 20:42] by Moderator Report message to a moderator
|
|
|
|
Re: PRE-INSERT [message #292967 is a reply to message #292889] |
Thu, 10 January 2008 05:48 |
mclarensr
Messages: 5 Registered: January 2008
|
Junior Member |
|
|
Sorry for not being more specific. Everything compiles fine, what seems to be happenning is that it is always taking l_type = 2 even though my form has 1. I will attach a screen show when I go back to the office tomorrow.
I will also try the sequence and see what happens.
Thanks.
[Updated on: Thu, 10 January 2008 05:49] Report message to a moderator
|
|
|
Re: PRE-INSERT [message #293105 is a reply to message #292967] |
Thu, 10 January 2008 22:22 |
mclarensr
Messages: 5 Registered: January 2008
|
Junior Member |
|
|
Thanks for all the help. I finally had to change a several other modules and was able to get the results I wanted. I guess it was all worth it in the end, because this is better than using max+1. Here is what I have.
CREATE OR REPLACE TRIGGER "GDOCUMENT_RBI"
BEFORE INSERT ON GDOCUMENT
FOR EACH ROW
BEGIN
:NEW.last_update := TRUNC(sysdate, 'DD');
:NEW.last_update_name := USER;
IF :NEW.L_TYPE = 1 THEN
SELECT seq_r.NEXTVAL INTO :NEW.DOC# FROM dual;
ELSIF :NEW.L_TYPE = 2 THEN
SELECT seq_p.NEXTVAL INTO :NEW.DOC# FROM dual;
END IF;
END;
[EDITED by LF: added [code] tags]
[Updated on: Fri, 11 January 2008 00:51] by Moderator Report message to a moderator
|
|
|
|
Re: PRE-INSERT [message #293260 is a reply to message #293157] |
Fri, 11 January 2008 08:27 |
mclarensr
Messages: 5 Registered: January 2008
|
Junior Member |
|
|
The l_type will always be 1 or 2 the user has to choose a radio button on the form. But just to be on the safe I included this line for any unsupported types.
raise_application_error(-20001,'Unsupported type '||:new.l_type);
|
|
|