Its urgent please help!!!!!
animaldetails(Table 1 )
Animal_Id int (primary key)
IsMilking boolean
Milking_Capacity varchar
milking (Table 2)
Animal_Id int (primary key)
Month_Year varcahr (eg. '6 2009' -- Means June 2009)
Day1 varchar
Day2 varchar
and so on (till 31)
milkingevent(Table 3)
milking int (primary key)
Requirements:
1. I need a trigger for table3 Before update.
2. Loop 1
3. In table 1 it will search for every row for attribute IsMilking if it is true it will store value of Milking_Capacity in an variable1 as well as well as Animal_Id in another variable2.
4. Now it should go to table 2 search for Animal_Id from variable2. and Match with current month and year
5. a. If not found INSERT statement "Insert into table2..." values (variable2, current(month and Year), if todays date is 1 then value of variable1 in Day1 or whatever, reset all should be default value)
6. b. if found Update statement "Update into table2 ..." (where if current date is 28 then set value in Day 28 reset should be as it is.....)
7. After it gets completed
8. Go to loop 1 and search for Second animal ID and the process continues.
##########################################################################################################################################################
DELIMITER$$;
CREATE TRIGGER TBU_Table3 BEFORE Update OF milking ON milkingevent
DECLARE
@_lAnimal_Id INTEGER;
DECLARE
@_lIsMilking INTEGER;
DECLARE
@_lMilking_Capacity VARCHAR(10);
DECLARE
@_lMonth_Year VARCHAR(10);
DECLARE
@_lCount INTEGER;
DECLARE
@_lAnimal_Id INTEGER;
DECLARE Table1_CUR CURSOR
FOR
SELECT Milking_Capacity,Animal_ID FROM animaldetails WHERE IsMilking= 'Yes'
OPEN Table1_CUR
FETCH NEXT FROM Table1_CUR INTO @_lMilking_Capacity,@_lAnimal_Id
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @_lMonth_Year= MONTH(getdate())+' '+YEAR(getdate())
SELECT @_lCount=COUNT(*) FROM milking WHERE Animal_Id=@_lAnimal_Id AND MonthYear=@_lMonth_Year
IF @_lCount>0
BEGIN
IF MONTH(getdate())=26
BEGIN
Update milking SET Day26=@_lMilking_Capacity
END
END
ELSE
IF MONTH(getdate())=26
BEGIN
INSERT INTO milking (Animal_Id,MonthYear,Day26) VALUES (@_lAnimal_Id, @_lMonth_Year, @_lMilking_Capacity)
END
END
FETCH NEXT FROM Table1_CUR INTO @_lMilking_Capacity,@_lAnimal_Id
CLOSE Table1_CUR
DEALLOCATE Table1_CUR
END$$
DELIMITER;$