postgresql trigger by using 2 tables

Question

I have 3 tables:

Employee(eid, ename, salary, did, classification)
Project(pid, pname, did, budget, duedate)
Onproject(pid, eid, fromdate)

I try to write trigger that checks on insert of a new employee to Onproject, that the duedate is greater than one month from fromdate, if not return a error message, and the record won't be added to the table.

I tried

CREATE TRIGGER T1
BEFORE INSERT
ON Onproject
FOR EACH ROW
EXECUTE PROCEDURE trigf1();

create or replace function trigf1() returns trigger as
$BODY$ BEGIN
IF (DATE_PART('day', NEW.fdate::date) - DATE_PART('day', duedate::date) > 30)
THEN insert into Onproject values (NEW.pid, NEW.eid, NEW.fdate)
else
rais notice 'adding employee to the project failed, less then one month to due date.'
end if
end
$BODY$
LANGUAGE PLPGSQL VOLATILE

but the trigger doesn't know the duedate field and the Project table.

How can I create the trigger by using the Project and Onproject tables?

What error did you get?

Jonathan Hall
– Jonathan Hall

2019-11-26 21:31:35 +00:00
Commented Nov 26, 2019 at 21:31 — Jonathan Hall
– Jonathan Hall, Commented Nov 26, 2019 at 21:31

user330315 · Accepted Answer · 2019-11-26 22:40:09Z

2

You are not returning anything from the trigger. If you want the INSERT to proceed simply return the new row, if you want it to fail, you need to raise an error.

You also need to run a select on the project table to retrieve the duedate, you can't just take it out of thin air:

create or replace function trigf1() returns trigger as
$BODY$ 
declare
  l_duedate date;
BEGIN
  -- get the duedate for the project
  select duedate 
    into l_duedate
  from project
  where pid = new.pid;

  IF l_duedate > NEW.fromdate::date + interval '1 month'
  THEN  
     -- everything OK, proceed with the insert
     return new;
  end if;

  -- using raise error, aborts the transaction
  raise error 'adding employee to the project failed, less then one month to due date.'
end;
$BODY$
LANGUAGE PLPGSQL VOLATILE

answered Nov 26, 2019 at 22:40

user330315

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

Collectives™ on Stack Overflow

postgresql trigger by using 2 tables

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