Trigger - postgres

Question

I have created the following tables

professor
------------------------------------------------------
ProfNr | Name | Room | Bonus
------------------------------------------------------

lecture
--------------------------------------------------
Lecture No | Title | semester hours | held by (Prof Reference)
--------------------------------------------------

I am looking for a trigger under the postgres using the weekly lesson the professor gives a bonus (salary)

Maybe I can help someone would, really great!

Or even an idea for a possible trigger other application

What you're asking for sounds like application logic that would be better placed in application code, not in a trigger. Or is this an academic exercise? If so, can you describe what you're trying to do in more detail? — David Aldridge, Jan 11 '13 at 11:55
I wanted by way of example only, a trigger are showing the integrity, (from the context Student Administration I have the tables: student, lecture, professor, assistant, and listen (with student and course) and check (with Prof, student, course and grade !) Now I would like to have a trigger that runs well under postgres. Can also must be another application also not be so complicated, the main thing the trigger does something with the tables! Would be nice if someone could help me there many thanks — viv1d, Jan 11 '13 at 12:21
@viv1d; Please put relevant information into your *question*, not into comments. Hit "edit" to the left under your question and clarify it. [Here is one example for a trigger](http://stackoverflow.com/a/12791071/939860) with basic explanation. [Try a search for many more](http://stackoverflow.com/search?q=[postgresql]+[trigger]++create+function). — Erwin Brandstetter, Jan 11 '13 at 23:49

score 0 · Answer 1 · answered Apr 20 '13 at 14:33

First, I think your bonuses should be another table so you can aggregate on demand. So this leaves three tables:

CREATE TABLE professor (
   profid serial not null unique,
   ...
);

CREATE TABLE lecture (
   lecture_id serial not null unique,
   professor int not null references professor(profid),
   ...
);

CREATE TABLE prof_bonus (
   lecture_id int references lecture(id),
   profid int references professor (profid),
   bonus_amt numeric not null,
   primary key (lecture_id, profid)
);

CREATE FUNCTION add_bonus() RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
INSERT INTO prof_bonus (lecture_id, profid, bonus_amt)
VALUES (new.lecture_id, new.profid, 100);
RETURN NEW;
END;
$$;

CREATE TRIGGER add_bonus AFTER INSERT TO lecture FOR EACH ROW 
EXECUTE PROCEDURE add_bonus();

Trigger - postgres

1 Answers1