Recover Select from Any Trigger

Question

How can I get one back select of a trigger?

Example:

CREATE TABLE PESSOA2
(
    ID INTEGER IDENTITY NOT NULL,
    NOME VARCHAR(250)
)
 CREATE TRIGGER SELECIONAR_NOME2 ON DBO.PESSOA2
AFTER UPDATE AS 

BEGIN 
    SELECT * FROM PESSOA2

END


 INSERT INTO PESSOA2 (NOME) VALUES ('fulano1')
 INSERT INTO PESSOA2 (NOME) VALUES ('fulano2')
 INSERT INTO PESSOA2 (NOME) VALUES ('fulano3')
 INSERT INTO PESSOA2 (NOME) VALUES ('fulano4')
 INSERT INTO PESSOA2 (NOME) VALUES ('fulano5')

UPDATE PESSOA2 SET NOME = 'fulano0' WHERE NOME = 'fulano1'

Result after giving UPDATE:
In this example, when I give one update he gives a select on the table pessoa, what I want to know is how I can recover the select via application c#?

Using try{}catch{} I can get an error return, but how to recover select table?

EX to get return:

try
{
    identity = dbCommand.ExecuteXmlReader().toInt32(); //Não conseguiu exercutar? Gera uma Excepition gera um log para análise 
}                                                   // e procura por outra Exception.
catch (Exception e)
{
    if(e.Message == "Comando inválido enviado a ExecuteXmlReader. O comando deve retornar um resultado Xml.")
    {
        return;
    }
    MessageBox.Show(e.Message, "Exceção banco de dados");

}

Answer 1

You can use Sqltabledependency:

using System;
using TableDependency.SqlClient;
using TableDependency.SqlClient.Base.EventArgs;

namespace RealTimeUpdate
{
    public class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Surname { get; set; }
    }


    class Program
    {
        private static string connection = "data source=.; initial catalog=MyDB; integrated security=True";

        static void Main(string[] args)
        {
            SqlTableDependency<Customer> dep;
            using (dep = new SqlTableDependency<Customer>(connection, "Customers"));
            {                
                dep.OnChanged += Changed;
                dep.Start();

                Console.WriteLine("Press a key to exit");
                Console.ReadKey();

                dep.Stop();
            }
        }

        public static void Changed(object sender, RecordChangedEventArgs<Customer> e)
        {
            var changedEntity = e.Entity;

            Console.WriteLine("DML operation: " + e.ChangeType);
            Console.WriteLine("ID: " + changedEntity.Id);
            Console.WriteLine("Name: " + changedEntity.Name);
            Console.WriteLine("Surame: " + changedEntity.Surname);
        }

    }
}

this row refers to the name of your table new SqlTableDependency<Customer>(connection, "Customers") that will be monitored, if you do not have a model with the same name you can use Mapper new SqlTableDependency<Customer>(_con, "Customers", mapper: mapper));.

that line dep.OnChanged += Changed; plus void Changed(object sender, RecordChangedEventArgs<Customer> e) refers to the watch in the chosen table, every time you have a update, insert or delete you will be notified.

Link to the github https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency

---

A second option would be Servicebrokerlistener

Enable Broken in db: ALTER DATABASE nomedodb SET ENABLE_BROKER

use the following code in c#:

var listener = new SqlDependencyEx(connectionString, "nomedobancodedados", "nomedatabela");
listener.TableChanged += (o, e) => Console.WriteLine("Faz alguma coisa");
listener.Start();
//listener.Stop();
//desativar o listener

update only:

var listener = new SqlDependencyEx(connectionString, "nomedobancodedados",
             "nomedatabela", listenerType: SqlDependencyEx.NotificationTypes.Update);

Link to the Github: https://github.com/dyatchenko/ServiceBrokerListener