How to make a trigger between to tables SQL

Question

I have 2 tables in my data in SQL. One is Class and another is EXtra_Hours, basically, I want to calculate the amount school should pay in extra hours to teachers.

Table Class has the following attributes:

- class_id
- hourly_rate
- class_type

Table tab_extra_hours has the following attributes:

- teacher_id
- class_id
- extra_hours
- amount_extra_hours

I'm trying to create a trigger so that everytime I update the number of extra hours, having in consideration the class_id (because each class_id has a different hourly rate) it automatically updates the amount_extra_hours in EUR that the teacher should get.

Can someone help me?

Thanks!

Akhilesh Mishra · Accepted Answer · 2020-05-18 18:07:13Z

1

First you have to create a trigger procedure for your business logic like below

CREATE OR REPLACE FUNCTION trig_update_amount()
  RETURNS "pg_catalog"."trigger" AS $BODY$
    declare hourlyrate decimal(10,2);
    begin
    hourlyrate  =(select hourly_rate from class_ where class_id=new.class_id);

    update tab_extra_hours set amount_extra_hours=(hourlyrate*new.extra_hours) where class_id=new.class_id and teacher_id=new.teacher_id;

    return NEW;
    end;
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

then you have to write the trigger according to your scenario (on change of extra_hours column in table tab_extra_hours):

  CREATE TRIGGER update_extra_hours
  AFTER UPDATE OF extra_hours
  ON tab_extra_hours 
  FOR EACH ROW
  EXECUTE PROCEDURE trig_update_amount();

answered May 18, 2020 at 18:07

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to make a trigger between to tables SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related