trgtest.cs

来自「< SQL Server2005程序设计>」· CS 代码 · 共 56 行

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


public partial class Triggers
{
    public static void trgUpdateContact()
    {
        SqlTriggerContext TriggerContext = SqlContext.TriggerContext;
        String OldName = String.Empty;
        String NewName = String.Empty;
        String OldDate = String.Empty;
        String NewDate = String.Empty;
        SqlConnection conn = new SqlConnection("context connection=true");
        SqlCommand cmOld = new SqlCommand("SELECT FirstName, ModifiedDate from DELETED", conn);
        SqlCommand cmNew = new SqlCommand("SELECT FirstName, ModifiedDate from INSERTED", conn);
        conn.Open();
        SqlDataReader drOld = cmOld.ExecuteReader();
        if (drOld.Read())
        {
            OldName = (string)drOld[0];
            OldDate = drOld[1].ToString();
        }
        drOld.Close();
        SqlDataReader drNew = cmNew.ExecuteReader();
        if (drNew.Read())
        {
            NewName = (string)drNew[0];
            NewDate = drNew[1].ToString();
        }
        drNew.Close();
        conn.Close();
        SqlContext.Pipe.Send("Old Value of FirstName:" + OldName);
        SqlContext.Pipe.Send("New Value of FirstName:" + NewName);
        SqlContext.Pipe.Send("Old Value of ModifiedDate:" + OldDate);
        SqlContext.Pipe.Send("New Value of ModifiedDate:" + NewDate);
        for (int i = 0; i <= TriggerContext.ColumnCount - 1; i++)
        {
            SqlContext.Pipe.Send("Column " + i.ToString() + ": " + TriggerContext.IsUpdatedColumn(i).ToString());
        }
        
    }

    public static void trgCreateTable()
    {
        SqlTriggerContext TriggerContext = SqlContext.TriggerContext;
        if (!(TriggerContext.EventData == null))
        {
            SqlContext.Pipe.Send("Event Data: " + TriggerContext.EventData.Value.ToString());
        }
    }

}