Home » SQL & PL/SQL » SQL & PL/SQL » INSERT UPDATE trigger HELP
icon10.gif  INSERT UPDATE trigger HELP [message #225505] Tue, 20 March 2007 03:22 Go to next message
bad boy killa
Messages: 1
Registered: November 2006
Junior Member


table A

LOG_NO (primary key)
SERIAL_NO
TYPE
MODEL
LOG_DATE

table B

SERIAL_NO (primary key)
TYPE
MODEL

Problem 1:
i need a trigger for UPDATE and INSERT on table A, so that it would update table B auomatically. However note that although table A could have duplicate SERIAL_NO, table B must have one entry only for each unique SERIAL_NO. (note: in table A for duplicate SERIAL_NO the TYPE and MODEL would be the same)

Problem 2:
the data of table A are entered through a form. I need an automatic sequence for the LOG_NO number (e.g. 1000, 1001, 1002)
I'm using seq.NEXTVAL in which the user presses a button the value inserted in the LOG_NO field in form and when he/she commits it is inserted in the table. the problem is when the user cancels or whatever and then comes to insert again the next value is generated. (i.e. inserted 1001 and then cancelled, when inserting again its 1002). how can i make it go back to 1001 since it was not used in the table? any suggestions?
Re: INSERT UPDATE trigger HELP [message #225509 is a reply to message #225505] Tue, 20 March 2007 03:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Problem 1: "i need a trigger for UPDATE and INSERT on table A, so that it would update table B auomatically". What have you tried so far and what fields do you want to see updated?

Problem 2 is no problem at all. Sequences will have gaps. Always. Is that a bad thing? I don't think so.

MHE
Re: INSERT UPDATE trigger HELP [message #225511 is a reply to message #225505] Tue, 20 March 2007 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

To complete the answer for point 2.
You simply can't unless you use a custom sequence and serialize all the transactions.

Regards
Michel
Re: INSERT UPDATE trigger HELP [message #225520 is a reply to message #225505] Tue, 20 March 2007 03:56 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Problem a)
You need the MERGE command, and a trigger like this one:
create table trg_test_1 (col_a varchar2(10), col_b varchar2(10)
                        ,constraint col_a_unq unique (col_a,col_b));

create table trg_test_2 (col_a varchar2(10), col_b varchar2(10),col_c varchar2(10));

create or replace trigger trg_test_2_bri 
before insert on trg_test_2 for each row
BEGIN
  merge into trg_test_1  t1
  using (select :new.col_a col_a,:new.col_b col_b from dual) t2
  on (t2.col_a = t1.col_a)
  when matched then update set t1.col_b = t2.col_b
  when not matched then insert (col_a,col_b) values (t2.col_a,t2.col_b);
end;
/

insert into trg_test_2 values ('B','X','A');
insert into trg_test_2 values ('B','Y','B');
insert into trg_test_2 values ('C','Y','B');
insert into trg_test_2 values ('C','Z','B');

SQL> select * from trg_test_1;

COL_A      COL_B
---------- ----------
B          Y
C          Z


Problem b) You're kind of out of luck. An Oracle sequence guarantees to provide a unique, increasing sequence, but it isn't gap free. The only way of doing this is some sort of post processing task that goes through all the committed records and populates them with a value. You could do this with a process called from Dbms_Jobs, and then have an AFTER trigger that would create such a job if there isn't already one waiting to go.

Generally, you don't really need gap free sequences if you do a little poking into the reasons behind the requirement.
Previous Topic: sql help
Next Topic: Trigger issue
Goto Forum:
  


Current Time: Thu Dec 08 10:29:13 CST 2016

Total time taken to generate the page: 0.08205 seconds