Microsoft SQL Server 2014 -- debug table trigger ?

What is the best way to debug a Microsoft SQL Server 2014 table UPDATE trigger inside of Microsoft SQL Server 2014 Management Studio ?

https://stackoverflow.com/questions/22033109/how-to-debug-a-t-sql-trigger acts like maybe the below ?
 1. set a BREAKPOINT on insert into X(ID) values (1),(2)
 2. run insert into X(ID) values (1),(2) in DEBUG mode
 3. press F11 to "Step Into"
finance_teacherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
A simple way to debug a trigger would be to log certain messages into log tables or print some messages if you are trying to debug or test it from Query window..
There's no need to complicate much on this..
May I know what exactly you are trying to achieve so that we can guide you better.
Mark WillsTopic AdvisorCommented:
While you can use DEBUG ( from the Menu Bar), I normally write to a log / Audit table

It can be difficult in Debug mode to see values, where as you can do your own audit / log:

If object_id('EE_Test','U') is NOT null drop table EE_Test 
go

create table EE_Test(ID int identity primary key, Val1 varchar(100), Val2 money, Val3 datetime)
go

If object_id('tempdb..##My_Audit_EE_Test','U') is NOT null drop table ##My_Audit_EE_Test 
go

create table ##My_Audit_EE_Test(AuditDate datetime, UserID varchar(100),Activity char(1), Source char(1), Message varchar(100),ID int , Val1 varchar(100), Val2 money, Val3 datetime)
go


create trigger trg_EE_Test on EE_Test
after insert, update, Delete
as
    set nocount on
    declare @Activity char(1)
    declare @Message varchar(100)

	set @Message = 'Start Audit Trail'

	If object_id('tempdb..##My_Audit_EE_Test','U') is NOT null
       insert ##My_Audit_EE_Test (AuditDate,UserID,Activity,Source,Message)
       Select getdate() as AuditDate, @@SPID UserID, @Activity Activity, ' ' Source, @message  


    if exists (select null from inserted)
      if exists (select null from deleted)
         select @Activity = 'U'
      else
         select @Activity = 'I'
    else
       select @Activity = 'D'

    if @Activity in ('I','U')
    begin
	   set @message = 'Now loading INSERTED'
       If object_id('tempdb..##My_Audit_EE_Test','U') is NOT null
          Insert ##My_Audit_EE_Test
          select getdate(), @@SPID, @Activity,'I',@message,* from inserted 
    end

    if @Activity in ('D','U')
    begin
	   set @message = 'Now loading DELETED'
       If object_id('tempdb..##My_Audit_EE_Test','U') is NOT null
          Insert ##My_Audit_EE_Test
          select getdate(), @@SPID, @Activity,'D',@message,* from deleted 
    end
go



insert EE_Test values ('Some Value',123, getdate())
 
select * from ##my_Audit_EE_Test

insert EE_Test values ('Some Other Value',111.23, getdate())

select * from ##my_Audit_EE_Test

update EE_Test set val2 = val2 * 10

select * from ##my_Audit_EE_Test

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSMS

From novice to tech pro — start learning today.