public static void SqlTrigger1() { // Replace with your own code SqlContext.Pipe.Send("Trigger FIRED"); SqlTriggerContext triggContext = SqlContext.TriggerContext; String name = ""; 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 name from INSERTED"; name = sqlComm.ExecuteScalar().ToString(); } } var req = HttpWebRequest.Create("http://localhost:8080/Rest/HelloWorld?name=" + name); req.ContentType = "application/json"; req.Method = "GET"; var response = req.GetResponse(); }
public static void updateFromDB() { // <summary> // Authors: Christopher Rupert, // This will be used to check against the database to determine if: // The database has recently had a new entry inserted // OR, // IF the database has had an update or delete to a record. // </summary> string strConnect = ConfigurationManager.ConnectionStrings["ChatAppConnectionString"].ToString(); // Set contextual trigger for sql connection. SqlTriggerContext triggContext = SqlContext.TriggerContext; // Define pipe SqlPipe pipe = SqlContext.Pipe; // Flexible switch statement: switch (triggContext.TriggerAction) { case TriggerAction.Insert: // Retrieve the connection string and use it forward; // NOTE: This can be used along side a data access layer class. using (SqlConnection connection = new SqlConnection(@"context connection=true")) { // Bring in Data Access Layer class script for quick distribution var sqlOut = DAL.ExecStoredProcedure("GetMessages", null); //DataTableReader dr = sqlOut.CreateDataReader(); } break; } }
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 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 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 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 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); } } } }
//[SqlTrigger(Name = "InsertContact", Target = "Person.EmailAddress", Event = "FOR INSERT")] public static void InsertContact() { SqlTriggerContext triggerContext = SqlContext.TriggerContext; if (triggerContext != null && triggerContext.TriggerAction == TriggerAction.Insert) { using (var connection = new SqlConnection("Context Connection=true")) { using ( var command = new SqlCommand { Connection = connection, CommandText = "SELECT EmailAddress FROM INSERTED" }) { connection.Open(); var email = command.ExecuteScalar() as string; if (email != null && !Regex.IsMatch(email, EmailRegExPattern)) { throw new FormatException("Invalid email"); } } } } }
public static void TableAudit() { SqlCommand command = new SqlCommand(); SqlTriggerContext triggContext = SqlContext.TriggerContext; SqlDataReader reader; switch (triggContext.TriggerAction) { // Insert. case TriggerAction.Insert: using (SqlConnection connection = new SqlConnection(@"context connection=true")) { // Open the context connection. connection.Open(); // Get the inserted row. command = new SqlCommand(@"SELECT * FROM INSERTED;", connection); reader = command.ExecuteReader(); reader.Read(); // Retrieve data from inserted row. reader.Close(); } break; // Delete. case TriggerAction.Delete: using (SqlConnection connection = new SqlConnection(@"context connection=true")) { // Open the context connection. connection.Open(); // Get the deleted rows. command = new SqlCommand(@"SELECT * FROM DELETED;", connection); reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { // Retrieve data from deleted rows. } reader.Close(); } else { // No rows affected. } } break; } }
public static void ctr_Contact_iu_Email() { //get trigger context to access trigger related features SqlTriggerContext triggerContext = SqlContext.TriggerContext; //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(); } } } } } } }
// [Microsoft.SqlServer.Server.SqlTrigger(Name="NoLineDelete", Target="[Lines]", Event="FOR UPDATE")] public static void T_NoLineDelete() { SqlTriggerContext triggerContext = SqlContext.TriggerContext; if (triggerContext.TriggerAction == TriggerAction.Delete) { SqlContext.Pipe.Send("You can't delete lines."); } }
public static void NewBlogFriends() { SqlTriggerContext triggContext = SqlContext.TriggerContext; SqlCommand command = null; DataSet insertedDS = null; SqlDataAdapter dataAdapter = null; try { // Retrieve the connection that the trigger is using using (SqlConnection connection = new SqlConnection(@"context connection=true")) { connection.Open(); command = new SqlCommand(@"SELECT * FROM INSERTED;", connection); dataAdapter = new SqlDataAdapter(command); insertedDS = new DataSet(); dataAdapter.Fill(insertedDS); TriggerHandler(connection, ContentType.Blog, insertedDS); } } catch { } finally { try { if (command != null) { command.Dispose(); command = null; } if (dataAdapter != null) { dataAdapter.Dispose(); dataAdapter = null; } if (dataAdapter != null) { dataAdapter.Dispose(); dataAdapter = null; } if (insertedDS != null) { insertedDS.Dispose(); insertedDS = null; } } catch { } } }
// ¬ведите существующую таблицу или представление дл¤ целевого объекта и раскомментируйте строку атрибута. // [Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger", Target="Table1", Event="FOR UPDATE")] public static void SafeTrigger() { SqlTriggerContext triggerContext = SqlContext.TriggerContext; if (triggerContext.TriggerAction == TriggerAction.Delete) { SqlContext.Pipe.Send("You can't delete while safe trigger active! Sorry boi"); } }
// Введите существующую таблицу или представление для целевого объекта и раскомментируйте строку атрибута. // [Microsoft.SqlServer.Server.SqlTrigger (Name="SqlTrigger1", Target="Table1", Event="FOR UPDATE")] public static void MyTrigger() { SqlTriggerContext triggerContext = SqlContext.TriggerContext; if (triggerContext.TriggerAction == TriggerAction.Update) { SqlContext.Pipe.Send("You can't update while safe trigger active!"); } }
public static void deleteMe() { SqlTriggerContext triggerContext = SqlContext.TriggerContext; if (triggerContext.TriggerAction == TriggerAction.Delete) { SqlContext.Pipe.Send("You can't delete while deleteMe active! Sorry, not today..!.."); } }
public static void HelloWorld() { SqlTriggerContext triggContext = SqlContext.TriggerContext; SqlConnection conn = new SqlConnection("context connection=true"); SqlPipe p; p = SqlContext.Pipe; p.Send(System.DateTime.Today.ToString()); }
public static void AgentUpdatedTrigger() { try { SqlTriggerContext context = SqlContext.TriggerContext; if (context.TriggerAction == TriggerAction.Update) { Uri uri = new Uri("http://152.96.56.75/Data/AgentUpdatedTrigger"); WebClient client = new WebClient(); SqlCommand command; SqlDataReader reader; string values = "{param:["; bool hasRows = false; using (SqlConnection connection = new SqlConnection(@"context connection=true")) { connection.Open(); command = new SqlCommand(@"SELECT i.AgentNr, i.Name, i.IPAddress, i.Status, i.Port, i.sysDesc, i.sysName, i.sysUptime FROM INSERTED i;", connection); reader = command.ExecuteReader(); hasRows = reader.HasRows; while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if (reader.GetName(i).Equals("sysDesc") || reader.GetName(i).Equals("sysName") || reader.GetName(i).Equals("sysUptime")) { values += "{\"" + reader.GetName(i) + "\":" + reader.GetValue(i) + "},"; } else { values += "{\"" + reader.GetName(i) + "\":\"" + reader.GetValue(i) + "\"},"; } } } reader.Close(); } values += "]}"; if (hasRows) { string param = "param=" + values; client.Headers[HttpRequestHeader.ContentType] = "application/x-www-form-urlencoded"; client.UploadString(uri, "POST", param); } } } catch (Exception exc) { SqlPipe sqlP = SqlContext.Pipe; sqlP.Send("Fehler UpdatedTrigger: " + exc.Message); } }
public static void NewLiveBroadcast() { try { SqlCommand command; SqlTriggerContext triggContext = SqlContext.TriggerContext; switch (triggContext.TriggerAction) { case TriggerAction.Insert: // Retrieve the connection that the trigger is using using (SqlConnection connection = new SqlConnection(@"context connection=true")) { connection.Open(); command = new SqlCommand(@"SELECT * FROM INSERTED;", connection); SqlDataAdapter dataAdapter = new SqlDataAdapter(command); DataSet ds = new DataSet(); dataAdapter.Fill(ds); //Populate data from the inserted row DataRow insertedRow = ds.Tables[0].Rows[0]; DataColumnCollection insertedColumns = ds.Tables[0].Columns; int MemberIDTo = (int)insertedRow["MemberIDTo"]; int MemberIDFrom = (int)insertedRow["MemberIDFrom"]; Member targetMember = Member.GetMemberByMemberID(MemberIDTo, connection); Member sendingMember = Member.GetMemberByMemberID(MemberIDFrom, connection); string TargetEmailAddress = targetMember.Email; string subject = Templates.NewLiveBroadcastSubject; string body = Templates.NewLiveBroadcastBody; subject = MergeHelper.GenericMerge(subject, insertedColumns, insertedRow); body = MergeHelper.GenericMerge(body, insertedColumns, insertedRow); subject = MergeHelper.MergeMemberInfo(subject, targetMember); body = MergeHelper.MergeMemberInfo(body, targetMember); subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember); body = MergeHelper.MergeOtherMemberInfo(body, sendingMember); MailHelper.SendEmail(TargetEmailAddress, subject, body); } break; } } catch { } }
// Enter existing table or view for the target and uncomment the attribute line //[Microsoft.SqlServer.Server.SqlTrigger (Name="SalesAudit", Target="[dbo].[SalesInfo]", Event="FOR INSERT")] public static void SalesAudit() { // Get the trigger context. SqlTriggerContext triggContext = SqlContext.TriggerContext; switch (triggContext.TriggerAction) { case TriggerAction.Insert: SqlContext.Pipe.Send("Trigger FIRED"); break; } }
public static void C1Order() { try { webmethods.Add("getOrder", "http://nationaldev.conceptconfigurator.com/webservices/services/ConceptAccess?method=getOrder"); } catch (Exception ex1) { //fallthru } string connectionString = "server = grcdslsql0.dom.grc; enlist=false; database = SL_MAN_App; User ID = sa_config; Password = options23"; //using (SqlConnection conn = new SqlConnection("context connection=true")) string orderNum; string orderValue = ""; SqlTriggerContext triggContext = SqlContext.TriggerContext; SqlParameter orderNumber = new SqlParameter("@order_num", 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 order_num from INSERTED"; orderNumber.Value = sqlComm.ExecuteScalar().ToString(); orderValue = orderNumber.Value.ToString(); } } string useMethod = ""; string key = "getOrder"; if (webmethods.ContainsKey(key)) { useMethod = webmethods[key]; C1URL = useMethod; } caller = "ORDER"; orderNum = orderValue; string xmlPayload = "<soap:Envelope xmlns:xsi=" + (char)34 + "http://www.w3.org/2001/XMLSchema-instance" + (char)34 + " xmlns:xsd=" + (char)34 + "http://www.w3.org/2001/XMLSchema" + (char)34 + " xmlns:soap=" + (char)34 + "http://schemas.xmlsoap.org/soap/envelope/" + (char)34 + ">" + "<soap:Body><" + key + " xmlns=" + (char)34 + "http://ws.configureone.com" + (char)34 + "><orderNum>" + orderNum + "</orderNum></" + key + "></soap:Body></soap:Envelope>"; C1WebService.CallConfigureOne(key, xmlPayload, C1URL); }
public static void MonitorDataInsertedTrigger() { try { SqlTriggerContext myContext = SqlContext.TriggerContext; if (myContext.TriggerAction == TriggerAction.Insert) { Uri uri = new Uri("http://152.96.56.75/Data/RowInsertedTrigger"); WebClient client = new WebClient(); SqlCommand command; SqlDataReader reader; string values = "{param:["; using (SqlConnection connection = new SqlConnection(@"context connection=true")) { connection.Open(); command = new SqlCommand(@"SELECT i.AgentNr, mt.ObjectID, i.Result, i.MonitorTimestamp FROM INSERTED i INNER JOIN MonitoringType mt ON i.MonitoringTypeNr = mt.MonitoringTypeNr;", connection); reader = command.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if (reader.GetName(i).Equals("Result")) { values += "{\"" + reader.GetName(i) + "\":" + reader.GetValue(i) + "},"; } else { values += "{\"" + reader.GetName(i) + "\":\"" + reader.GetValue(i) + "\"},"; } } } reader.Close(); } values += "]}"; string param = "param=" + values; client.Headers[HttpRequestHeader.ContentType] = "application/x-www-form-urlencoded"; client.UploadString(uri, "POST", param); } } catch (Exception exc) { SqlPipe sqlP = SqlContext.Pipe; sqlP.Send("Fehler: " + exc.Message); } }
public static void SalesAudit() { // Get the trigger context. SqlTriggerContext triggContext = SqlContext.TriggerContext; switch (triggContext.TriggerAction) { case TriggerAction.Insert: // Do something in response to the INSERT. break; } }
public static void InsertUserTrigger() { // Get the trigger context. SqlTriggerContext triggContext = SqlContext.TriggerContext; // Select the trigger. switch (triggContext.TriggerAction) { case TriggerAction.Insert: // Insert new user. SqlXml data = triggContext.EventData; break; } }
public static void SqlTrigger1() { SqlTriggerContext triggContext = SqlContext.TriggerContext; if (triggContext.TriggerAction == TriggerAction.Insert) { using (SqlConnection connection = new SqlConnection("context connection = true")) { SqlCommand command = new SqlCommand(@"SELECT * FROM INSERTED;", connection); connection.Open(); SqlContext.Pipe.ExecuteAndSend(command); connection.Close(); } } }
public static void GetSumReportMG(int type, int level, int debug, DateTime dts, DateTime dte) { SqlTriggerContext triggerContext = SqlContext.TriggerContext; SqlPipe pipe = SqlContext.Pipe; using (SqlConnection connection = new SqlConnection("context connection=true")) { DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(); string runSQL = string.Empty; var command = connection.CreateCommand(); command.CommandText = "SELECT u001 FROM wgs049(NOLOCK) WHERE u018=@Level;"; command.Parameters.AddWithValue("@Level", @level); } }
public static void DropTableTrigger() { SqlTriggerContext triggContext = SqlContext.TriggerContext; switch (triggContext.TriggerAction) { case TriggerAction.Delete: SqlContext.Pipe.Send("String was not deleted! I cant let you do that!"); break; default: SqlContext.Pipe.Send("Something happened!"); break; } }
public static void RTLogs() { SqlCommand command = null; SqlDataReader reader = null; SqlTriggerContext triggContext = SqlContext.TriggerContext; switch (triggContext.TriggerAction) { case TriggerAction.Insert: pushToSlack(command, reader, "Insert", "AlgoExecLogs"); break; default: break; } }
public static void Database_CreateTable_DropTable() { SqlTriggerContext tc = SqlContext.TriggerContext; if (tc.TriggerAction == TriggerAction.CreateTable) { SqlContext.Pipe.Send("A new table has been created."); } else if (tc.TriggerAction == TriggerAction.DropTable) { SqlContext.Pipe.Send("A table has been dropped."); } string eventData = tc.EventData.Value; SqlContext.Pipe.Send(eventData); }
public static void NewEventTrigger() { try { SqlTriggerContext myContext = SqlContext.TriggerContext; if (myContext.TriggerAction == TriggerAction.Insert) { Uri uri = new Uri("http://152.96.56.75/Data/NewEventTrigger"); WebClient client = new WebClient(); SqlCommand command; SqlDataReader reader; string values = "{param:["; using (SqlConnection connection = new SqlConnection(@"context connection=true")) { connection.Open(); command = new SqlCommand(@"SELECT * FROM INSERTED i;", connection); reader = command.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if (reader.GetName(i).Equals("Message") || reader.GetName(i).Equals("Stacktrace")) { values += "{\"" + reader.GetName(i) + "\":\"" + cleanForJSON(reader.GetValue(i).ToString()) + "\"},"; } else { values += "{\"" + reader.GetName(i) + "\":\"" + reader.GetValue(i) + "\"},"; } } } reader.Close(); } values += "]}"; string param = "param=" + values; client.Headers[HttpRequestHeader.ContentType] = "application/x-www-form-urlencoded"; client.UploadString(uri, "POST", param); } } catch (Exception exc) { SqlPipe sqlP = SqlContext.Pipe; sqlP.Send("Fehler: " + exc.Message); } }
public static void SendMessageBitrix() { SqlTriggerContext triggContext = SqlContext.TriggerContext; SqlCommand command; SqlDataReader reader; if (triggContext.TriggerAction == TriggerAction.Update) { using (SqlConnection connection = new SqlConnection(@"context connection=true")) { connection.Open(); command = new SqlCommand(@"SELECT * FROM INSERTED;", connection); reader = command.ExecuteReader(); reader.Read(); bool goingToSendMessage = false; for (int i = 0; i < reader.FieldCount - 1; i++) { if (reader.GetName(i) == "DocNum") { goingToSendMessage = true; break; } } reader.Close(); if (goingToSendMessage) { Bitrix24 bx_logon = new Bitrix24(); try { _ = bx_logon.SendCommand("log.blogpost.add", "", "USER_ID=1767" + "&POST_TITLE=тест триггер" + "&POST_MESSAGE=тест триггер" + "&DEST[0]=SG521" ); } catch (Exception) { } } } } }