Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2266
  • Last Modified:

How do i debug triggers?

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
paulCardiff
Asked:
paulCardiff
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
scorpiiaCommented:
you can insert "print" command in your trigger scripts, it will display the result in the query result window
0
 
paulCardiffAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now