CODEWITHSUNDEEP
sqloracle-databaseplsqldatabase-trigger
R Ram
R Ram

Reputation: 195

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

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

Upvotes: 0

Views: 2193

Answers (2)

Stefano Di Cecco
Stefano Di Cecco

Reputation: 101

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.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65288

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

Upvotes: 1

Related Questions