December 11, 2002 at 12:59 pm
Hello,
I have an Access 2000 Project interface to a database in SQL Server 2000 in which the user will generate an error from the Project form when an attempt is made to enter duplicate records in a table against a field with a Unique constraint against the field. The error message might look something like this from the Access Project:
"Violation of UNIQUE KEY constraint 'UN_tblMMStudentTestScoresCOPY'. Cannot insert duplicate key in object 'tblMMStudentTestScores'."
This error message matches the one coming from the server when the same record insertion is attempted in the QA, so I'm guessing that the error message I see coming from the Access Project is generated by SQL Server.
I have been asked to make this particular 'Violation' error more friendly. My first attempt at this was to create a trigger attached to a 'practice' table called 'tblMMStudentTestScoresCOPY', in which the trigger would raise an error if the user attempted to enter a duplicate record into the table. The code for the table looks like this:
***********************************
CREATE TABLE [tblMMStudentTestScoresCOPY] (
[PERMNUM] [varchar] (12) NOT NULL,
[TestShortName] [nvarchar] (8) NOT NULL ,
[TestScore] [smallint] NULL ,
[DateEntered] [smalldatetime] NOT NULL ,
CONSTRAINT [UN_tblMMStudentTestScoresCOPY] UNIQUE NONCLUSTERED
(
[PERMNUM],
[TestShortName]
) ON [PRIMARY]
******************************************
My first attempt at the Trigger looks like this:
CREATE TRIGGER tblMMStudentTestScoresCOPY_dup
ON tblMMStudentTestScoresCOPY
FOR INSERT
AS
IF EXISTS
(SELECT 'true'
FROM Inserted IR
INNER JOIN tblMMStudentTestScoresCOPY TS
ON IR.Permnum=TS.Permnum
AND IR.TestshortName=TS.TestshortName
WHERE TS.Testshortname is not null
AND TS.Permnum is not null )
Begin
RAISERROR('You already have this student entered',16,1)
ROLLBACK TRAN
End
I created the trigger, then tried to run an insert statement against tblMMStudentTestScoresCOPY that violated the Unique constraint, but still received the former server error message.
The parameter at 'RAISERROR' was not created with 'sp_admessage'. Does it need to be? How should I go about creating a custom message to fire when the user violates the constraint?
Thanks.
CSDunn
December 11, 2002 at 4:11 pm
If you use raiserror() in a trigger, the result error number 50000 is sent back to the caller. Use the SP mentioned to add your custom error message in the trigger.
Generally, you want to avoid issuing things in a trigger that the end user has to deal with. End user in this case is the calling app.
December 11, 2002 at 4:48 pm
Don is right if you want the message to have a specific number. But what I understand is that your message isn't fired but the generic one for Unique constraint violated. This is because a constraint is processed before the trigger is fired.
To do yourself remove the constriant (yes I know what about unique data). Instead place an index (do not set to unique and do trigger like so)
CREATE TRIGGER tblMMStudentTestScoresCOPY_dup
ON tblMMStudentTestScoresCOPY
FOR INSERT
AS
IF (SELECT COUNT(*)
FROM Inserted IR
INNER JOIN tblMMStudentTestScoresCOPY TS
ON IR.Permnum=TS.Permnum
AND IR.TestshortName=TS.TestshortName
WHERE TS.Testshortname is not null
AND TS.Permnum is not null ) > 1
Begin
RAISERROR('You already have this student entered',16,1)
ROLLBACK TRAN
End
The reason for using Count > 1 is that the trigger can see the newly inserted row as well so EXISTS picks it up. Since must be unique COUNT > 1 means it cannot exists more than 1 time in the DB counting even the currently inserted.
December 11, 2002 at 8:32 pm
I think the easiest way to accomplish your goal is to check for the existing record inyour application and issue the message from there. This is a pretty common way to handle that business requirement and is usually easier to understand and maintain.
Triggers are way cool though.
December 12, 2002 at 6:31 am
I agree. I usually trap such errors in my app before calling upd or if using a stored proc then I trap in the proc and use RAISERROR to notify user.
Far away is close at hand in the images of elsewhere.
Anon.
December 12, 2002 at 7:23 am
Could change the message in sysmessages ?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
December 12, 2002 at 10:54 am
quote:
I agree. I usually trap such errors in my app before calling upd or if using a stored proc then I trap in the proc and use RAISERROR to notify user.
I have not done much with error trapping up to this point. Do you have any examples you could share? Basically, I want to prevent duplicate records from entering a table, want to raise a custom error, and rollback the transaction if an error occurs. Thanks. cd.
December 12, 2002 at 11:45 am
In your case I would use the folowing proc
CREATE proc tblMMStudentTestScoresCOPYInsert
(
@PERMNUM varchar (12),
@TestShortName nvarchar (8),
@TestScore smallint,
@DateEntered smalldatetime
)
as
IF EXISTS
(SELECT *
FROM tblMMStudentTestScoresCOPY
WHERE PERMNUM = @PERMNUM
AND TestShortName = @TestShortName)
Begin
RAISERROR('You already have this student entered',16,1)
RETURN
End
INSERT tblMMStudentTestScoresCOPY
(PERMNUM,TestShortName,TestScore,DateEntered)
VALUES (@PERMNUM,@TestShortName,@TestScore,@DateEntered)
GO
and trap the error in the app.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply