Newbie Trigger Syntax

Hello all,

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 employee.
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
begin
if 1 is not in ( select skill_number from C   where employee_number = :old.employee_number;) then   raise_application_error(-20225, "Unqualified"); end if;
end;

Thanks

Edmund Received on Fri Nov 30 2001 - 14:00:21 CST