Update on this issue. I have finished my changes to the front-end and
back-end to make it work.
I did the following:
a. Created the insert and update triggers at the back-end
b. Modified my stored procedure to capture the user-defined error message
I
created for this task
c.
"Ben" <pillars4@[EMAIL PROTECTED]
> wrote in message
news:0RaMj.951$FF6.571@[EMAIL PROTECTED]
> 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 @[EMAIL PROTECTED]
int;
>
> DECLARE @[EMAIL PROTECTED]
nvarchar(200);
>
> SET @[EMAIL PROTECTED]
= 0;
>
> SET @[EMAIL PROTECTED]
= '';
>
> -- get the record count in the dbo.NewCustTracker table
>
> SET @[EMAIL PROTECTED]
= (SELECT count(*) FROM dbo.NewCustTracker);
>
> BEGIN TRANSACTION insertIntoNewCustTracker
>
> IF (@[EMAIL PROTECTED]
> 0)
>
> BEGIN
>
> -- get the info in the NewCustTracker table...
>
> SET @[EMAIL PROTECTED]
= (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,@[EMAIL PROTECTED]
);
>
> 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 @[EMAIL PROTECTED]
> 0
>
> COMMIT TRANSACTION insertIntoNewCustTracker;
>
> END
>
> END
>
> GO
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
> - - - - - - - - - - -
>
> Any ideas/suggestions are appreciated.
>
>
>
>
>
> Thanks,
>
> Ben
>
>
>
>
>
>
|
