trigger in PL/SQL

Question

There are two tables given:

1)

employee(eno,ename,basic,da,gross)
da=basic*(5.0/100)
gross = basic+da

2)

sal_hist(eno, sys_dt, old_basic)

How to write a trigger to update the 'da' and 'gross' whenever I am updating basic salary of the employee?

Answer 1

PL/SQL tag suggests that you use Oracle database.

You said that there's yet another table, sal_hist, but - you didn't say what to do with it. I presume you'd want to save the old basic salary.

In that case, trigger would look like this:

SQL> create or replace trigger trg_biu_emp
  2    before insert or update on employee
  3    for each row
  4  begin
  5    insert into sal_hist(eno, sys_dt, old_basic) values
  6      (:new.eno, sysdate, :old.basic);
  7
  8    :new.da := :new.basic * 5 / 100;
  9    :new.gross := :new.basic + :new.da;
 10  end;
 11  /

Trigger created.

Let's see how it works:

SQL> select * From employee;

       ENO ENAME      BASIC         DA      GROSS
---------- ----- ---------- ---------- ----------
         1 Scott        100          0          0
         2 Tiger        500          0          0

SQL> select * From sal_hist;

no rows selected

SQL> update employee set basic = 200 where eno = 1;

1 row updated.

SQL> insert into employee (eno, ename, basic) values (3, 'King', 1000);

1 row created.

SQL> select * From employee;

       ENO ENAME      BASIC         DA      GROSS
---------- ----- ---------- ---------- ----------
         1 Scott        200         10        210
         2 Tiger        500          0          0
         3 King        1000         50       1050

SQL> select * From sal_hist;

       ENO SYS_DT               OLD_BASIC
---------- ------------------- ----------
         1 06.06.2020 11:10:49        100
         3 06.06.2020 11:12:07

SQL>