January 28, 2011 at 5:31 am
Hello All
I am having problem with a trigger which i really cant understand.
I am use this trigger to insert into a table when a new customer has been added to the database
Below is the trigger
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER
TRIGGER [dbo].[Customer_InsertSync] ON [dbo].[Customer] FOR INSERT
AS
INSERT INTO Sync_Table( Record_Id,TableName,Operation,Transfered,LocationCode,BranchCode)
Select I.CustomerCode,'Customer','I',0,I.LocationCode,I.BranchCode
From Inserted I
The problem is its not consistent or I should say it does not fire sometimes.
If fifteen customers are added for the day about five are not added to the Sync_Table.
Example
2000, Customer,I,0,1,1
2001, Customer,I,0,1,1
2004, Customer,I,0,1,1
2006, Customer,I,0,1,1
2007, Customer,I,0,1,1
2008, Customer,I,0,1,1
2009, Customer,I,0,1,1
2012, Customer,I,0,1,1
Can someone tell me where I am going wrong I really cant see anything wrong with this trigger
Thanks in advance
January 28, 2011 at 5:43 am
it would also do this if the transaction is rolled back by the calling proc or application.
Your trigger seems fine. so unless it's manually disabled on and off during the day, then you have rollbacks.
Or someone is manually deleting data in the sync table.
AFAIK it's impossible for the trigger not to fire and your code is solid. So I'd definitely look elsewhere.
January 28, 2011 at 5:44 am
1 way to confirm this is to compare both tables on the ids. See if they match perfectly (at least recently).
January 28, 2011 at 5:59 am
You could also have some problems with the data if you are not handling exceptions correctly.
(eg Attempting to put NULLs into a NOT NULL column etc)
You could try logging any exceptions and seeing if anything apeears in the log. Something like:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.Exceptions
(
ExceptionId int IDENTITY(1,1) NOT NULL
CONSTRAINT PK_Exceptions PRIMARY KEY
,ExceptionDate datetime NOT NULL
,ErrorNumber int NOT NULL
,ErrorSeverity int NOT NULL
,ErrorState int NOT NULL
,ErrorProcedure nvarchar(126) NOT NULL
,ErrorLine int NOT NULL
,ErrorMessage nvarchar(2048) NOT NULL
)
GO
CREATE PROCEDURE dbo.ExceptionHandler
AS
SET NOCOUNT ON;
DECLARE @ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorProcedure nvarchar(126)
,@ErrorLine int
,@ErrorMessage nvarchar(2048);
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorLine = ERROR_LINE()
,@ErrorMessage = ERROR_MESSAGE();
IF @@TRANCOUNT > 0
ROLLBACK;
-- write to log
INSERT INTO dbo.Exceptions
(
ExceptionDate, ErrorNumber, ErrorSeverity, ErrorState
,ErrorProcedure, ErrorLine, ErrorMessage
)
SELECT CURRENT_TIMESTAMP, @ErrorNumber, @ErrorSeverity, @ErrorState
,COALESCE(@ErrorProcedure, N'NA'), @ErrorLine, COALESCE(@ErrorMessage, N'No Message');
RAISERROR
(
N'Error %d occurred in procedure %s at line %d. %s'
,@ErrorSeverity
,@ErrorState
,@ErrorNumber
,@ErrorProcedure
,@ErrorLine
,@ErrorMessage
);
GO
ALTER TRIGGER dbo.Customer_InsertSync
ON dbo.Customer
AFTER INSERT
AS
SET NOCOUNT ON
BEGIN TRY
INSERT INTO Sync_Table( Record_Id,TableName,Operation,Transfered,LocationCode,BranchCode)
SELECT I.CustomerCode, 'Customer' , 'I', 0, I.LocationCode, I.BranchCode
FROM inserted I;
END TRY
BEGIN CATCH
EXEC dbo.ExceptionHandler
END CATCH
GO
January 28, 2011 at 6:26 am
Thanks Ken
I'll try your solution.
And thanks to all who provided information.
Will provide feedback!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply