Access 2003 ADP - SQL trigger problem
Posted on 2006-05-30
I have a SQL table 'A' with an INSERT trigger that inserts rows into another table 'B'. The trigger works fine when I use Enterprise Manager to insert a row into table 'A'. But if I try to edit table 'A' from the ADP and insert a row I get an error, though the trigger does actually do the job. What's going on?
The error is "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."
'A' has a one-to-many relationship to 'B'.
CREATE TRIGGER trgContact_Insert ON [dbo].[tblContact]
NOT FOR REPLICATION
--Add default records to tblMailout when new Contact is created
--if records don't already exist and the PromMaterial bit is on
SET NOCOUNT ON
DECLARE @HasMail int
DECLARE @ContactID int
DECLARE @PromoMaterial bit
SELECT @ContactID=ContactID,@PromoMaterial=PromoMaterial FROM Inserted
--get a count of records from tblMailout for this client
SELECT @HasMail=count(*) FROM dbo.tblMailout
--insert the tblMailout records if required
IF @PromoMaterial=1 and @HasMail=0
INSERT INTO dbo.tblMailout
SELECT @ContactID,MailoutTypeID,1,1,1 FROM dbo.tbl_LU_MailOutType