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


Public Class PubsTriggers

    ' used for trigger when rows are deleted from
    ' the jobs table. Deleted jobs are moved to
 ' the obsoletejobs table
 <SqlTrigger(Name:="AddToObsolete", Target:="Jobs", Event:="FOR DELETE")> _
 Public Shared Sub AddToObsolete()
        ' get the trigger contesxt so you can tell what is
        ' going on
        Dim tc As SqlTriggerContext = SqlContext.TriggerContext
        ' this trigger is for deletes
        If tc.TriggerAction = TriggerAction.Delete Then
            ' Make a command
            Dim conn As New SqlConnection("Context Connection=true")
            Dim cmd As New SqlCommand
            cmd.Connection = conn
            ' make a command the inserts the deleted row into
            ' the obsoletejobs table
            cmd.CommandText = "insert into obsoletejobs select * from deleted"
            ' move the rows
            Dim rowsAffected As Integer = CType(cmd.ExecuteNonQuery(), Integer)
            'SqlContext.GetPipe().Send(rowsAffected.ToString() + " inserted into obsoletejobs");
            If rowsAffected = 0 Then
                ' something bad happened, rollback
                Transaction.Current.Rollback()
            End If
        End If
 End Sub

    ' Used for trigger on authors table
    ' prevents the authors contract from
    ' being changed from 1 to 0 if any
    ' author (of those being updated) 
    ' has a book that pays a royality
    ' greater than 30.
    ' All publishers should have this
 ' trigger in their databases
 <SqlTrigger(Name:="AuthorCheckContract", Target:="authors", Event:="FOR UPDATE")> _
    Public Shared Sub AuthorCheckContract()
        ' get the trigger context for later
        Dim tc As SqlTriggerContext = SqlContext.TriggerContext
        ' this is used on for updates
        If tc.TriggerAction = TriggerAction.Update Then
            Dim conn As New SqlConnection("Context Connection=true")
            Dim cmd As New SqlCommand
            cmd.Connection = conn
            ' check to see if the contract is being changed
            ' from 1 to 0

            cmd.CommandText = "select count(i.contract) from inserted i" & _
            " join deleted d on i.au_id = d.au_id " & _
            " join titleauthor ta on i.au_id = ta.au_id " & _
            " where i.contract = 0 and d.contract = 1 " & _
            " and ta.royaltyper > 30 "



            Try
                If CType(cmd.ExecuteScalar(), Integer) > 0 Then
                    ' if you get to here then at
                    ' least one of the authors in
                    ' those in the updated table
                    ' had a book with a royalty 
                    ' greater than 30

                    ' rollback the transaction and leave the
                    ' contracts as there were
                    cmd.CommandText = "rollback transaction"
                    cmd.ExecuteNonQuery()
                End If
            Catch e As Exception
                ' do nothing, means contract was not part of the update statement
            End Try
        End If
 End Sub
End Class
