CODEWITHSUNDEEP
postgresqlstored-procedurestriggers
Zeke
Zeke

Reputation: 57

stored procedure, accessing data that has just been inserted

I am trying to insert values into a table based upon a person being inserted into another table. I have a trigger for this, when someone is assigned to employee, they are automatically assigned to employeepark with the first spot that is available. I cannot figure out how to access the id that is being input into the employee table. I would appreciate any tips or ideas, thank you !

This is the error I am receiving.

ERROR:  record "new" is not assigned yet

create or replace function new_employeeAssign() returns trigger as $new_employeeAssign$
declare 
    open_spotID int := (select parkingspot.spotid  
                        from employeepark e full outer join parkingspot on e.spotid = parkingspot.spotid
                        where e.spotid isNull limit 1);
begin 
    insert into employeepark(employeeid, spotid)
        values(new.employeeid ,open_spotID);
End;
$new_employeeAssign$ language plpgsql;

create trigger new_employeeAssign after insert on employee
   execute procedure new_employeeAssign();

insert into people(peopleid, fname, lname)
    values(686, 'random', 'person');
insert into employee(employeeid)
    values(686);

Patrick figured this out for me now I am running into THIS PROBLEM: I want to select the first value out of all of these ranges that is null, I keep getting back one though and it is just bypassing the ranges and going straight to the isNull. 

    (select parkingspot.spotid  
    from employeepark e full outer join parkingspot on e.spotid =     parkingspot.spotid
    where  (e.spotid = 301) 
    or (e.spotid = 1601) 
    or (e.spotid = 2001) 
    or (e.spotid = 2011) 
    or (e.spotid = 2121) 
    or (e.spotid = 2021) 
    or (e.spotid = 2771) 
    or (e.spotid = 2921) 
    or (e.spotid = 3021) 
    or (e.spotid = 3823) isNull
    limit 1)

Upvotes: 1

Views: 118

Answers (1)

Patrick
Patrick

Reputation: 32161

Your trigger definition is incorrect. By default, a trigger applies to FOR EACH STATEMENT and then the NEW parameter does not exist (the trigger does not apply to a row, after all). Instead you should have:

CREATE TRIGGER new_employeeAssign AFTER INSERT ON employee
FOR EACH ROW EXECUTE PROCEDURE new_employeeAssign();

There are also some issues with your trigger function, in particular the query that assigns to variable open_spotID. This query will always select NULL because e.spotid IS NULL and you join on e.spotid = parkingspot.spotid. The logic that you are looking for is probably that you want to assign a parking slot to a new employee by making a row in table employeepark with a spot_id that is not already assigned to some other employee. See code below.

You also have to RETURN NEW from the function.

Other than that, your trigger function could be much optimized like so:

CREATE FUNCTION new_employeeAssign() RETURNS trigger AS $new_employeeAssign$
BEGIN
    INSERT INTO employeepark(employeeid, spotid)
        SELECT NEW.employeeid, spotid  
        FROM parkingspot p
        LEFT JOIN employeepark e USING (spotid)
        WHERE e.employeeid IS NULL
        LIMIT 1;
    RETURN NEW;
END;
$new_employeeAssign$ LANGUAGE plpgsql;

Upvotes: 2

Related Questions