| trigger question [message #36309] |
Thu, 15 November 2001 11:30  |
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  |
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 |
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.
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
