Solved

How do i debug triggers?

Posted on 2007-12-07
4
2,213 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
  • 2
4 Comments
 
LVL 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
index  - last use and update 8 56
Compare data between two databases 16 95
SQL Select - Finding chars in a column 2 57
MS SQLK Server multi-part identifier cannot be bound 5 27
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now