Handle Triggers in MS Access 2003 with SQL Server as Back-End

Hi!

I have a trigger created for Customer table. My front-end is access. What
is the best approach to handle a trigger result when adding a new customer
record?

Below is the trigger script:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - -

CREATE TRIGGER dbo.trTrackInsert

ON dbo.Customers

FOR INSERT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Validate the new record.

-- Criteria:

-- 1. Check if there is already a record in the NewCustTracker

-- 1.1. If no record add to the table and record the user info

-- 1.2. If there is record

-- 1.2.1 Inform user that there is pending new record to be completed

-- 1.2.2 Perform roll back of the insert in the Customers table

-- Initialize variables to use in getting some info in the NewCustTracker
and Customer

-- tables.

DECLARE @recordCount int;

DECLARE @userName nvarchar(200);

SET @recordCount = 0;

SET @userName = '';

-- get the record count in the dbo.NewCustTracker table

SET @recordCount = (SELECT count(*) FROM dbo.NewCustTracker);

BEGIN TRANSACTION insertIntoNewCustTracker

IF (@recordCount > 0)

BEGIN

-- get the info in the NewCustTracker table...

SET @userName = (SELECT UserName FROM dbo.NewCustTracker);

RAISERROR(N'There is a pending new customer record to be completed by %s.
Please recheck in a couple of minutes.',16,1,@userName);

ROLLBACK TRANSACTION insertIntoNewCustTracker;

END

ELSE

BEGIN

-- record the new customer record in the NewCustTracker table for next
validation...

INSERT INTO dbo.NewCustTracker(CustNum, UserName)

SELECT [Customer Number], user_name() FROM inserted;

IF @@TranCount > 0

COMMIT TRANSACTION insertIntoNewCustTracker;

END

END

GO

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - -

Any ideas/suggestions are appreciated.





Thanks,

Ben