Home » Developer & Programmer » Forms » PRE-INSERT
PRE-INSERT [message #292677] Wed, 09 January 2008 10:34 Go to next message
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 #292696 is a reply to message #292677] Wed, 09 January 2008 12:12 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, it is possible. Write a database trigger (BEFORE INSERT) which would do the job that PRE-INSERT does now. Something like this:
SQL> CREATE TABLE TEST (TYPE NUMBER, seq_num NUMBER);

Table created.

SQL> CREATE SEQUENCE seq_1 START WITH 100000;

Sequence created.

SQL> CREATE SEQUENCE seq_2 START WITH 200000;

Sequence created.

SQL>
SQL> CREATE OR REPLACE TRIGGER bi_test
  2    BEFORE INSERT ON TEST
  3    FOR EACH ROW
  4  BEGIN
  5    IF :NEW.TYPE = 1 THEN
  6       SELECT seq_1.NEXTVAL INTO :NEW.seq_num FROM dual;
  7    ELSIF :NEW.TYPE = 2 THEN
  8       SELECT seq_2.NEXTVAL INTO :NEW.seq_num FROM dual;
  9    END IF;
 10  END;
 11  /

Trigger created.

SQL> insert into test (type) values (1);

1 row created.

SQL> insert into test (type) values (1);

1 row created.

SQL> insert into test (type) values (2);

1 row created.

SQL> select * from test;

      TYPE    SEQ_NUM
---------- ----------
         1     100000
         1     100001
         2     200000

SQL>
Re: PRE-INSERT [message #292730 is a reply to message #292677] Wed, 09 January 2008 14:45 Go to previous messageGo to next message
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 #292889 is a reply to message #292730] Thu, 10 January 2008 02:14 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

This is what I have but does not seem to work.
Well, this is what you've said, but it doesn't help at all. What does it mean "it does not seem to work"? Nobody here sees your screen so all we know is what you tell us. Is there an error? Did the trigger not compile? Something else?

A blind guess: if a table is empty, change
MAX(doc_id) + 1
to
NVL(MAX(doc_id), 0) + 1


However, I'd suggest you NOT to use "MAX + 1" principle. As long as it works in a single-user environment, it will (sooner or later) create a mess in a multi-user environment.
Re: PRE-INSERT [message #292967 is a reply to message #292889] Thu, 10 January 2008 05:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #293157 is a reply to message #293105] Fri, 11 January 2008 00:56 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm glad you made it work!

However: what will you do if "l_type" is different from 1 or 2? Will it ALWAYS be one of these values? You can make it happen if you create a CHECK constraint on this column.

Also, not that it matters much, but - TRUNC(SYSDATE, 'DD') is equal to TRUNC(SYSDATE) (just in case you want to simplify the code).
Re: PRE-INSERT [message #293260 is a reply to message #293157] Fri, 11 January 2008 08:27 Go to previous message
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);


Previous Topic: How to lock the source code for the forms triggers code
Next Topic: calling a form from another form
Goto Forum:
  


Current Time: Sun Nov 03 01:50:14 CDT 2024