c. At the front-end, MS Access, captured the error code and error message
using variant variables
This is the general overview of the things I did to make it work.
Below are the scripts:
[INSERT TRIGER SCRIPT]
----------------------------------------
CREATE TRIGGER [trTrackInsert]
ON [dbo].[Customers]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @[EMAIL PROTECTED]
int;
DECLARE @[EMAIL PROTECTED]
nvarchar(200);
SET @[EMAIL PROTECTED]
= 0;
SET @[EMAIL PROTECTED]
= '';
SET @[EMAIL PROTECTED]
= (SELECT count(*) FROM dbo.NewCustTracker);
BEGIN TRANSACTION insertIntoNewCustTracker
IF (@[EMAIL PROTECTED]
> 0)
BEGIN
SET @[EMAIL PROTECTED]
= (SELECT UserName FROM dbo.NewCustTracker);
RAISERROR(70000,16,1,@[EMAIL PROTECTED]
) WITH SETERROR;
END
ELSE
BEGIN
INSERT INTO dbo.NewCustTracker(CustNum, UserName)
SELECT [Customer Number], user_name() FROM inserted;
IF @[EMAIL PROTECTED]
> 0
COMMIT TRANSACTION insertIntoNewCustTracker;
END
END
[UPDATE TRIGGER SCRIPT]
--------------------------------------------
CREATE TRIGGER [trUpdateCustomer]
ON [dbo].[Customers]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @[EMAIL PROTECTED]
nvarchar(15);
DECLARE @[EMAIL PROTECTED]
int;
DECLARE @[EMAIL PROTECTED]
nvarchar(50);
DECLARE @[EMAIL PROTECTED]
nvarchar(34);
SET @[EMAIL PROTECTED]
= (SELECT [Customer Number] FROM deleted)
SET @[EMAIL PROTECTED]
= (SELECT count(*) FROM dbo.NewCustTracker WHERE [CustNum] =
@[EMAIL PROTECTED]
)
IF (@[EMAIL PROTECTED]
> 0)
BEGIN
IF (UPDATE([Last Name]) OR UPDATE(Company))
DELETE FROM dbo.NewCustTracker;
END
END
[STORED PROCEDURE SCRIPT]
--------------------------------------------------
CREATE PROCEDURE [dbo].[sp_InsertNewCustomer]
@[EMAIL PROTECTED]
nvarchar(15),
@[EMAIL PROTECTED]
int OUTPUT,
@[EMAIL PROTECTED]
nvarchar(4000) OUTPUT
AS
BEGIN TRY
INSERT INTO Customers ([Customer Number], Active, [Customer Record Type])
VALUES (@[EMAIL PROTECTED]
1, 'Both')
END TRY
BEGIN CATCH
SELECT @[EMAIL PROTECTED]
= ERROR_NUMBER(), @[EMAIL PROTECTED]
= ERROR_MESSAGE();
END CATCH
[MS ACCESS FRONT-END SCRIPT]
-----------------------------------------------------
....
....
Dim errorNumber As Variant, errorMessage As Variant
Dim errMsgNum As String
....
....
Set com = New ADODB.Command
With com
.ActiveConnection = <your connection string here...>
.CommandText = "sp_InsertNewCustomer"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@[EMAIL PROTECTED]
") = NextCustID
.Execute
End With
errorNumber = 0
If Not IsNull(com.Parameters.Item(2).Value) Then
errorNumber = com.Parameters.Item(2).Value
errMsgNum = Str(errorNumber)
errorMessage = com.Parameters.Item(3).Value
Set com = Nothing
'Check if there is an error returned from the addition of a new
customer...
If errorNumber <> 0 Then
MsgBox "Encountered error: " + errMsgNum + ". " +
errorMessage,
_
vbOKOnly, "New Customer Warning!"
End If
End If
....
....
....
As we all know, there are other ways to solve the task even better ways.
But this serves my own purpose.
"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
>
>
>
>
>
>
|
