0

Here is my code so far:

CREATE OR REPLACE FUNCTION updateDegreeStatus()
RETURNS TRIGGER as $updateDegreeStatus$
  BEGIN
    IF(new.degreeName = old.degreeName and old.endDate = NULL)
    THEN
          UPDATE degree
          SET old.endDate = CURRENT_DATE
          WHERE degree.degreeID = old.degreeID;
    END IF;
    RETURN NEW;
    END;
    $updateDegreeStatus$ LANGUAGE plpgsql;

CREATE TRIGGER updateDegreeStatus
  BEFORE INSERT ON degree
  FOR EACH ROW
  EXECUTE PROCEDURE updateDegreeStatus();

What I having trouble with is accessing the current values of the row the trigger is currently on. How my stored procedure should work, is if the new insert has the same degree name as the current row and the current row value for end date is null then do this. But the keyword old is not how you access the current row values. And I have been reading through documentation and I still can not find the answer. If anyone has any input on how to fix this issue, I would love your help.

Here is my current table:

enter image description here

And my insert is this:

INSERT INTO degree(degreeID, degreeName, type, startDate, endDate)
    VALUES(3, 'Computer Science with a Concentration in Software Development', 'Concentration', CURRENT_DATE, null)

Now what I want to happen is when the insert command is issued it will fire off my trigger, and will go and check if the New degreeName equals the current row degreeName and if the current row endDate is null. If it is then it will issue the Update on the current row, which just sets the endDate to the CURRENT_Date.

MrCokeman
  • 63
  • 1
  • 6
  • `INSERT` doesn't have `old.`. You insert something new that wasn't there in the past, there for Insert has only `new.`, `Update` has both `old.` and `new.` and `Delete` has only `old.`, If you explain what you actually trying to do (provide a data example with the expected results) we might be able to help more. – Elad Apr 25 '16 at 06:59
  • @Elad I just add some edits to the original post that clears it up a little more. Thanks for helping. – MrCokeman Apr 25 '16 at 18:30
  • I don't understand what do you mean by current row. In the trigger you have only information about the data from the insert query. If you would like to compare this data to data from the table you have to query over the table. I think you are looking for `UPSERT` ability. Read this: http://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql – Elad Apr 26 '16 at 10:51

0 Answers0