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

On Sat, 12 Apr 2008 22:37:48 GMT, "Ben" <pillars4@xxxxxxxxxxxxx>
wrote:

I didn't test this, but I would think the RAISEERROR would generate a
trappable runtime error in your Access app. Depending on the exact
details of your app, it could be trapped in the Form_Error event, or
if your form is unbound or the record is inserted using ADO or
similar, in that procedure.

-Tom.



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





.

Relevant Pages

  • Re: Index for username/password
    ... say that users want to be able to define customer ... A username in a login table is a little different. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Logging database changes
    ... I use a "changes" file with a nask key and fields containing record- ... I have a generic trigger subroutine that I clone & modify for each new ... as those equate lists must be managed ... frequently, like customer balance, etc. ...
    (comp.databases.pick)
  • Re: customer service
    ... And so you dug out your username, that is, typed it into the ... you were directed by the email or by the follow-up message to you ... Am.Exp./Discover itself warns customers about 'Phishing ... and account information or to lure a Discover customer who responds to ...
    (soc.retirement)
  • Problem with date evaluation in JOIN filter
    ... UserName varchar- PK ... Customer int - PK ... UserCustomer (which mobile user works with which customer) ...
    (microsoft.public.sqlserver.replication)
  • Re: Logging deletes...
    ... when the customer record is deleted, the trigger is fired and then the row ... is added to the delcustomer table it would automatically add the datetime ... Here is my current trigger: ... > Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.programming)