Solved

How do i debug triggers?

Posted on 2007-12-07
4
2,243 Views
Last Modified: 2012-08-14
I've got an insert trigger but for some reason its failing - whats the best way to debug and get to the route of this problem?

THe problem i have is that i'm using SQL 2005s new INSERTED table and think it maybe related - but as a consequence i'm finding this very difficult to reproduce
0
Comment
Question by:paulCardiff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20426893
>I've got an insert trigger but for some reason its failing
any error message(s) ? or does it simply "not work"?

trigger code? relevant table descriptions?
0
 
LVL 4

Assisted Solution

by:scorpiia
scorpiia earned 100 total points
ID: 20426913
you can insert "print" command in your trigger scripts, it will display the result in the query result window
0
 

Author Comment

by:paulCardiff
ID: 20427010
Hi Angell here the exact code - any advice would be much appreciated
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
Create TRIGGER [dbo].[trgInsert]
ON [TABLE1].[dbo].[Customer]
FOR INSERT
AS 
 
BEGIN TRANSACTION
DECLARE @ERR INT
DECLARE @CompanyId INT
 
		-----------------------------
		-- INSERT INTO COMPANY
		-----------------------------
		INSERT INTO TABLE2.[dbo].[Company]
           ([IsActive]
           ,[Name]
           ,[DefaultEmail]
           ,[CreatedDate]
           ,[useDefaultMTA]
           ,Field1
           ,Field2
           ,Field3
           ,Field4
           ,Field5
           ,Field6
           ,Field7
           ,Field8
           ,Field9
           ,Field10
           ,Field11
           ,Field12
           ,Field13
           ,Field14
           ,Field15
           ,Field16
           ,Field17
           ,Field18
           ,Field19
           ,Field20
           ,Field21
           ,Field22
           ,Field23
           ,Field24
           ,Field25
           ,Field26
           ,Field27
           ,Field28)		
		SELECT
			1,
			a.Company,
			a.Email,
			getdate(),
			1,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			null,
			1,
			1,
			1,
			1,
			0		
		FROM INSERTED i -- direct copy
			INNER JOIN dbo.Address a on i.BillingAddressID = a.AddressID
	     
		set @CompanyId = @@identity
		-----------------------------
		-- INSERT INTO USER
		-----------------------------
 
        INSERT INTO [TABLE2].[dbo].[User]
           ([FirstName]
           ,[LastName]
           ,[Email]
           ,[Address]
           ,[Country]
           ,[City]
           ,[County]
           ,[PostCode]
           ,[PhoneNumber]
           ,[Gender]
           ,Field1
           ,Field2
           ,Field3
           ,Field4 
           ,Field5
           ,Field6
           ,Field7
           ,Field8
           ,Field9
           ,Field10
           ,Field11)
 
        SELECT
			i.FirstName, --[FirstName]
			i.LastName, --[LastName]
			i.Email, --[Email]
            a.Address1,-----------[Address]  
            a.Country,-----------[Country]
            a.City,-----------[City]
            a.State,-----------[County]
            a.Zip,-----------[PostCode]
			i.Phone, --
			Gender, --
			0, --
			1, --
			CustomerGUID, --
			@CompanyId, --
			null, --
			null, --
			null, --
			LastIPAddress, --
			0, --
			0, --
			3 --
 
		FROM INSERTED i -- direct copy
			INNER JOIN dbo.Address a on i.BillingAddressID = a.AddressID
 
SET @ERR = @@Error
IF @ERR = 0    
             BEGIN
				ROLLBACK TRANSACTION 
             END
ELSE
             BEGIN
				COMMIT TRANSACTION
             END

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 20427029
>set @CompanyId = @@identity
should really be SCOPE_IDENTITY()

now, will the insert potentially insert multiple records? scope_identity() will only return 1 value...
if that first insert can insert multiple rows, you will need the new sql server 2005 concept of the OUTPUT clause:
http://www.databasejournal.com/features/mssql/article.php/3598636

note: when using a ROLLBACK inside a trigger, it will rollback also the outer transaction.
you should consider removing BEGIN TRANSACTION and COMMIT, and only keep the ROLLBACK in case of problems...
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question