trgtest.cs

< SQL Server2005程序设计>

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


public partial class Triggers
{
    // Because table Contact is contained within 'Person' schema, SqlTrigger attribute will not work
    // the SqlTrigger attribute may only be used with objects in the 'dbo' schema
    // Code in PostDeployScript.sql offers T-SQL work-around for this shortcoming
    // Code in PreDeployScript.sql drops trigger for prior to subsequent deployments
    //[SqlTrigger(Target="Person.Contact", Event="for UPDATE")]
    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());
        }
        
    }

    [SqlTrigger(Target = "DATABASE", Event = "FOR CREATE_TABLE")]
    public static void trgCreateTable()
    {
        SqlTriggerContext TriggerContext = SqlContext.TriggerContext;
        if (!(TriggerContext.EventData == null))
        {
            SqlContext.Pipe.Send("Event Data: " + TriggerContext.EventData.Value.ToString());
        }
    }

}