Service Broker- Similar to Trigger RRS feed

  • Question

  • Hi Guys,

    We are new to service broker, we want to enable service broker on our server; we have lot of triggers in the SQL server which we want to get rid of  and go service broker route.

    We have below table format

    OrderID    OrderQty CreatedDate    OrderAmount  OrderTotalAmount      CreatedDateGMT           

    1                  2          05-11-2012            4                   8                           GMTDate

    Our trigger populates OrderTotalmount and GMT dates; now we want to achieve same using service broker; is it possible, if so can you provide sample or link etc.



    Navin.D http://dnavin.wordpress.com

    Tuesday, May 12, 2020 5:59 PM

All replies

  • Both do same functionality synchronously do action - Service broker is best choice rather than trigger, as trigger will be very slow during bulk operations and need extra care, improper code could fire them recursively

    here is blog for service broker https://blog.sqlauthority.com/2009/09/21/sql-server-intorduction-to-service-broker-and-sample-script/


    Tuesday, May 12, 2020 6:17 PM
  • Rather than having triggers that perform synchronous updates, you can have triggers that post messages on Service Broker queues for asynchronous updates. This can certainly be a good idea in some situation, but maybe not all. Say that you have a Transactions table and a CashHoldings table, and the latter is updated by a trigger on Transactions.

    Say that a deposit is registered for an account? Would it be OK to process the update of CashHoldings asynchronously? Probably, yes. But what if it is a withdrawal? Absolutely, no way! In this case, the cash holdings MUST be updated before the transaction commits.

    I am not going to give you an example, but I will give you a link to a very good book about Service BRoker:
    Many technical books are thick and takes ages to read. This book is only 220 pages and is very short and concise. Roger Wolter was the Program Manager for the development of Service Broker.

    Also be sure to check out Remus Rusanu's blog, rusanu.com. He was one of the developers of Service Broker, and his blog is full of tips for Service Broker.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, May 12, 2020 9:40 PM