Input validation

On 8/7/19 11:07 AM, stan wrote:

Have table that contains employee keys, paired up with work type keys
(both foreign keys) and a 3rd column that you enter a billing rate in.
Then I have a table where employees enter their work. I need to validate
that the employee, work type pair exists, before allowing the new record
to be inserted.

Any thoughts as to good way to do this?

INSERT UPDATE trigger:

https://www.postgresql.org/docs/11/sql-createtrigger.html

https://www.postgresql.org/docs/11/plpgsql-trigger.html

--
Adrian Klaver
adrian.klaver@aklaver.com

On 8/7/19 12:07 PM, stan wrote:

Have table that contains employee keys, paired up with work type keys
(both foreign keys) and a 3rd column that you enter a billing rate in.
Then I have a table where employees enter their work. I need to validate
that the employee, work type pair exists, before allowing the new record
to be inserted.

Any thoughts as to good way to do this?

Does the employee interactively specify the "work type" then some
time-spent value?

Can the work-type be chosen from a drop-down generated by

    select work_type from table where employee = <current user>

Otherwise you'll need a trigger on the insert into "enter their work"
table.  Sad thing here is the user has likely left the scene.

I think a check will also work but I second triggers.

Thanks,
~Ben

On Wed, Aug 7, 2019, 2:21 PM Rob Sargent <robjsargent@gmail.com> wrote: