Update History Table Trigger Problems

[MelissaKT]

I set up a trigger on Friday, hoping to have a history table on my database. The trigger seemed to be working fine. Then, I started getting THOSE phone calls. We started getting the error:
[COLOR=red yellow]the data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source [/color]

Now, the form that this is talking about is based on a view with no parameters. So, I tried to enter a record from the table...same error. Everything went back to normal as soon as I took the trigger off of the table. I would appreciate any help with this.

Oh, and I actually have tried 2 different versions. The first actually dumps all of the fields into a History Table. The second dumps the records into an audit table. The audit table only records the field name, new value, old value and table name. Anyway, here is the first trigger:

ALTER TRIGGER tblRequestDetailsReoccuringUpdateHistoryTable
ON dbo.tblRequestDetailsReoccuring
AFTER  INSERT, UPDATE, DELETE 
AS
BEGIN
	IF (SELECT COUNT(*) FROM inserted) > 0 
		BEGIN
			IF (SELECT COUNT(*) FROM deleted)> 0          		BEGIN
                                                                                INSERT INTO dbo.tblRequestDetailsReoccuringHISTORY
                     					 (
							MonthlyPOID, RequestNumber, TypeOfService, AmountOfService, BudgetAccount, 
							closed, InputDate, InputUser,Type
						)
					SELECT     MonthlyPOID, RequestNumber, TypeOfService, AmountOfService, BudgetAccount, 
						closed, InputDate, InputUser,'U'
					FROM        inserted
				END
			ELSE
				BEGIN
					              INSERT INTO dbo.tblRequestDetailsReoccuringHISTORY
                     						 (
								MonthlyPOID, RequestNumber, TypeOfService, AmountOfService, 
								BudgetAccount, closed, InputDate, InputUser,Type
							)
							SELECT     MonthlyPOID, RequestNumber, TypeOfService, AmountOfService, 
								BudgetAccount, closed, InputDate, InputUser,'I'
								FROM         inserted
				END
			END
		ELSE
			BEGIN
					INSERT INTO dbo.tblRequestDetailsReoccuringHISTORY
                      					(
							MonthlyPOID, RequestNumber, TypeOfService, AmountOfService, BudgetAccount, 
							closed, InputDate, InputUser,Type
						)
					SELECT     MonthlyPOID, RequestNumber, TypeOfService, AmountOfService, BudgetAccount, closed, 
						InputDate, InputUser,'D'
					FROM        deleted
			END
		END

Thanks in advance for any help.

 

[SQLSister]

so what is the view code and what are some of the sample records you have tried to insert, update or delete?

Nothing in the trigger pops out at me. It puts the data into another table but doesn't change it in the underlying table. So not sure why turning the trigger off would fix your error.

"NOTHING is more important in a database than integrity." ESquared

 

[MelissaKT]

The View is a simple SELECT:

SELECT     RequestNumber, BudgetAccount, MonthlyPOID, TypeOfService, AmountOfService
FROM         dbo.tblRequestDetailsReoccuring

The MonthlyPOID is an int identity field. (I wondered if that was the problem)

The data was pretty simple stuff: The Request Number is something like 2007120220, the budget account 102-50-001, The monthlyPOID is an int field, type of service is a text field and Amount of Service is just a float (defaults to 0).

The really weird part was getting that error when I tried to directly enter it into the table.

Thanks for answering. Sometimes, even explaining it, can cause a lightbulb to go off. Unfortunately, so far, no light bulb.

What about nulls? The History Table is set up to accept nulls, but does it matter if I'm passing an INSERT statement NULL values? Just a thought?