CODEWITHSUNDEEP
postgresqlplpgsqldatabase-trigger
gal leshem
gal leshem

Reputation: 561

postgresql trigger by using 2 tables

I have 3 tables:

  1. Employee(eid, ename, salary, did, classification)
  2. Project(pid, pname, did, budget, duedate)
  3. 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?

Upvotes: 0

Views: 36

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 2

Related Questions