public static void ColsUpdDemo()
{
SqlTriggerContext sqlTrg = SqlContext.TriggerContext;
EventLog ev = new EventLog("Application", ".", "ColsUpdated");
ev.WriteEntry("Starting");
for (int i = 0; i < sqlTrg.ColumnCount - 1; i++)
{
ev.WriteEntry(string.Format("Column {0}, updated: {1}", i,
sqlTrg.IsUpdatedColumn(i).ToString()));
}
SqlPipe pipeSql = SqlContext.Pipe;
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
string sql = "SELECT * FROM inserted";
SqlCommand sqlComm = new SqlCommand(sql, cn);
SqlDataReader dr = sqlComm.ExecuteReader();
dr.Read();
string col1 = dr.GetString(1);
ev.WriteEntry(string.Format("Inserted {0}, {1}", dr.FieldCount, col1));
dr.Close();
}
}
public static void trICStockX9FirstCheck()
{
string billCode = string.Empty;
long interID = 0L;
int iROB = 1;
long lTransType = 0L;
int insFFirstChecker, delFFirstChecker;
bool bCheckTriggerCol = false;
string strX9No = string.Empty;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
initialConfig();
//pipe.Send(System.Enum.GetName(typeof(LOG_TYPE), ConfigLogType));
#region 只有出入库单一级审核人变化时才触发
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
reader = command.ExecuteReader();
reader.Read();
//虽reader不为null，但HasRows为false时，调用reader方法或属性会出现NullReferenceException异常。
if (reader == null || reader.HasRows == false)
{
return;
}
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
try
{
if (triggContext.IsUpdatedColumn(columnNumber) && reader.GetName(columnNumber).Equals("FMultiCheckLevel1", StringComparison.InvariantCultureIgnoreCase))
{
bCheckTriggerCol = true;
}
}
catch (NullReferenceException ex)
{
continue;
}
//pipe.Send("Updated column "
// + reader.GetName(columnNumber) + "? "
// + triggContext.IsUpdatedColumn(columnNumber).ToString());
}
pipe.Send(bCheckTriggerCol.ToString());
if (!bCheckTriggerCol)
{
return;
}
billCode = (string)reader["FBillNo"].ToString();
interID = Convert.ToInt64(reader["FInterID"].ToString());
iROB = Convert.ToInt32(reader["FROB"].ToString());
lTransType = Convert.ToInt64(reader["FTranType"].ToString());
strX9No = (string)reader["X9NO"].ToString();
insFFirstChecker = Convert.ToInt32(reader["FMultiCheckLevel1"] == null ||
string.IsNullOrEmpty(reader["FMultiCheckLevel1"].ToString()) ? "0" : reader["FMultiCheckLevel1"].ToString());
reader.Close();
}
#endregion
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT * FROM DELETED;", connection);
reader = command.ExecuteReader();
reader.Read();
if (reader == null || reader.HasRows == false)
{
return;
}
delFFirstChecker = Convert.ToInt32(reader["FMultiCheckLevel1"] == null ||
string.IsNullOrEmpty(reader["FMultiCheckLevel1"].ToString()) ? "0" : reader["FMultiCheckLevel1"].ToString());
reader.Close();
}
#region 一级审核时
if (delFFirstChecker == 0 && insFFirstChecker > 0)
{
K3DataParaInfo docInfo = new K3DataParaInfo()
{
BillCode = billCode,
InterID = interID,
EntryID = 0,
TransType = lTransType,
ROB = iROB,
CurrentUser = "******",
X9BillType = ContrastK3TransType(lTransType, iROB),
EventName = "FirstApprovedAfter",
Data = strX9No,
};
if (!basicHandle(docInfo))
{
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
//throw new Exception("X9系统异常");
return;
}
}
}
#endregion
#region 一级反审核时
if (delFFirstChecker > 0 && insFFirstChecker == 0)
{
K3DataParaInfo docInfo = new K3DataParaInfo()
{
BillCode = billCode,
InterID = interID,
EntryID = 0,
TransType = lTransType,
ROB = iROB,
CurrentUser = "******",
X9BillType = ContrastK3TransType(lTransType, iROB),
EventName = "UnFirstApprovedAfter",
Data = strX9No,
};
if (!basicHandle(docInfo))
{
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
return;
}
}
}
#endregion
}
public static void trOMOrderEntryClosed()
{
string billCode = string.Empty;
long interID = 0L;
int entryID = 0;
int insFClosed, delFClosed;
bool bCheckTriggerCol = false;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
initialConfig();
#region 只有委外订单行关闭时触发
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
reader = command.ExecuteReader();
reader.Read();
if (reader == null || reader.HasRows == false)
{
return;
}
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
if (reader.GetName(columnNumber).Equals("FMrpClosed", StringComparison.InvariantCultureIgnoreCase) && triggContext.IsUpdatedColumn(columnNumber))
{
bCheckTriggerCol = true;
}
//pipe.Send("Updated column "
// + reader.GetName(columnNumber) + "? "
// + triggContext.IsUpdatedColumn(columnNumber).ToString());
}
pipe.Send(bCheckTriggerCol.ToString());
if (!bCheckTriggerCol)
{
return;
}
billCode = string.Empty;
interID = Convert.ToInt64(reader["FInterID"].ToString());
entryID = Convert.ToInt32(reader["FEntryID"].ToString());
insFClosed = Convert.ToInt32(reader["FMrpClosed"].ToString());
reader.Close();
}
#endregion
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT * FROM DELETED;", connection);
reader = command.ExecuteReader();
reader.Read();
if (reader == null || reader.HasRows == false)
{
return;
}
delFClosed = Convert.ToInt32(reader["FMrpClosed"].ToString());
reader.Close();
}
//行关闭时
if (insFClosed == 1 && delFClosed == 0)
{
K3DataParaInfo docInfo = new K3DataParaInfo()
{
BillCode = billCode,
InterID = interID,
EntryID = entryID,
TransType = 1007105,
ROB = 0,
CurrentUser = "******",
X9BillType = 3,
EventName = "EntryClosedAfter",
Data = "",
};
if (!basicHandle(docInfo))
{
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
return;
}
}
}
//反关闭时
if (delFClosed == 1 && insFClosed == 0)
{
K3DataParaInfo docInfo = new K3DataParaInfo()
{
BillCode = billCode,
InterID = interID,
EntryID = entryID,
TransType = 1007105,
ROB = 0,
CurrentUser = "******",
X9BillType = 3,
EventName = "UnEntryClosedAfter",
Data = "",
};
if (!basicHandle(docInfo))
{
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
return;
}
}
}
}
public static void trPPBOMX9Validate()
{
string billCode = string.Empty;
long interID = 0L;
int insFStatus, delFStatus;
int typeID = 0;
bool bCheckTriggerCol = false;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
initialConfig();
#region 只有生产投料单状态变化时才触发
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
reader = command.ExecuteReader();
reader.Read();
if (reader == null || reader.HasRows == false)
{
return;
}
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
if (reader.GetName(columnNumber).Equals("FStatus", StringComparison.InvariantCultureIgnoreCase) && triggContext.IsUpdatedColumn(columnNumber))
{
bCheckTriggerCol = true;
}
//pipe.Send("Updated column "
// + reader.GetName(columnNumber) + "? "
// + triggContext.IsUpdatedColumn(columnNumber).ToString());
}
pipe.Send(bCheckTriggerCol.ToString());
if (!bCheckTriggerCol)
{
return;
}
billCode = (string)reader["FBillNo"].ToString();
interID = Convert.ToInt64(reader["FInterID"].ToString());
insFStatus = Convert.ToInt32(reader["FStatus"].ToString());
typeID = Convert.ToInt32(reader["FType"].ToString());
reader.Close();
}
#endregion
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT * FROM DELETED;", connection);
reader = command.ExecuteReader();
reader.Read();
if (reader == null || reader.HasRows == false)
{
return;
}
delFStatus = Convert.ToInt32(reader["FStatus"].ToString());
reader.Close();
}
//审核时
if (insFStatus == 1 && delFStatus == 0)
{
K3DataParaInfo docInfo = new K3DataParaInfo()
{
BillCode = billCode,
InterID = interID,
EntryID = 0,
TransType = 88,
ROB = 1,
CurrentUser = "******",
X9BillType = typeID == 1067 ? 23 : 7,//根据typeID区分是工单投料，还是委外投料。
EventName = "ApprovedAfter",
Data = "",
};
if (!basicHandle(docInfo))
{
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
return;
}
}
}
//弃审时
if (delFStatus == 1 && insFStatus == 0)
{
K3DataParaInfo docInfo = new K3DataParaInfo()
{
BillCode = billCode,
InterID = interID,
EntryID = 0,
TransType = 88,
ROB = 1,
CurrentUser = "******",
X9BillType = typeID == 1067 ? 23 : 7,//根据typeID区分是工单投料，还是委外投料。
EventName = "UnApprovedAfter",
Data = "",
};
if (!basicHandle(docInfo))
{
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
return;
}
}
}
}
public static void trICMOX9Validate()
{
string billCode = string.Empty;
long interID = 0L;
int insFStatus, delFStatus;
string strInsCommitDate = string.Empty, strDelCommitDate = string.Empty;
bool bCheckTriggerCol = false;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
initialConfig();
//pipe.Send(System.Enum.GetName(typeof(LOG_TYPE), ConfigLogType));
#region 只有生产任务单状态变化时才触发
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
reader = command.ExecuteReader();
reader.Read();
//虽reader不为null，但HasRows为false时，调用reader方法或属性会出现NullReferenceException异常。
if (reader == null || reader.HasRows == false)
{
return;
}
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
try
{
if (triggContext.IsUpdatedColumn(columnNumber) && reader.GetName(columnNumber).Equals("FStatus", StringComparison.InvariantCultureIgnoreCase))
{
bCheckTriggerCol = true;
}
}
catch (NullReferenceException ex) {
continue;
}
//pipe.Send("Updated column "
// + reader.GetName(columnNumber) + "? "
// + triggContext.IsUpdatedColumn(columnNumber).ToString());
}
pipe.Send(bCheckTriggerCol.ToString());
if (!bCheckTriggerCol)
{
return;
}
billCode = (string)reader["FBillNo"].ToString();
interID = Convert.ToInt64(reader["FInterID"].ToString());
insFStatus = Convert.ToInt32(reader["FStatus"].ToString());
strInsCommitDate = reader["FCommitDate"].ToString();//下达日期
reader.Close();
}
#endregion
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT * FROM DELETED;", connection);
reader = command.ExecuteReader();
reader.Read();
if (reader == null || reader.HasRows == false)
{
return;
}
delFStatus = Convert.ToInt32(reader["FStatus"].ToString());
strDelCommitDate = reader["FCommitDate"].ToString();//下达日期
reader.Close();
}
#region 达时
//不再用insFStatus == 1 && delFStatus != 1判断是否下达，因为结案会出现更新ICShop_SubcOut表的FStatus,却触发IcMo触发器的bug。
/*
*
*
* */
if (string.IsNullOrEmpty(strDelCommitDate) && !string.IsNullOrEmpty(strInsCommitDate))
{
K3DataParaInfo docInfo = new K3DataParaInfo()
{
BillCode = billCode,
InterID = interID,
EntryID = 0,
TransType = 85,
ROB = 1,
CurrentUser = "******",
X9BillType = 5,
EventName = "ApprovedAfter",
Data = "",
};
if (!basicHandle(docInfo))
{
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
//throw new Exception("X9系统异常");
return;
}
}
}
#endregion
#region 反下达时
if (string.IsNullOrEmpty(strInsCommitDate) && !string.IsNullOrEmpty(strDelCommitDate))
{
K3DataParaInfo docInfo = new K3DataParaInfo()
{
BillCode = billCode,
InterID = interID,
EntryID = 0,
TransType = 85,
ROB = 1,
CurrentUser = "******",
X9BillType = 5,
EventName = "UnApprovedAfter",
Data = "",
};
if (!basicHandle(docInfo))
{
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
return;
}
}
}
#endregion
#region 结案时
if (insFStatus == 3 && delFStatus != 3)
{
K3DataParaInfo docInfo = new K3DataParaInfo()
{
BillCode = billCode,
InterID = interID,
EntryID = 0,
TransType = 85,
ROB = 1,
CurrentUser = "******",
X9BillType = 5,
EventName = "ClosedAfter",
Data = "",
};
if (!basicHandle(docInfo))
{
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
return;
}
}
}
#endregion
//反结案时
if (delFStatus == 3 && insFStatus != 3)
{
K3DataParaInfo docInfo = new K3DataParaInfo()
{
BillCode = billCode,
InterID = interID,
EntryID = 0,
TransType = 85,
ROB = 1,
CurrentUser = "******",
X9BillType = 5,
EventName = "UnClosedAfter",
Data = "",
};
if (!basicHandle(docInfo))
{
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
return;
}
}
}
}
public static void ClientsCfsTrigger()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
var TableName = "Clients";
var UpdateDate = DateTime.Now;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
if (triggContext.TriggerAction == TriggerAction.Update)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlPipe sqlP = SqlContext.Pipe;
var schemaTable = GetSchema(conn);
using (var auditAdapter = new SqlDataAdapter("select top 0 * from History", conn))
{
var auditTable = new DataTable();
auditAdapter.Fill(auditTable);
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("Select inserted.ClientId,");
for (int i = 0; i < triggContext.ColumnCount; i++)
{
if (triggContext.IsUpdatedColumn(i))
{
var name = schemaTable.Columns[i].ColumnName;
if (!ignoreCfsColumn(name))
{
sb.Append("Inserted.").Append(name)
.Append(" as Inserted_").Append(name).Append(",");
sb.Append("Deleted.").Append(name)
.Append(" as Deleted_").Append(name).Append(",");
}
}
}
sb.Append("inserted.UpdatedAt, inserted.UpdatedById as UpdatedBy ");
sb.Append("from Inserted join Deleted on inserted.id = deleted.id");
var changeSql = new SqlCommand();
changeSql.CommandText = sb.ToString();
changeSql.Connection = conn;
using (var reader = changeSql.ExecuteReader())
{
while (reader.Read())
{
for (int i = 0; i < triggContext.ColumnCount; i++)
{
if (triggContext.IsUpdatedColumn(i))
{
string colName = schemaTable.Columns[i].ColumnName;
if (!ignoreCfsColumn(colName))
{
var row = auditTable.NewRow();
var oldValue = reader.GetValue(reader.GetOrdinal("Deleted_" + colName));
var newValue = reader.GetValue(reader.GetOrdinal("Inserted_" + colName));
var changed = Changed(oldValue, newValue, schemaTable.Columns[i].DataType);
if (changed)
{
row["OldValue"] = oldValue;
row["NewValue"] = newValue;
row["ReferenceId"] = reader.GetValue(reader.GetOrdinal("ClientId"));
row["UpdateDate"] = UpdateDate;
row["UpdatedBy"] = reader.GetValue(reader.GetOrdinal("UpdatedBy"));
row["TableName"] = TableName;
row["FieldName"] = "CFS_" + colName;
auditTable.Rows.Add(row);
}
}
}
}
}
if (!reader.IsClosed)
{
reader.Close();
}
}
var commandBuilder = new SqlCommandBuilder(auditAdapter);
auditAdapter.Update(auditTable);
}
sqlP.Send("exiting trigger");
}
}
}
public static void SqlTrigger1()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
SqlCommand command;
SqlDataReader reader;
SqlPipe pipe = SqlContext.Pipe;
String uid;
String customerName;
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;", conn);
reader = command.ExecuteReader();
reader.Read();
uid = reader[0].ToString();
customerName = (string)reader[1];
pipe.Send(@"You updated: '" + uid + @"' - '"
+ customerName + @"'");
reader.Close();
SendMessage(uid, customerName, "insert");
}
break;
case TriggerAction.Update:
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
reader = command.ExecuteReader();
reader.Read();
uid = reader[0].ToString();
customerName = (string)reader[1];
pipe.Send(@"You updated: '" + uid + @"' - '"
+ customerName + @"'");
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
pipe.Send("Updated column "
+ reader.GetName(columnNumber) + "? "
+ triggContext.IsUpdatedColumn(columnNumber).ToString());
}
reader.Close();
SendMessage(uid, customerName, "update");
}
break;
case TriggerAction.Delete:
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM DELETED;",
connection);
reader = command.ExecuteReader();
if (reader.HasRows)
{
pipe.Send(@"You deleted the following rows:");
while (reader.Read())
{
uid = reader.GetInt32(0).ToString();
customerName = reader.GetString(1);
pipe.Send(@"'" + reader.GetInt32(0)
+ @"', '" + reader.GetString(1) + @"'");
SendMessage(uid, customerName, "delete");
}
reader.Close();
//alternately, to just send a tabular resultset back:
//pipe.ExecuteAndSend(command);
}
else
{
pipe.Send("No rows affected.");
}
}
break;
}
}
public static void ctr_Contact_iu()
{
//get trigger context to access trigger related features
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
string action = triggerContext.TriggerAction.ToString();
string sObj = triggerContext.ToString();
SqlContext.Pipe.Send("Trigger " + sObj + " FIRED on " + action);
string s = "";
int iCount = triggerContext.ColumnCount;
for (int i = 0; i < iCount; i++)
{
if (triggerContext.IsUpdatedColumn(i) == true)
{
s = s + i.ToString() + ", ";
}
}
SqlContext.Pipe.Send("Trigger updated columns: " + s);
//test validity of email
using (SqlConnection con = new SqlConnection("context connection = true"))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
if (triggerContext.TriggerAction == TriggerAction.Insert)
{
cmd.CommandText = "SELECT * FROM INSERTED";
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
string email = rdr.GetValue(5).ToString();
if (Regex.IsMatch(email, @"^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$") == false)
{
SqlContext.Pipe.Send("Not a valid email!");
//Transaction.Current.Rollback();
}
}
}
}
}
}
//list of updated column names
using (SqlConnection con = new SqlConnection("context connection = true"))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT * FROM INSERTED";
using (SqlDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();
if (triggerContext.TriggerAction == TriggerAction.Update)
{
string sCol = "Updated columns: ";
for (int icol = 0; icol < triggerContext.ColumnCount; icol++)
{
if (triggerContext.IsUpdatedColumn(icol) == true)
{
sCol = sCol + rdr.GetName(icol);
}
}
SqlContext.Pipe.Send(sCol);
}
}
}
}
}
public static void ReplicaToPg()
{
SqlCommand command;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlDataReader reader;
// Retrieve the connection that the trigger is using
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand cmdTableName =
new SqlCommand(
"SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'",
connection);
var tableName = cmdTableName.ExecuteScalar().ToString();
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
{
command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
StringBuilder sb = new StringBuilder($"INSERT INTO {tableName} (");
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
if (columnNumber > 0)
{
sb.Append(", ");
}
sb.Append(reader.GetName(columnNumber));
}
sb.Append(") VALUES (");
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
if (columnNumber > 0)
{
sb.Append(", ");
}
//sb.Append("?");
AppendValue(sb, reader, columnNumber);
}
sb.Append(")");
//using (var cmd = new OdbcCommand(sb.ToString()))
//{
// for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
// {
// var param = new OdbcParameter();
// param.OdbcType = (OdbcType)Enum.Parse(typeof(OdbcType), reader.GetDataTypeName(columnNumber), true);
// param.Value = reader.GetSqlValue(columnNumber);
// cmd.Parameters.Add(param);
// }
// ExecutePgsql(cmd);
//}
WebClient client = new WebClient();
client.UploadString("http://*****:*****@"SELECT * FROM INSERTED;", connection);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
StringBuilder sb = new StringBuilder($"UPDATE {tableName} SET ");
int idx = 0;
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
if (!triggContext.IsUpdatedColumn(columnNumber))
{
continue;
}
if (idx > 0)
{
sb.Append(", ");
}
sb.Append($"{reader.GetName(columnNumber)} = ");
AppendValue(sb, reader, columnNumber);
idx++;
}
sb.Append($" WHERE {reader.GetName(0)} = {reader.GetSqlValue(0)};");
WebClient client = new WebClient();
client.UploadString("http://*****:*****@"SELECT * FROM DELETED;", connection);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
StringBuilder sb = new StringBuilder($"DELETE FROM {tableName}");
sb.Append($" WHERE {reader.GetName(0)} = {reader.GetSqlValue(0)};");
WebClient client = new WebClient();
client.UploadString("http://localhost:8080/", sb.ToString());
}
}
break;
}
}
}
}
public static void trICMOX9Validate()
{
string billCode = string.Empty;
long interID = 0L;
int insFStatus, delFStatus;
bool bCheckTriggerCol = false;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
//WindowsIdentity windowsIdentity = null;
//WindowsImpersonationContext userImpersonated = null;
//pipe.Send("1");
//windowsIdentity = SqlContext.WindowsIdentity;
//pipe.Send(windowsIdentity == null ? "null" : windowsIdentity.AuthenticationType);
//// Switch the context to local service account user (a domain user)
//// by getting its identity in order to call the web service
//userImpersonated = windowsIdentity.Impersonate();
//pipe.Send("2");
//if (userImpersonated != null)
//{
// // Create the instance of a web service to be called. // Remember this is not actual // CAAT search web service but a similar fake web service just for testing.
// pipe.Send("create service");
// LogInfoHelp.Log("Begin Login", LOG_TYPE.LOG_DEBUG);
// userImpersonated.Undo();
//}
//else
//{
// pipe.Send("userImpersonated is null");
//}
//只有生产任务单状态变化时才触发
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
reader = command.ExecuteReader();
reader.Read();
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
if (reader.GetName(columnNumber).Equals("FStatus", StringComparison.InvariantCultureIgnoreCase) && triggContext.IsUpdatedColumn(columnNumber))
{
bCheckTriggerCol = true;
}
//pipe.Send("Updated column "
// + reader.GetName(columnNumber) + "? "
// + triggContext.IsUpdatedColumn(columnNumber).ToString());
}
pipe.Send(bCheckTriggerCol.ToString());
if (!bCheckTriggerCol)
{
return;
}
pipe.Send("FBillNo");
billCode = (string)reader["FBillNo"].ToString();
interID = Convert.ToInt64(reader["FInterID"].ToString());
pipe.Send("FStatus");
pipe.Send(reader["FStatus"].ToString());
insFStatus = Convert.ToInt32(reader["FStatus"].ToString());
reader.Close();
}
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT * FROM DELETED;", connection);
reader = command.ExecuteReader();
reader.Read();
delFStatus = Convert.ToInt32(reader["FStatus"].ToString());
reader.Close();
}
//结案时
if (insFStatus == 3 && delFStatus != 3)
{
pipe.Send("if (insFStatus == 3 && delFStatus != 3)");
K3DataParaInfo docInfo = new K3DataParaInfo()
{
BillCode = billCode,
InterID = interID,
TransType = 85,
ROB = 1,
CurrentUser = "******",
X9BillType = 5,
EventName = "ClosedBefore",
Data = "",
};
if (!icmoCloseHandle(docInfo))
{
pipe.Send("!icmoCloseHandle(docInfo)");
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
return;
}
}
}
//反结案时
if (delFStatus == 3 && insFStatus != 3)
{
pipe.Send("delFStatus == 3 && insFStatus != 3");
K3DataParaInfo docInfo = new K3DataParaInfo()
{
BillCode = billCode,
InterID = interID,
TransType = 85,
ROB = 1,
CurrentUser = "******",
X9BillType = 5,
EventName = "UnClosedBefore",
Data = "",
};
if (!icmoCloseHandle(docInfo))
{
pipe.Send("!icmoCloseHandle(docInfo)");
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
return;
}
}
}
}
