testtriggers.cs

计算机考试管理系统 包括模拟测试

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Text;

public partial class Triggers
{
    [Microsoft.SqlServer.Server.SqlTrigger (Name="OnChangeSQuestionAnswer", Target="SQuestion", Event="FOR UPDATE")]
    public static void OnChangeSQuestionAnswer()
    {
        if (SqlContext.TriggerContext.IsUpdatedColumn(6))
        {
            using (SqlConnection conn1 = new SqlConnection())
            {
                conn1.ConnectionString = "context connection=true";
                SqlCommand cmd1 = new SqlCommand("SELECT [Number] FROM deleted", conn1);
                conn1.Open();
                int iNum = (int)cmd1.ExecuteScalar();
                cmd1.CommandText = "SELECT DISTINCT [TestID] FROM [TestItem] WHERE [QuestionType]=0 AND [QuestionNumber]=" + iNum;
                DataTable table1 = new DataTable();
                SqlDataReader reader1 = cmd1.ExecuteReader();
                StringBuilder sb1 = new StringBuilder();
                while (reader1.Read())
                {
                    sb1.Append(reader1[0]);
                    sb1.Append(";");
                }
                reader1.Close();
                SqlContext.Pipe.Send("以下编号的测试中有试题标准答案被修改:" + sb1.ToString());
                cmd1.CommandText = "SELECT DISTINCT [TestPaper].[ID] FROM [TestPaper], [TestItem] WHERE [TestPaper].[TestID] = [TestItem].[TestID] AND [TestItem].[QuestionType]=0 AND [TestItem].[QuestionNumber]=" + iNum;
                reader1 = cmd1.ExecuteReader();
                StringBuilder sb2 = new StringBuilder();
                while (reader1.Read())
                {
                    sb2.Append(reader1[0]);
                    sb2.Append(";");
                }
                reader1.Close();
                SqlContext.Pipe.Send("以下编号的答卷中有试题标准答案被修改:" + sb2.ToString());
            }
        }
    }

    [Microsoft.SqlServer.Server.SqlTrigger(Name = "OnChangeMQuestionAnswer", Target = "MQuestion", Event = "FOR UPDATE")]
    public static void OnChangeMQuestionAnswer()
    {
        if (SqlContext.TriggerContext.IsUpdatedColumn(6))
        {
            using (SqlConnection conn1 = new SqlConnection())
            {
                conn1.ConnectionString = "context connection=true";
                SqlCommand cmd1 = new SqlCommand("SELECT [Number] FROM deleted", conn1);
                conn1.Open();
                int iNum = (int)cmd1.ExecuteScalar();
                cmd1.CommandText = "SELECT DISTINCT [TestID] FROM [TestItem] WHERE [QuestionType]=1 AND [QuestionNumber]=" + iNum;
                DataTable table1 = new DataTable();
                SqlDataReader reader1 = cmd1.ExecuteReader();
                StringBuilder sb1 = new StringBuilder();
                while (reader1.Read())
                {
                    sb1.Append(reader1[0]);
                    sb1.Append(";");
                }
                reader1.Close();
                SqlContext.Pipe.Send("以下编号的测试中有试题标准答案被修改:" + sb1.ToString());
                cmd1.CommandText = "SELECT DISTINCT [TestPaper].[ID] FROM [TestPaper], [TestItem] WHERE [TestPaper].[TestID] = [TestItem].[TestID] AND [TestItem].[QuestionType]=1 AND [TestItem].[QuestionNumber]=" + iNum;
                reader1 = cmd1.ExecuteReader();
                StringBuilder sb2 = new StringBuilder();
                while (reader1.Read())
                {
                    sb2.Append(reader1[0]);
                    sb2.Append(";");
                }
                reader1.Close();
                SqlContext.Pipe.Send("以下编号的答卷中有试题标准答案被修改:" + sb2.ToString());
            }
        }
    }
}