I'm trying to adapt a book example and failing.
Table A has employee_number, and stuff about employees.
Table B has skill_number, and stuff about skills employees might have.
Table C is the bridege table between A & B, it has as it's primary key
employee_number and skill_number - 1 row for each skill posessed by an
Table D has employee_number as a foreign key. It's a table where the
employee "signs off" on some work that has been completed.
What I want to do is: Before allowing the employee to "sign off" on
the work being completed, I want a trigger to check that the employee
has the necessary skill that makes the employee "qualified" to sign
off on the work being done correctly.
So, I want a trigger so that when an employee "signs off" in Table D,
it checks in Table C that the employee_number (which matches the
employee_number from the row being updated in Table D) is associated
with the correct skill.
There is only one possible skill number that is acceptable in this
context, so there is no need to grab it at run time, it can be
hard-coded - I'll call it skill number 1.
So, I want to check in Table C that there is a row that has the
"signing off" employee_number and skill_number 1.
My sad attempt is as follows:
Create or replace Trigger trig
before update of myColumn on D
for each row
if 1 is not in ( select skill_number from C
where employee_number = :old.employee_number;) then