SQL Server Performance

Trigger not Inserting in DestinationTable

Discussion in 'General Developer Questions' started by daipayan, Mar 2, 2009.

  1. daipayan New Member

    I have 2 tables:
    --CREATE TABLE
    create table MyTable
    ( Student varchar(10), course varchar(10), credit varchar(2)
    , gradeno int, remark varchar(10)
    )
    go

    --INSERT SAMPLE DATA
    insert MyTable select 'Steve', 'ASM01', 3, 50,0
    insert MyTable select 'Steve', 'ASM01', 3, 43,0
    insert MyTable select 'Bob', 'ASM01', 3, 0,0
    insert MyTable select 'Bob', 'OB01', 3, 23,0
    insert MyTable select 'Bob', 'OB01', 3, 59,0
    insert MyTable select 'Bob', 'ASM01', 3, 100,0
    insert MyTable select 'Andy', 'OB01', 3, 0,0
    insert MyTable select 'Andy', 'ASM01', 3, 50,0
    insert MyTable select 'Andy', 'ASM01', 3, 10,0
    insert MyTable select 'Andy', 'OB01', 3, 70,0
    insert MyTable select 'Andy', 'OB01', 3, 0, 'ABS'
    go

    CREATE TABLE DestinationTable( Student varchar(10), course varchar(10), credit varchar(2), Grades int, Grade varchar(10), Points int, Quality int )GO
    I now declared a trigger, which will update DestinationTable, whenever data is being inserted in MyTable. The Trigger is:
    CREATE TRIGGER UpdateDetailsON MyTableAFTER UPDATEASBEGININSERT INTO DestinationTable (Student, course, credit, Grades, Grade, Points, Quality)select Student, course, credit, sum(gradeno) as Grades , case when sum(gradeno) BETWEEN 91 and 100 then 'A+' when sum(gradeno) BETWEEN 83 and 90 then 'A' when sum(gradeno) BETWEEN 75 and 82 then 'B+' when sum(gradeno) BETWEEN 67 and 74 then 'B' when sum(gradeno) BETWEEN 59 and 66 then 'C+' when sum(gradeno) BETWEEN 51 and 58 then 'A' when sum(gradeno) BETWEEN 43 and 50 then 'A' when sum(gradeno) BETWEEN 35 and 42 then 'A'when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'when sum(gradeno) BETWEEN 0 and 34 then 'F' else 'NULL' end 'Grade', case when sum(gradeno) BETWEEN 91 and 100 then '8' when sum(gradeno) BETWEEN 83 and 90 then '7' when sum(gradeno) BETWEEN 75 and 82 then '6' when sum(gradeno) BETWEEN 67 and 74 then '5' when sum(gradeno) BETWEEN 59 and 66 then '4' when sum(gradeno) BETWEEN 51 and 58 then '3' when sum(gradeno) BETWEEN 43 and 50 then '2' when sum(gradeno) BETWEEN 35 and 42 then '1'when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0' when sum(gradeno) BETWEEN 0 and 34 then '0' else '0' end 'Points', case when sum(gradeno) BETWEEN 91 and 100 then 8 * credit when sum(gradeno) BETWEEN 83 and 90 then 7 * credit when sum(gradeno) BETWEEN 75 and 82 then 6 * credit when sum(gradeno) BETWEEN 67 and 74 then 5 * credit when sum(gradeno) BETWEEN 59 and 66 then 4 * credit when sum(gradeno) BETWEEN 51 and 58 then 3 * credit when sum(gradeno) BETWEEN 43 and 50 then 2 * credit when sum(gradeno) BETWEEN 35 and 42 then 1 * creditwhen sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0' when sum(gradeno) BETWEEN 0 and 34 then '0' else '0' end 'Quality'from Inserted
    group by Student, course, creditEND
    Please point out me the error in Trigger
  2. Sandy New Member

    Deepa,
    Can you tell me where exactly you are facing the problem..??
    Sandy.
  3. Adriaan New Member

    You might want to create the trigger on the table before inserting the data.
  4. Adriaan New Member

    Also please distinguish between updates and insertions - the title of this thread should be "Trigger not Inserting into my DestinationTable".
    Updates are on existing rows, inserts create new rows.
  5. daipayan New Member

    @Sandy:
    Sir, My aim is to insert/update data into DestinationTable, whenever data is being inserted/edited in MyTable, for this I created a Trigger, but this trigger is
    not working, there is some error in code of the trigger and am not getting the error.
    @Adriaan:
    Sir, the trigger should be called after the data is being inserted/edited in MyTable, as this trigger will automatically insert/update DestinationTable with the help of the trigger.
    Hope, I answered your questions.
    Daipayan
  6. Adriaan New Member

    Daipayan,
    You do not "call" triggers. A trigger is a piece of code that is attached to a table. This code is automatically executed when the event occurs - an insert, update or delete.
    So first you create the table with its trigger(s), then the trigger(s) will "go off" whenever the corresponding event occurs.
  7. daipayan New Member

    Sir,
    Sorry for my wrong wordings.
    I had created the table as well as its trigger, but the correspondent event is not ocurring and am not getting the error of the non-occurrance of the event.
    Will you please rectify or point out the error, PLEASE!! [:(]
  8. Adriaan New Member

    Your trigger will be fired only when data is inserted into the table.
    Your script creates the table, then inserts the data, and finally creates the trigger. So the data is already in the table when the trigger is created.
    The trigger does not get fired when it is created. Only new insertions of data into the table will fire the trigger. And so the data that is already in the table will not fire the trigger.
    In an INSERT trigger, the inserted snapshot only contains the inserted rows, not the existing rows.
    An INSERT trigger only fires for an insert event, not for an update event. You can use the FOR INSERT, UPDATE syntax to make the trigger fire for both events.
  9. daipayan New Member

    Sir, Its working...thank you for the suggestion...THANKS A LOT [:)][:D]
  10. daipayan New Member

    Sir, 1 problem:
    CREATE TRIGGER UpdateDetails1
    ON MyTable
    FOR INSERT, UPDATE
    AS
    BEGIN
    INSERT INTO DestinationTable ( Student, course, credit, gradeno.....)
    select Student, course, credit, sum(gradeno)
    .......
    from Inserted
    group by Student, course, credit
    END
    The output in destinationTable should be like this:
    --------------------------------------------------
    Student course credit Grades Grade Points Quality
    --------------------------------------------------
    Andy ASM01 3 60 C+ 4 12
    Andy OB01 3 70 B 5 15
    Bob ASM01 3 100 A+ 8 24
    Bob OB01 3 82 B+ 6 18
    Steve ASM01 3 93 A+ 8 24
    --------------------------------------------------
    But, it coming as:
    --------------------------------------------------
    Student course credit Grades Grade Points Quality
    --------------------------------------------------
    Steve ASM01 3 50 A 2 6
    Steve ASM01 3 43 A 2 6
    Bob ASM01 3 0 F 0 0
    Bob OB01 3 23 F 0 0
    Bob OB01 3 59 C+ 4 12
    Bob ASM01 3 100 A+ 8 24
    Andy OB01 3 0 F 0 0
    Andy ASM01 3 50 A 2 6
    Andy ASM01 3 10 F 0 0
    Andy OB01 3 70 B 5 15
    Andy OB01 3 0 0 0 0
    --------------------------------------------------
    The SUM and Group By is not working!
    Why is it so??
  11. Adriaan New Member

    Run only the SELECT part of your INSERT query, and look at the results.
    The columns that you are hiding in this script will be showing varying values for the same (Student, Course, Credit) combination, resulting in multiple lines.
  12. daipayan New Member

    While running the SELECT part, the output is not giving any error, the error coming through Trigger only.
  13. Adriaan New Member

    What error?
    Please look at the second paragraph in my reply for another suggestion.
  14. daipayan New Member

    Is there any way to solve this part?
  15. Adriaan New Member

    Yes.
    But you're not terribly clear about (1) the data that is already in the target table, then (2) what is inserted into the "trigger table", and (3) what you expect tot be in the target table afterwards.

Share This Page