0

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;$
1
Contributor
1
Reply
2
Views
8 Years
Discussion Span
Last Post by Chirag Fisher
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.