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?
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>