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
