SQL Help V. Triggers

Discussion in 'OT Technology' started by devguru616, Dec 9, 2007.

  1. devguru616

    devguru616 New Member

    Joined:
    Oct 1, 2007
    Messages:
    29
    Likes Received:
    0
    I am trying to create a trigger on insert/update/delete that will change a value in one table(TABLE A) if a change has been made to a value in a different table(TABLE B).

    Catch is the record in table a is computed on the set of records in table B corresponding, Table B's primary key is also based on two fields in the table.

    If someone provides me enough help to get this complete will buy em a sub for a couple of months.

    Just super confused and anything i've looked up online hasn't been of much assistance.
     
  2. Dnepr

    Dnepr Guest

    Is this Oracle, MSSQL, etc?
     
  3. devguru616

    devguru616 New Member

    Joined:
    Oct 1, 2007
    Messages:
    29
    Likes Received:
    0
    running mssql 05



    I understand simple triggers, but when using values between two or more different tables, ie, if a change on one, computation on the other.
     
    Last edited: Dec 9, 2007
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I don't understand the question. Give us a simple recordset of what you want the trigger to do.
     
  5. devguru616

    devguru616 New Member

    Joined:
    Oct 1, 2007
    Messages:
    29
    Likes Received:
    0
    COURSES (cno, ctitle, hours, dept-id)
    STUDENTS (sid, ... other fields here but they don't mater, hours, gpa)
    ENROLLMENTS (sid, term, line-no, grade)

    If hours in COURSES or grade in ENROLLMENTS changes, I need to compute and then update the corresponding student(or students) GPAs appropriately.
     
  6. mobbarley

    mobbarley Active Member

    Joined:
    Mar 4, 2005
    Messages:
    9,256
    Likes Received:
    2
    Location:
    Sydney
    on the existing table you want something like this:
    Code:
    CREATE TRIGGER name ON InsertTable
    FOR INSERT
    AS
    
    DECLARE @Id int
    DECLARE @ValueA int
    DECLARE @ValueB int
    DECLARE @ProdAB int
    
    SELECT @Id = (SELECT Id FROM INSERTED) [COLOR="DarkOrange"]the selected Id is the primary key of the field inserted[/COLOR]
    SELECT @ValueA = (SELECT Something1 FROM INSERTED)
    SELECT @ValueB = (SELECT Something2 FROM INSERTED)
    
    SELECT @ProdAB = @ValueA * @ValueB
    
    INSERT INTO CalculatedValues ( InsertedValuesId, Product) VALUES @Id, @ProdAB
    
    go
    
    you will then need something similar for update / delete. It has been a long time since I have done this and I dont have sql server running, so no idea if it will accept that, but it should give you an idea.
     
  7. mobbarley

    mobbarley Active Member

    Joined:
    Mar 4, 2005
    Messages:
    9,256
    Likes Received:
    2
    Location:
    Sydney
    as you have inserted the Id from the first table you can use update or even delete with a where (id = 123)
     
  8. devguru616

    devguru616 New Member

    Joined:
    Oct 1, 2007
    Messages:
    29
    Likes Received:
    0
    Any clue on as to how I would be able to computer the average for all records with the same ID/Term value ?

    Confuses the hell out of me.
     
  9. mobbarley

    mobbarley Active Member

    Joined:
    Mar 4, 2005
    Messages:
    9,256
    Likes Received:
    2
    Location:
    Sydney
    is grade a number? something like this?

    SELECT sid, avg(grade) as Avg
    FROM ENROLLMENTS
    WHERE TERM='1'
    GROUP BY sid
    ORDER BY sid ASC
     
  10. devguru616

    devguru616 New Member

    Joined:
    Oct 1, 2007
    Messages:
    29
    Likes Received:
    0
    yeah it could be, a float(ie 3, 3.5, 4)

    You can do those kinds of selects within the trigger itself ?

    ie.

    SELECT sid,term,avg(grade) as Avg
    FROM enrollments
    WHERE sid = (updated/inserted) SID
    AND term="someterm"
    GROUP BY SID,TERM
     
  11. mobbarley

    mobbarley Active Member

    Joined:
    Mar 4, 2005
    Messages:
    9,256
    Likes Received:
    2
    Location:
    Sydney
    yes you can, assign the updated sid to a variable as above, then put the variable name in your where clause.
     

Share This Page