Trigger syntax Help

[ballouta]

Hello

I am writing an Update trigger for my homework

I have two questions Please:

 

1) I am declaring variables in the trigger e.g: Set @old_part_num = OLD.part_number;

when I use this variable in a later query in the trigger, do I use the @ sign before the variable,

in other words, do I always use the variable name preceded by @ sign?

 

2) I have If statement in my trigger, the first condition contains more than once statement

how do i put several statements in one block (scope)

as in If and later in Else...

 

thank you

here is my trigger

delimiter //
create trigger order_line_ad #this is the update Trigger
After Update
on order_line
for each row
Begin
Set @old_part_num = OLD.part_number;
Set @new_part_num = New.part_number;
Set @order_num = OLD.order_number;

if EXISTS (select part_number from order_line where part_number = old_part_num)
	THEN 
		Set @old_units_onhand = select units_on_hand from parts where part_number = @old_part_num;
		Set @new_units_onhand = new.units_onhand;
		update parts set units_on_hand = new_units_onhand - old_units_onhand;
	ELSE
		Set @units_to_return = select number_ordered from order_line where part_number = @old_part_num;
		Set @old_units_onhand = select units_on_hand from parts where part_number = @old_part_num;
		Set @new_units_ordered = New.number_ordered;
		update order_line set part_number = @new_part_num, number_ordered = @new_units_ordered where order_number = @order_num;
		update parts set units_on_hands = @old_units_onhand + @units_to_return where part_number = @old_part_num;
End;
//
delimiter

[fenway]

1) Yes.

2) You can multiple BEGIN/END blocks.

[ballouta]

Thank you