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>

Answer 2

CREATE  DEFINER=`root`@`localhost` TRIGGER `TRIGGERNAME` AFTER UPDATE ON `employee` FOR EACH ROW 

BEGIN

SET employee.da = employee.basic*(5.0/100)
SET employee.gross = employee.basic + (employee.basic*(5.0/100))

END

Answer 3

The way you have them defined both da and gross are derivable directly from base. However as a standard column I can update either of them directly. If this is not desirable, then you can declare them as virtual columns.

alter table employee drop (da, gross); 

alter table employee add ( 
                  da    generated always  as base * 0.05 virtual
                , gross generated always  as base * 1.05 virtual 
                );  

Now neither column can be updated independently and both are automatically updated when base is updated. You use them as normal columns of select, but DO NOT reference then in Insert or Update. And there is NO trigger needed.

See fiddle. Note: You did not specify what Oracle version you are using. This requires 11gR1 or higher.