Newbie Trigger Syntax

Newbie Trigger Syntax

Post by Edmu » Sun, 02 Dec 2001 05:00:21



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

 
 
 

Newbie Trigger Syntax

Post by Forrest Cicogn » Sun, 02 Dec 2001 08:14:50


declare

   skill_rec c.skill_number%type;

begin

/* Implicit cursor; Throws NO_DATA_FOUND when
    employee does not posess skill. . . .      */

 select    c.skill_number into skill_rec
   from   c
 where   c.employee_number = :old.employee_number
    and    c.skill_number = 1
    and    c.rownum <=1;  --Important: one row at most!

exception
    when NO_DATA_FOUND
         then raise_application_error(-20225, "Unqualified");

end;

 
 
 

Newbie Trigger Syntax

Post by Edmu » Sun, 02 Dec 2001 22:49:50


Thanks for your help, Forrest.
I ran your code, and it complained about c.rownum - it said
something like "No column named 'rownum' in table c'"
which is true.
You marked that it was important to have that code for some reason;
it seems to me that you are making sure that there are not multiple
return values, and maybe the syntax is wrong? (not that I know the
correct
syntax...)
If that is the case - you needn't worry, because the employee_number
and skill_number combination are the stated primary key for table c,
so uniquness is already guaranteed.
So, I ran the code without that line, input some values that would
cause the error, the error message displayed properly, along with some
other error messages that had me a little worried, but it seems to
work fine!

Many thanks

Edmund

 
 
 

Newbie Trigger Syntax

Post by Forrest Cicogn » Mon, 03 Dec 2001 01:44:47


Sorry, it was late when I wrote that. An alternative that came up on the
group later was using the EXISTS clause, which will eliminate the need for
an INTO clause, as well as the WHERE ROWNUM <= 1. When it evaluates to true,
the statement processes without doing anything. When FALSE, the same
NO_DATA_FOUND error is thrown. EXIXTS is "best practice", as well, which is
more important.



Quote:

> Thanks for your help, Forrest.
> I ran your code, and it complained about c.rownum - it said
> something like "No column named 'rownum' in table c'"
> which is true.
> You marked that it was important to have that code for some reason;
> it seems to me that you are making sure that there are not multiple
> return values, and maybe the syntax is wrong? (not that I know the
> correct
> syntax...)
> If that is the case - you needn't worry, because the employee_number
> and skill_number combination are the stated primary key for table c,
> so uniquness is already guaranteed.
> So, I ran the code without that line, input some values that would
> cause the error, the error message displayed properly, along with some
> other error messages that had me a little worried, but it seems to
> work fine!

> Many thanks

> Edmund

 
 
 

1. newbie - trigger - syntax

Hi,

Further to my previous question on syntax, I have a question regarding the
syntax of a trigger. This question is probably more to do with general TSQL
syntax than triggers but please bare with me.

I realise that this probably isn't the best design in the world but I figure
its a good learning exercise and I can always change it later.

I have two tables:

PucrhaseOrder - Among others it has a bit field named "Complete".
LineItems - This contains (among others) =
    "PO_ID"
    "DeliveryDate"
    "DeliveryReference"
    "InvoiceDate"
    "InvoiceRef"

There is a many to one relationship between the tables in that a PO may have
many LineItems.

What I would like the trigger to do is after a lineitem row is updated, the
trigger checks to see if DeliveryDate/Ref, InvoiceDate/Ref are not null for
every record in the LineItems table with a PO of X. If they all not null
then it updates the "Complete" field in the PO table to true.

However, I'm not really sure how to do this. The code I have so far is:

Create Trigger trigMarkPOComplete
On dbo.tblLineItems
For Update
As

Its the code to check the value of the fields I'm not sure about. If anyone
could point me in the right direction I'd be very grateful. I'm afraid my
TSQL syntax is appalling. :)

Any advice is gratefully received.

Cheers

Chris Strug

2. Arrays

3. Newbie to Triggers - Syntax Help

4. Fox Pro Developer Needed

5. Newbie: Help on trigger syntax needed.

6. access2 to paradox5

7. trigger for db audit purposes - newbie to the world of triggers

8. TX-DALLAS-100420--Informix-ORACLE-SQL-UNIX-Visual Basic-Informix DBA

9. Trigger Newbie... Help with Trigger

10. Help Newbie dying with SP syntax

11. Newbie stored proc syntax question

12. NEWBIE: easy syntax question with UPDATE statement

13. embarrassing newbie query syntax for remote machine