CODEWITHSUNDEEP
oracle-databaseplsql
user2001117
user2001117

Reputation: 3777

Update table column after insert into same table-PLSQL

There are 4 columns in a table, marks1, marks2,marks3 and total. The trigger should calculate the total and update the total when we insert marks1, marks2 and marks3.

Upvotes: 2

Views: 1066

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27261

If it happens that you are using Oracle 11g, to achieve the desired result you can add a virtual column to a table:

SQL> create table your_table(
  2    marks1 number,
  3    marks2 number,
  4    marks3 number
  5  )
  6  ;

Table created

SQL> 
SQL> alter table your_table
  2    add total number generated always as (nvl(marks1, 0)+
  3                                          nvl(marks2, 0)+
  4                                          nvl(marks3, 0)
  5                                          )
  6  ;

Table altered

SQL> insert into your_table(marks1,marks2,marks3)
  2    values(1,2,3);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from your_table;

    MARKS1     MARKS2     MARKS3      TOTAL
---------- ---------- ---------- ----------
         1          2          3          6

Upvotes: 2

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23767

create or replace trigger calc_total
before insert on your_table
for each row
begin
  :new.total := :new.marks1 + :new.marks2 + :new.marks3;
end;

Upvotes: 1

Related Questions