public static void UserNameAudit()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlParameter userNameParam = new SqlParameter("@username", System.Data.SqlDbType.NVarChar);
if (triggContext.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand sqlComm = new SqlCommand();
SqlPipe sqlP = SqlContext.Pipe;
sqlComm.Connection = conn;
sqlComm.CommandText = "SELECT UserName from INSERTED";
userNameParam.Value = sqlComm.ExecuteScalar().ToString();
if (IsEMailAddress(userNameParam.Value.ToString()))
{
sqlComm.CommandText = "INSERT UsersAudit (UserName) VALUES(@username)";
sqlComm.Parameters.Add(userNameParam);
sqlP.Send(sqlComm.CommandText);
sqlP.ExecuteAndSend(sqlComm);
}
}
}
}
public static void AuditTrigger()
{
ArrayList auditActions = ReadMonitoringActions();
String[] arrActions = (String[])auditActions.ToArray(typeof(string));
SqlTriggerContext trgContext = SqlContext.TriggerContext;
for (int i = 0; i < arrActions.Length; i += 3)
{
DateTime fromDate = Convert.ToDateTime(arrActions[i + 1]);
DateTime toDate = Convert.ToDateTime(arrActions[i + 2]);
if (arrActions[i] == trgContext.TriggerAction.ToString() &&
fromDate.ToShortTimeString().CompareTo(DateTime.Now.
ToShortTimeString()) < 0 &&
toDate.ToShortTimeString().CompareTo(DateTime.Now.
ToShortTimeString()) > 0)
{
string evData = trgContext.EventData.Value;
SqlPipe pipeSql = SqlContext.Pipe;
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
string sql = "msdb.dbo.sp_send_dbmail " +
"@profile_name = 'default profile'," +
"@recipients = '*****@*****.**'," +
"@body = '" + trgContext.TriggerAction.ToString() +
" is happening during core hours.' ," +
"@subject = 'Trigger Action occurring'";
SqlCommand sqlComm = new SqlCommand(sql, cn);
pipeSql.Send(sqlComm.CommandText);
pipeSql.ExecuteAndSend(sqlComm);
}
}
}
}
public static void greetingsTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
if (triggContext.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = conn;
SqlPipe sqlP = SqlContext.Pipe;
sqlComm.CommandText = "SELECT name from INSERTED";
SqlParameter greeting = new SqlParameter("@greeting", System.Data.SqlDbType.NVarChar);
sqlComm.Parameters.Add(greeting);
greeting.Value = "Hello " + sqlComm.ExecuteScalar().ToString();
sqlComm.CommandText = "INSERT triggered_greetings (greeting) VALUES (@greeting)";
sqlP.Send(sqlComm.CommandText);
sqlP.ExecuteAndSend(sqlComm);
}
}
}
public static void GetInvoiceReport()
{
// open the Connection object for the context
SqlConnection connection =
new SqlConnection("Context connection=true");
connection.Open();
// create a string that defines the select statement
string selectStatement =
"SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal " +
"FROM Invoices JOIN Vendors " +
"ON Invoices.VendorID = Vendors.VendorID " +
"WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0";
// create the Command object
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
// use the SqlPipe object to return the data
SqlPipe pipe = SqlContext.Pipe;
pipe.ExecuteAndSend(selectCommand);
// close the Connection object
connection.Close();
}
public static void UserNameAudit()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlParameter userName = new SqlParameter("@username", SqlDbType.NVarChar);
if (triggContext.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection connect = new SqlConnection("context connection=true"))
{
connect.Open();
SqlCommand _command = new SqlCommand();
SqlPipe _pipe = SqlContext.Pipe;
_command.Connection = connect;
_command.CommandText = "SELECT UserName from INSERTED";
userName.Value = _command.ExecuteScalar().ToString();
if (IsEMailAddress(userName.Value.ToString()))
{
_command.Parameters.Add(userName);
_command.CommandText = "INSERT UsersAudit(UserName) VALUES(@username)";
_pipe.Send(_command.CommandText);
_pipe.ExecuteAndSend(_command);
}
}
}
}
public static void Trigger1()
{
SqlPipe Pipe = SqlContext.Pipe;
using (SqlConnection Connection = new SqlConnection("context connection = true"))
{
Connection.Open();
SqlCommand Command = new SqlCommand(@"
print 'Эти поля были удалены'
select * from deleted", Connection);
Pipe.ExecuteAndSend(Command);
}
}
public static void Trigger2()
{
SqlPipe Pipe = SqlContext.Pipe;
using (SqlConnection Connection = new SqlConnection("context connection = true"))
{
Connection.Open();
SqlCommand Command = new SqlCommand(@"
SET NOCOUNT ON;
if (select RegisterDate from inserted) > getdate()
rollback
print'Вы не можете вставлять пользователя, который может быть в будущем зарегестрируется!!'" , Connection);
Pipe.ExecuteAndSend(Command);
}
}
public static void ValidateYear()
{
SqlConnection conn =
new SqlConnection("context connection=true");
//Define the query
string sql =
"SELECT COUNT(*) " +
"FROM INSERTED " +
"WHERE YEAR(ModifiedDate) <> 2005";
SqlCommand comm =
new SqlCommand(sql, conn);
//Open the connection
conn.Open();
//Get the number of bad rows
int numBadRows = (int)comm.ExecuteScalar();
if (numBadRows > 0)
{
//Get the SqlPipe
SqlPipe pipe = SqlContext.Pipe;
//Rollback and raise an error
comm.CommandText =
"RAISERROR('Modified Date must fall in 2005', 11, 1)";
//Send the error
try
{
pipe.ExecuteAndSend(comm);
}
catch
{
//do nothing
}
System.Transactions.Transaction.Current.Rollback();
}
//Close the connection
conn.Close();
}
public static void StoredProcedure2(SqlString Name, SqlString Project, SqlDateTime RegisterDate)
{
SqlPipe Pipe = SqlContext.Pipe;
using (SqlConnection Connection = new SqlConnection("context connection = true"))
{
Connection.Open();
SqlCommand Command = new SqlCommand(@"
DECLARE @max_id INT
SELECT @max_id = Max(UserID)
FROM tblUsers
INSERT tblUsers (UserID, UserName, Team, Country, Project, RegisterDate, AvgScorePerDay, AllScore)
VALUES(@max_id + 1, @Name, NULL, NULL, @Project, @RegisterDate, 0, 0)" , Connection);
Command.Parameters.AddWithValue("@Name", Name);
Command.Parameters.AddWithValue("@Project", Project);
Command.Parameters.AddWithValue("@RegisterDate", RegisterDate);
Pipe.ExecuteAndSend(Command);
}
}
public static void spGetInvoiceReport()
{
// Open the Connection object for the context
SqlConnection con = new SqlConnection("Context connection=true");
con.Open();
//create a string
string selectStatement = "select VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal " +
"from Vendors join Invoices on Vendors.VendorID = Invoices.VendorID " +
"where InvoiceTotal - CreditTotal - PaymentTotal > 0";
//create a Command Object
SqlCommand selectCommand = new SqlCommand(selectStatement, con); //con is a connection
//Use the SqlPipe
SqlPipe pipe = SqlContext.Pipe;
pipe.ExecuteAndSend(selectCommand);
con.Close();
}
public static void StoredProcedure3(SqlInt32 Year, out SqlMoney YearPaymentCredit)
{
SqlPipe Pipe = SqlContext.Pipe;
using (SqlConnection Connection = new SqlConnection("context connection = true"))
{
Connection.Open();
SqlCommand Command = new SqlCommand(@"
SELECT Sum(PaymentCredit)
FROM tblPayments
GROUP BY Year(PaymentDate)
HAVING Year(PaymentDate) = @Year",
Connection);
Command.Parameters.AddWithValue("@Year", Year);
decimal X = (decimal)Command.ExecuteScalar();
YearPaymentCredit = new SqlMoney(X);
Pipe.ExecuteAndSend(Command);
}
}
public static void StoredProcedure1()
{
SqlPipe Pipe = SqlContext.Pipe;
using (SqlConnection Connection = new SqlConnection("context connection = true"))
{
Connection.Open();
SqlCommand Command = new SqlCommand(@"
IF OBJECT_ID('dbo.tblActiveUsers') IS NOT NULL
DROP TABLE tblActiveUsers
CREATE TABLE tblActiveUsers
(
UserID INT PRIMARY KEY,
UserName VARCHAR (60) NOT NULL,
);
INSERT tblActiveUsers (UserID, UserName)
SELECT tblUsers.UserID, tblUsers.UserName
FROM tblUsers
WHERE tblUsers.AvgScorePerDay > 200.0" , Connection);
Pipe.ExecuteAndSend(Command);
}
}
public static void GetTop10Vendors()
{
SqlConnection connection =
new SqlConnection("Context connection=true");
connection.Open();
string selectStatement =
"SELECT TOP 10 VendorName, SUM(InvoiceTotal) " +
"AS InvoiceTotal " +
"FROM Invoices JOIN Vendors " +
"ON Invoices.VendorID = Vendors.VendorID " +
"GROUP BY VendorName " +
"ORDER BY SUM(InvoiceTotal) DESC";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
SqlPipe pipe = SqlContext.Pipe;
pipe.ExecuteAndSend(selectCommand);
connection.Close();
}
public static void AuditCommon()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlCommand sqlComm = new SqlCommand();
SqlPipe sqlP = SqlContext.Pipe;
DataRow insertedRow;
DataRow deletedRow;
string tableName;
string selectedColumns = "";
string keyColumn = "";
string keyValue = "";
int rowsAffected;
int userID;
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
try
{
conn.Open();
sqlComm.Connection = conn;
////SqlCommand cmd = new SqlCommand("SELECT OBJECT_NAME(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @@SPID AND resource_type = 'OBJECT'", conn);
SqlCommand cmd = new SqlCommand("SELECT * INTO T2AuditTriggerForTerraScan FROM Inserted WHERE 1 = 0; SELECT TOP 1 TableName FROM (SELECT TableName, TotalCount FROM (SELECT OBJECT_NAME(object_id) AS TableName ,COUNT(OBJECT_NAME(object_id)) TotalCount FROM sys.columns O JOIN sys.syscolumns T ON O.[name] = T.[name] WHERE OBJECT_NAME(T.id) = 'T2AuditTriggerForTerraScan' AND OBJECT_NAME(object_id) IN (SELECT OBJECT_NAME(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @@SPID AND resource_type = 'OBJECT') GROUP BY OBJECT_NAME(object_id)) AS ObjectsLocked) T2DerivedAudit WHERE TableName NOT IN('T2AuditTriggerForTerraScan') ORDER BY TotalCount DESC; DROP TABLE T2AuditTriggerForTerraScan;", conn);
tableName = cmd.ExecuteScalar().ToString();
cmd.CommandText = "SELECT C.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK, INFORMATION_SCHEMA.KEY_COLUMN_USAGE C WHERE PK.TABLE_NAME = '" + tableName.Trim() + "' AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND C.TABLE_NAME = PK.TABLE_NAME AND C.CONSTRAINT_NAME = PK.CONSTRAINT_NAME";
if (cmd.ExecuteScalar() != null)
{
keyColumn = cmd.ExecuteScalar().ToString();
}
else
{
if (string.IsNullOrEmpty(keyColumn))
{
cmd.CommandText = "SELECT sys.identity_columns.[name] FROM sys.identity_columns WHERE OBJECT_NAME(object_id) ='" + tableName + "'";
if (cmd.ExecuteScalar() != null)
{
keyColumn = cmd.ExecuteScalar().ToString();
}
}
}
cmd.CommandText = "SELECT ISNULL(UpdatedBy, 0) FROM INSERTED"; //"SELECT UserID FROM tTS_User WHERE Name_Net = SUSER_SNAME()";
if (cmd.ExecuteScalar() != null)
{
userID = Convert.ToInt32(cmd.ExecuteScalar().ToString());
}
else
{
userID = 0;
}
SqlDataAdapter auditAdapter = new SqlDataAdapter("SELECT ColumnName = [name] FROM sys.columns WHERE OBJECT_NAME(object_id) = '" + tableName + "' AND [name] IN(SELECT FieldName FROM tTR_Audit_TableList JOIN tTR_Audit_FieldList ON tTR_Audit_TableList.AuditTableID = tTR_Audit_FieldList.AuditTableID WHERE TableName = '" + tableName + "' AND tTR_Audit_TableList.IsDeleted = 0 AND tTR_Audit_FieldList.IsDeleted = 0) ORDER BY column_id ASC", conn);
DataTable selectedAudit = new DataTable();
auditAdapter.Fill(selectedAudit);
if (selectedAudit.Rows.Count != 0)
{
selectedColumns = "";
for (int totalConfColumns = 0; totalConfColumns < selectedAudit.Rows.Count; totalConfColumns++)
{
selectedColumns = selectedColumns + selectedAudit.Rows[totalConfColumns]["ColumnName"].ToString() + ", ";
}
selectedColumns = selectedColumns.Trim().Substring(0, selectedColumns.Length - 2);
auditAdapter.SelectCommand.CommandText = "SELECT " + keyColumn + "," + selectedColumns + " FROM INSERTED";
DataTable insertedTable = new DataTable();
auditAdapter.Fill(insertedTable);
auditAdapter.SelectCommand.CommandText = "SELECT " + keyColumn + "," + selectedColumns + " FROM DELETED";
DataTable deletedTable = new DataTable();
auditAdapter.Fill(deletedTable);
if (triggContext.TriggerAction == TriggerAction.Update)
{
rowsAffected = deletedTable.Rows.Count;
for (int totalNofRecords = rowsAffected - 1; totalNofRecords >= 0; totalNofRecords--)
{
insertedRow = insertedTable.Rows[totalNofRecords];
deletedRow = deletedTable.Rows[totalNofRecords];
keyValue = deletedTable.Rows[totalNofRecords][keyColumn].ToString();
foreach (DataColumn column in insertedTable.Columns)
{
if (!insertedRow[column.Ordinal].Equals(deletedRow[column.Ordinal]))
{
sqlComm.CommandText = "INSERT INTO tTR_Audit(TableName, PK, AuditType, FieldName, Old, New, UpdateDateTime, UserID, Comment) SELECT '" + tableName + "', '" + keyValue + "', 'U', '" + column.ColumnName + "', NULLIF('" + deletedRow[column.Ordinal].ToString().Replace("'", "''") + "', ''), NULLIF('" + insertedRow[column.Ordinal].ToString().Replace("'", "''") + "', ''), GETDATE(), " + userID + ", NULL";
sqlP.Send(sqlComm.CommandText);
sqlP.ExecuteAndSend(sqlComm);
}
}
}
}
else if (triggContext.TriggerAction == TriggerAction.Delete)
{
rowsAffected = deletedTable.Rows.Count;
for (int totalNofRecords = rowsAffected - 1; totalNofRecords >= 0; totalNofRecords--)
{
deletedRow = deletedTable.Rows[totalNofRecords];
keyValue = deletedTable.Rows[totalNofRecords][keyColumn].ToString();
foreach (DataColumn column in deletedTable.Columns)
{
sqlComm.CommandText = "INSERT INTO tTR_Audit(TableName, PK, AuditType, FieldName, Old, New, UpdateDateTime, UserID, Comment) SELECT '" + tableName + "', '" + keyValue + "', 'D', '" + column.ColumnName + "', '" + deletedRow[column.Ordinal].ToString().Replace("'", "''") + "', GETDATE(), " + userID + ", NULL";
sqlP.Send(sqlComm.CommandText);
sqlP.ExecuteAndSend(sqlComm);
}
}
}
else if (triggContext.TriggerAction == TriggerAction.Insert)
{
rowsAffected = insertedTable.Rows.Count;
for (int totalNofRecords = rowsAffected - 1; totalNofRecords >= 0; totalNofRecords--)
{
insertedRow = insertedTable.Rows[totalNofRecords];
keyValue = insertedTable.Rows[totalNofRecords][keyColumn].ToString();
//foreach (DataColumn column in insertedTable.Columns)
//{
sqlComm.CommandText = "INSERT INTO tTR_Audit(TableName, PK, AuditType, FieldName, Old, New, UpdateDateTime, UserID, Comment) SELECT '" + tableName + "', '" + keyValue + "', 'I', '" + keyColumn + "', NULL, '" + keyValue + "', GETDATE(), " + userID + ", NULL";
sqlP.Send(keyColumn);
sqlP.Send(sqlComm.CommandText);
sqlP.ExecuteAndSend(sqlComm);
//}
}
}
}
conn.Close();
}
catch (InvalidOperationException ex)
{
sqlP.Send(ex.ToString());
}
}
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
private static void WriteOldVersion(XmlNode objectXML)
{
string objectName = "";
string objectType = "";
string objectDetails = "";
string loginName = "";
string databaseName = "";
string postTime = "";
XmlNodeReader xmlDetails = new XmlNodeReader(objectXML);
while (xmlDetails.Read())
{
if (xmlDetails.NodeType == XmlNodeType.Element &&
xmlDetails.Name == "ObjectName")
{
xmlDetails.Read();
objectName = xmlDetails.Value;
}
if (xmlDetails.NodeType == XmlNodeType.Element &&
xmlDetails.Name == "ObjectType")
{
xmlDetails.Read();
objectType = xmlDetails.Value;
}
if (xmlDetails.NodeType == XmlNodeType.Element && xmlDetails.Name == "LoginName")
{
xmlDetails.Read();
loginName = xmlDetails.Value;
}
if (xmlDetails.NodeType == XmlNodeType.Element &&
xmlDetails.Name == "DatabaseName")
{
xmlDetails.Read();
databaseName = xmlDetails.Value;
}
if (xmlDetails.NodeType == XmlNodeType.Element &&
xmlDetails.Name == "PostTime")
{
xmlDetails.Read();
postTime = xmlDetails.Value;
}
if (xmlDetails.NodeType == XmlNodeType.Element &&
xmlDetails.Name == "CommandText")
{
xmlDetails.Read();
objectDetails = xmlDetails.Value;
}
}
xmlDetails.Close();
SqlPipe pipeSql = SqlContext.Pipe;
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
string sql = "INSERT INTO VSS " +
"(ObjectName,ObjectType,LoginName,DatabaseName," +
" PostTime,ObjectDetails) " +
"VALUES('" + objectName.Trim() + "','" + objectType.Trim() +
"','" + loginName.Trim() + "','" + databaseName.Trim() +
"','" + postTime.Trim() + "','" + objectDetails.Trim() + "')";
SqlCommand sqlComm = new SqlCommand(sql, cn);
pipeSql.Send(sqlComm.CommandText);
pipeSql.ExecuteAndSend(sqlComm);
}
}