Home » SQL & PL/SQL » SQL & PL/SQL » trigger question
trigger question [message #36309] Thu, 15 November 2001 11:30 Go to next message
Tony
Messages: 190
Registered: June 2001
Senior Member
I have to create a trigger that updates a column with a sequential number when a separate column is updated. For instance, I enter in the number 123 in column A, when that occurs I need the trigger to enter in number 001 to column B, then I enter in 124 to column A, the trigger updates the column to number 002 in column B, etc. I have 2 questions:
First, if I were to reboot my node, how can I be certain that upon the next update, the trigger will provide the next number sequentially, and not start again at 001?
Second, what would some example syntax be of such a statement that adds numbers sequentially to a column upon update? Thanks.

----------------------------------------------------------------------
Re: trigger question [message #36311 is a reply to message #36309] Thu, 15 November 2001 13:18 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Assumed that column a could be null on insert and you wouldn't want to set b = 1 in this case. If you want b = 1 on any insert, just remove the 'is not null' conditional.

create or replace trigger mytrig
before insert or update of a on t
for each row
begin
  if inserting then
    if :new.a is not null then
      :new.b := 1;
    end if;  
  elsif updating then
    :new.b := :new.b + 1;
  end if;
end;
/


----------------------------------------------------------------------
Re: trigger question [message #36347 is a reply to message #36311] Mon, 19 November 2001 06:54 Go to previous message
Tony
Messages: 190
Registered: June 2001
Senior Member
Hi,
Here's the trigger I have written below:

create or replace trigger my_mrn
before insert or update of MRN on MRN_DEASSOC
for each row
begin
:new.MEDI_MRN := 100000000;
if inserting then
if :new.MRN is not null then
:new.MEDI_MRN := :new.MEDI_MRN + 1;
end if;
end if;
end;
/

I can only get the number 10000001 created for each insert. I know why because I defined :new.MEDI_MRN := 100000000, how can I get :new.MEDI_MRN to equal the last number created, so I can get 100000002, etc.?
Also, can I add something like:
':new.MEDI_MRN := select max(medi_mrn) from tableA'
so I can ensure the operation will maintian sequential integrity if a reboot were to occur? Thanks.

----------------------------------------------------------------------
Previous Topic: Trigger problem @ Execution
Next Topic: Hiding tablenames and using synonyms ?
Goto Forum:
  


Current Time: Thu Mar 28 05:42:58 CDT 2024