Microsoft SQL Server 2014 -- debug table trigger ?

finance_teacher
finance_teacher used Ask the Experts™
on
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"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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.
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial