using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;


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 static void AddToObsolete()
	{
		// get the trigger context so you can tell what is
		// going on
		SqlTriggerContext tc = SqlContext.TriggerContext;

		// this trigger is for deletes
		if (tc.TriggerAction == TriggerAction.Delete)
		{
			// Make a command
			SqlConnection conn = new SqlConnection("Context Connection=true");
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			conn.Open();

			// make a command the inserts the deleted row into
			// the obsoletejobs table
			cmd.CommandText = "insert into obsoletejobs select * from deleted";

			// move the rows
			int rowsAffected = (int)cmd.ExecuteNonQuery();

			//SqlContext.GetPipe().Send(rowsAffected.ToString() + " inserted into obsoletejobs");
			if (rowsAffected == 0)
			{
				cmd.CommandText = "rollback transaction";
				SqlContext.Pipe.ExecuteAndSend(cmd);
			}
		}
	}
	// 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 static void AuthorCheckContract()
	{
		// get the trigger context for later
		SqlTriggerContext tc = SqlContext.TriggerContext;

		// this is used on for updates
		if (tc.TriggerAction == TriggerAction.Update)
		{
			SqlConnection conn = new SqlConnection("Context Connection=true");
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			conn.Open();

			// 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 ((int)cmd.ExecuteScalar() > 0)
				{
					// 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. This produces
					// Msg 3609, Level 16, State 1, Line 1
                    // The transaction ended in the trigger. The batch has been aborted.
					//cmd.CommandText = "rollback transaction";
					//cmd.ExecuteNonQuery();

					// this works too, same error
					System.Transactions.Transaction.Current.Rollback();
				}
			}
			catch (Exception e)
			{
				// do nothing, means contract was not part of the update statement
			}
		}
	}
}
