paulCardiff
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
any error message(s) ? or does it simply "not work"?
trigger code? relevant table descriptions?