Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class Triggers
    ' Enter existing table or view for the target and uncomment the attribute line
    <Microsoft.SqlServer.Server.SqlTrigger(Name:="tblweightTrigger", Target:="Evaluation", Event:="FOR UPDATE, INSERT, DELETE")> _
    Public Shared Sub tblweightTrigger()
        ' Replace with your own code
        Dim i As Integer = 0
        Dim sqlCn As New SqlConnection("Context Connection=true")
        Dim sqlCm As New SqlCommand
        Dim sqlTrCtx As SqlTriggerContext = SqlContext.TriggerContext
        Dim spPipe As SqlPipe = SqlContext.Pipe
        Dim sXml As SqlXml = sqlTrCtx.EventData()
        Dim sqlDr, dr, drin As SqlDataReader
        If sqlTrCtx.TriggerAction = TriggerAction.Delete Then
            sqlCm.CommandText = "select tenderer,criteria from deleted"
        Else
            sqlCm.CommandText = "select tenderer,criteria from inserted"
        End If
        sqlCm.Connection = sqlCn
        sqlCn.Open()
        sqlDr = sqlCm.ExecuteReader
        Dim criteria, tenderer As String
        sqlDr.Read()
        criteria = sqlDr.GetValue(1).ToString
        tenderer = sqlDr.GetValue(0).ToString
        sqlDr.Close()
        sqlCm.CommandText = "select top(3) weightcountry*vendorweight from evaluationview where tenderer='" & tenderer & "' and criteriaid = '" & criteria & "' and weightcountry*vendorweight<>0 order by weightcountry*vendorweight"
        dr = sqlCm.ExecuteReader
        Dim av As Single = 0
        Dim count As Integer = 0
        For i = 0 To 2
            If dr.Read() Then
                av += CType(dr.GetValue(0), Single)
                count += 1
            End If
        Next
        av = av / count
        dr.Close()
        If count <> 0 Then
            sqlCm.CommandText = "select * from weight where tenderer='" & tenderer & "' and criteriaid = '" & criteria & "'"
            drin = sqlCm.ExecuteReader
            If drin.Read Then
                sqlCm.CommandText = "update  weight set weight=" & av & " where tenderer='" & tenderer & "' and criteriaid = '" & criteria & "'"
            Else
                sqlCm.CommandText = "insert into  weight(criteriaid,tenderer,weight)  values('" & criteria & "','" & tenderer & "'," & av & ")"
            End If
            drin.Close()
            sqlCm.ExecuteNonQuery()
            
        Else
            sqlCm.CommandText = "delete from weight where tenderer='" & tenderer & "' and criteriaid = '" & criteria & "'"
            sqlCm.ExecuteNonQuery()
        End If
        sqlCn.Close()
    End Sub
End Class