Fetching a column value for current row in before insert Oracle trigger

Question

I am creating a trigger in oracle which gets triggered before any insert statement. I want to insert a value in column2 using that trigger, but the problem is, I have to first get the the value of another column which is column 1 , of the new row being inserted and based on that value ,I will write business logic to insert the value in column2.

I am unable to access the value of column1 for the new row which will be inserted. How can I access the values of the new row being inserted.

Scenario is like this: Value for column2 has to be inserted using trigger. But, to popluate value in column2 , first value for column1 from the row which is going to be inserted, is required. and Based on the value of column1, value for column2 will be calculated and inserted. Please help me with syntax and proper resolution.

Use Case is:

Suppose column1 value is TS-1 then column2 value has to be TS-1-1, for the next time column2 value will be TS-1-2 and so on. SO there could be multiple values for TS-1 in the table, and for that every time vales for column2 will increase as TS-1-,TS-1-2, TS-1-3 and so on, last digit gets incremented for column2.

So I will have the fetch the maximum value of last number getting incremented and increase it by 1 for each insert.

Please help

" am unable to access the value of column1 for the new row which will be inserted." What does this mean? Please show code and any errors. — OldProgrammer
– OldProgrammer, Commented Aug 20, 2018 at 13:42

Barbaros Özhan · Accepted Answer · 2018-08-20 14:36:30Z

Once you create such a trigger as below :

SQL> create or replace trigger trg_tab_bi
  before insert on tab  
  for each row
declare
  nr int;
begin
  select count(1)+1
    into nr
    from tab
   where col1 = :new.col1;
  :new.col2 := :new.col1||'-'||nr;
end;

you may manage your desired duty like in a below way:

SQL> insert into tab(col1) values('TS-1');

1 row inserted

SQL> select t.*
  2    from tab t
  3   order by col1, col2;

COL1        COL2
------      ------   
TS-1        TS-1-1

SQL> insert into tab(col1) values('TS-1');

1 row inserted

SQL> select t.*
  2    from tab t
  3   order by col1, col2;

COL1        COL2
------      ------   
TS-1        TS-1-1
TS-1        TS-1-2

SQL> insert into tab(col1) values('TS-1');

1 row inserted

SQL> select t.*
  2    from tab t
  3   order by col1, col2;

COL1        COL2
------      ------   
TS-1        TS-1-1
TS-1        TS-1-2
TS-1        TS-1-3

SQL> insert into tab(col1) values('TS-7');

1 row inserted

SQL> select t.*
  2    from tab t
  3   order by col1, col2;

COL1        COL2
------      ------   
TS-1        TS-1-1
TS-1        TS-1-2
TS-1        TS-1-3
TS-7        TS-7-1

SQL> insert into tab(col1) values('TS-7');

1 row inserted

SQL> select t.*
  2    from tab t
  3   order by col1, col2;

COL1        COL2
------      ------   
TS-1        TS-1-1
TS-1        TS-1-2
TS-1        TS-1-3
TS-7        TS-7-1
TS-7        TS-7-2

Stefano Di Cecco · Accepted Answer · 2018-08-20 14:49:52Z

1

You can use these two terms in a trigger :old to reference the old value and :new to reference the new value.

Here is an example from the Oracle documentation linked to above

CREATE OR REPLACE TRIGGER Print_salary_changes   
   BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab   
   FOR EACH ROW WHEN (new.Empno > 0) 
DECLARE
    sal_diff number; 
BEGIN
    sal_diff  := :new.sal  - :old.sal;
    dbms_output.put('Old salary: ' || :old.sal);
    dbms_output.put('  New salary: ' || :new.sal);
    dbms_output.put_line('  Difference ' || sal_diff); 
END;

In this example the trigger fires BEFORE DELETE OR INSERT OR UPDATE :old.sal will contain the salary prior to the trigger firing and :new.sal will contain the new value.

edited Aug 20, 2018 at 14:49

user330315

answered Aug 20, 2018 at 14:34

Stefano Di Cecco

1201 silver badge10 bronze badges

1 Comment

llamaro25 Over a year ago

2 years later and this solved my problem. thank you

Collectives™ on Stack Overflow

Fetching a column value for current row in before insert Oracle trigger

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related