How to use update trigger or dynamic update function

See more:

SQL

SQL-Server

trigger

i am a new sql aspirant and was given with a assignment and struck with it
i have a table like the following
---------------------------------------------------------------
no | studentID | subject | marks


1 001 maths 90
2 001 history 88
3 002 maths 99
4 002 history 88
5 002 computer 97



and i want the output like this

--------------------------------------
no | student name | total | rank
1 james 178 2
2 john 227 1



irrespective of the number of subjects , based on the register number the total should be added dynamically and rank should be produced

What I have tried:

SELECT
    m.StudentID as ID,
    s.SubjectName as subjects,
    m.MarkRate as marks
FROM 
    Mark m
    INNER JOIN Subject s on m.SubjectID = m.SubjectID 
ORDER BY 
    m.StudentID,
    s.SubjectName
----------------------------------
SELECT 
    X.StudentID,
    X.StudentName,
    ROWNUMBER() OVER ( ORDER BY X.TotalMark desc) as Rank
FROM (
    SELECT 
        m.StudentID,
        s.StudentName,
        sum(m.MarkRate) TotalMark
    FROM 
        Mark m
        INNER JOIN Student s on s.StudentID = m.StudentID 
    GROUP BY 
        m.StudentID,
        s.StudentName
) X
ORDER BY X.TotalMark desc

i treid this but unable to add values dynamically



also i need to use the UPDATE keyword can use it as trigger too.