simple trigger

[ballouta]

Hello

I have two tables:

1) order_line (order_number, part_number,number_oredred, quoted_price)

2) part (part_number, units_on_hands, item_class...)

 

i am requested to write an INSERT trigger on order_line table. the trigger should decrease the units on hands in the part table for the part just inserted into the order_line table.

 

I am trying to write this trigger by myself, but I have something wrong. Please Help

 

delimiter //
create trigger order_line_ai
After Insert
on order_line
for each row
Begin
Set @part_num = New.part_number;
update part set units_on_hands = units_on_hands-- where part.part_number = @part_num;
End;
//

[gristoi]

hi, I have just tested this trigger in my workbench, It dosent like the -- you are using to decrease the number by one. Try

delimiter //
create trigger order_line_ai
After Insert
on order_line
for each row
Begin
Set @part_num = New.part_number;
update part set units_on_hands = units_on_hands - 1 where part.part_number = @part_num;
End;
//

[ballouta]

Thank you so much

it is working

 

Kindly i have a question

is there anyway to put the insert statement inside this trigger?

I am confused because the examples in saw in the book make some kind of filtration on the insert command,

but in this question, we are not doing any kind of filtration except we r decreasing an amount from another table. correct?

 

thanks

[gristoi]

I am not fully understanding your question. Basically think of the trigger as a listener. You tell it to either listen on the table for an insert or update and depending on the action performed 'trigger' a mysql query. So with that in mind what do you want to insert?

[ballouta]

Thank You

It is clear now.

whenever I execute a new procedure or trigger,

the commands don't execute after that.

for example if I write select * from customer;

it gives me

mysql ->

why?

[gristoi]

If you are running these from command line then it sounds like you are not terminating the line on the query. instead of using ';' try using \g, or \G at the end of your quries

[ballouta]

I appreciate ur help

yes I am using the command line

BYE