How to retrieve SQL trigger error in VBA?

Hi Experts,
I Have the following in a tables trigger
After Insert
SELECT @title = title,@licNum = licensenum, @resume = resume,@lastname=lastname,@firstname=firstname,@ssn=socialsecuritynumber
FROM  inserted i

IF @title is null or ltrim(rtrim(@title)) =''
BEGIN
   RAISERROR ('Please enter Title', 0, 1)
ROLLBACK TRANSACTION
END

Open in new window

Now my question is, when inserting a record from an Access FE, how can I get this error message, instead of getting "The transaction ended in the trigger. The batch has been aborted."?

FYI- The following is the code used in the app to insert the new record.
strSql = "Insert into EmployeesTbl (EmployeesID, Title)"
                    strSql = strSql & " Values (" & rs2(0) & ",'" & Obj & "')"
                    'On Error GoTo 0
                    CurrentProject.Connection.Execute strSql

Open in new window

LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
Use a try catch:
https://msdn.microsoft.com/en-us/library/ms175976.aspx

sql would be:

begin try
   --insert
select @@identity as result
end try
begin catch
 SELECT ERROR_MESSAGE() AS result
end catch
0
ste5anSenior DeveloperCommented:
Drop the trigger. And use declarative measures. Declare your column as NOT NULL and add a CHECK constraint. E.g.

USE tempdb;
GO

CREATE TABLE test 
	(
		ID INT NOT NULL,
		TitleName VARCHAR(255) NOT NULL CONSTRAINT CK_TitleName CHECK ( LEN(LTRIM(TitleName)) != 0 ),
		Payload VARCHAR(255)
	);
GO

INSERT INTO test VALUES ( 1, 'Test', '');
SELECT * FROM test;
GO

INSERT INTO test VALUES ( 1, '', '');
SELECT * FROM test;
GO

DROP TABLE test;
GO

Open in new window


Then you will get an appropriate error.

btw, your trigger contains an architural flaw: Triggers are execute per statement. Thus you can have a batch insert, but your trigger will be executed once. In this case your SELECT @varname will test only one arbitrary row and depending on this row will pass all the others even when they are wrong.
0
bfuchsAuthor Commented:
Hi Experts,
First of all, thanks for replying..

@Kyle, ste5an
I am actually not so familiar with t-sql, the code was designed by a temp consultant, while I do maintain the DB and do some times small changes whenever I feel comfortable, this is something that will really affect the users in case something does not function as desired.

Below is the full contents of the trigger, is it possible for you to help me re-design  according to those suggestions?
GO
/****** Object:  Trigger [dbo].[trgEmployees]    Script Date: 03/25/2015 17:18:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trgEmployees] ON [dbo].[Employeestbl]
after INSERT
AS
DECLARE @title varchar(50),
   @resume varchar(50),
	@lastname varchar(50),
	@firstname varchar(50),
	@ssn varchar(15),
	@licNum varchar(50) ,
	@err varchar(2000),
	@existFirstname varchar(50),
	@existLastname varchar(50),
@count int
SELECT @title = title,@licNum = licensenum, @resume = resume,@lastname=lastname,@firstname=firstname,@ssn=socialsecuritynumber
FROM  inserted i 

IF @title is null or ltrim(rtrim(@title)) =''
BEGIN
   RAISERROR ('Please enter Title', 0, 1)
ROLLBACK TRANSACTION
END

set @count =0

select @count=count(*) from Employeestbl where lastname=@lastname and firstname=@firstname

if @count > 1 
begin
	select @err='Employee record already exists with name  ' + @lastname + ',' + @firstname
	RAISERROR (@err, 0, 1)
	ROLLBACK TRANSACTION
end

set @count =0
IF @ssn is not null or ltrim(rtrim(@ssn)) <> ''
BEGIN
select @count=count(*) from Employeestbl where socialsecuritynumber=ltrim(rtrim(@ssn))

if @count > 1 
begin
	select @existFirstname=''
	select @existLastname =''
	select @existFirstname=firstname,@existLastname=lastname from Employeestbl where lastname!=@lastname and firstname!=@firstname and socialsecuritynumber=ltrim(rtrim(@ssn))
	select @err='Employee record already exists with this ssn  For  ' + @existLastname + ',' + @existFirstname
	RAISERROR (@err, 16, 1)
	ROLLBACK TRANSACTION
end
end
IF @licNum is not null or ltrim(rtrim(@licNum)) <> ''
BEGIN
  	set @count =0
	select @count=count(*) from Employeestbl where licensenum = @licNum	and title = @title
	if @count > 1 
		begin
			select @existFirstname=''
	select @existLastname =''
	select @existFirstname=firstname,@existLastname=lastname from Employeestbl where lastname!=@lastname and firstname!=@firstname and licensenum = @licNum
			select @err='Employee record already exists with this Licence Number   For  ' + @existLastname + ',' + @existFirstname
			RAISERROR (@err, 0, 1)
			ROLLBACK TRANSACTION
		end
END

Open in new window

P.S. Note the trigger also checks for duplicate values in the table, not sure if this can be handled with ste5an's solution.

Thanks,
Ben
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ste5anSenior DeveloperCommented:
hmm, from a consultant? I guess you should try to get your money back..

The basic problem is that your table needs to be changed. So please post the table DDL and describe your requirements which should be implemented by that trigger.

Just to be clear: The tigger will allow wrong data to be inserted.
0
bfuchsAuthor Commented:
Attached the DDL,

Actually from what I see, The trigger is preventing the following:

A-Null values on Title field.
B-Duplicate values in FirstName+LastName columns
C-Duplicate Values in SocialSecurityNumber field.
D-Duplicate values in LicenseNum Field.

I guess this could all be handled by creating Unique Indexes, correct?

I will have to check first if there aren't any existing records violating those rules.

In the end, by doing the above, how will I be able to see what the error is from my FE app code?

P.S. I know I know, you will all comment that its a whole mess...It does not belong all in one table, however this is a long story, and will worry another time how to re-arrange this.

Thanks,
Ben
EmpDDL.sql
0
bfuchsAuthor Commented:
The tigger will allow wrong data to be inserted.
Thanks for pointing out that, If your solution will eliminate this, would be great!!
0
bfuchsAuthor Commented:
OK, just did some queering and found out there are data violating those rules (that proves ste5an is right..) ,

I guess will have to make sure users clean out that before applying those indexes.

So what do you think should I do next in order to get those error messages displayed in my app?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
I agree with what st5an is saying on the overall basis.

The only one that couldn't be solved with unique constraints is the Title because you don't want it to be null OR blank.  Ideally that should be handled from the application side . . . don't let them submit without having something in that field.  I suppose you could hack it by having a dummy row with a blank title and setting the title to not null, but I wouldn't take that route.

If you violate a unique constraint the error will contain the constraint that was actually violated (see link below)

You can use the on error go to statement (http://www.cpearson.com/excel/errorhandling.htm) and then look at the error message.  Based on the error code (eg: if it contains SSN) . . . you know the SSN constraint was violated and can display a friendly message to the user.

The other way is still to handle the error in a try catch on the sql server.  The below link demonstrates methods on how to capture those errors and put them in a table.  you can combine that with things like the userid who called the statement to do some kind of filtering on it.

https://www.simple-talk.com/sql/t-sql-programming/handling-constraint-violations-and-errors-in-sql-server/

Hope that helps.
0
ste5anSenior DeveloperCommented:
@Kyle: Not a UNIQUE, but a CHECK for non-empty strings.

@bfuchs: Looks like an upsized table. Is this a table in DW (OLAP) or a normal system (OLTP)?

When it's OLAP, then we don't need those constraints nor the trigger, because the ETL process should handle integrity.
When it's a normal system, then the table is not properly normalized.

Yes. Contraints and indices can handle this. E.g.

-- Test A: Title must be NOT NULL and contain a value different then ''.
ALTER TABLE  dbo.Employeestbl
	ALTER COLUMN Title VARCHAR(50) NOT NULL;
GO

ALTER TABLE  dbo.Employeestbl
	ADD CONSTRAINT CK_Employeestbl_Title 
	CHECK ( LEN(LTRIM(Title)) > 0);
GO

-- Test B: LastName + FirstName must be unique.
-- This test implies that both columns must not be NULL under normal circumstances.
ALTER TABLE  dbo.Employeestbl
	ALTER COLUMN LastName VARCHAR(50) NOT NULL;
GO

ALTER TABLE  dbo.Employeestbl
	ALTER COLUMN FirstName VARCHAR(50) NOT NULL;
GO

ALTER TABLE  dbo.Employeestbl
	ADD CONSTRAINT UQ_Employeestbl_Name 
	UNIQUE ( LastName ASC, FirstName ASC );
GO

-- Test C: No duplicates in SSN.
ALTER TABLE  dbo.Employeestbl
	ADD CONSTRAINT CK_Employeestbl_SocialSecurityNumber 
	CHECK ( SocialSecurityNumber IS NULL OR LEN(LTRIM(SocialSecurityNumber)) > 0);
GO

CREATE UNIQUE NONCLUSTERED INDEX UQ_Employeestbl_SocialSecurityNumber
	ON dbo.Employeestbl ( SocialSecurityNumber ASC )
	WHERE SocialSecurityNumber IS NOT NULL;
GO

-- Test D: No duplicates in LicenseNum.
-- The trigger test this per title??
ALTER TABLE  dbo.Employeestbl
	ADD CONSTRAINT CK_Employeestbl_LicenseNum
	CHECK ( LicenseNum IS NULL OR LEN(LTRIM(LicenseNum)) > 0);
GO

CREATE UNIQUE NONCLUSTERED INDEX UQ_Employeestbl_LicenseNum 
	ON dbo.Employeestbl ( LicenseNum ASC, Title ASC )
	WHERE LicenseNum IS NOT NULL;
GO

Open in new window


Caveat: As always, make backups first.
0
bfuchsAuthor Commented:
Hi Experts,

I think you basically solved most of my problems, as putting the rules in the constraint/index enables me to have access to the error message, as opposed to the current method of raising error in trigger.

I am not sure why it was done initially that way...

@ste5an,

First thanks for providing those detailed scripts ready to use.

While trying them in a test DB I got the following errors.

Test A:
The ALTER TABLE statement conflicted with the CHECK constraint "CK_Employeestbl_Title". The conflict occurred in database "TESTNP", table "dbo.Employeestbl", column 'Title'.

Test B:
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Employeestbl' and the index name 'UQ_Employeestbl_Name'. The duplicate key value is (<NULL>, <NULL>).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.

Just want to make sure its only because data violating the rule, and nothing else?

Also re restricting null and zero length values, I am not sure why did you include them, as this is not a buss requirement, perhaps the unique index only works if we impose this restriction?

The same question I have in the other fields where you included that restriction.

Test C:
Incorrect syntax near the keyword 'WHERE'.

Test D:
Same error as TestC

Thanks,
Ben
0
bfuchsAuthor Commented:
@ste5an,

Re you comments about the table structure..
You're right, its an up-sized normal system table,

Basically the reason we got to that is that there is never time to spend on normalize it, actually its everything a one to one relationship, however I agree that it should be split up into various tables, depending on the type of info.

However the way its being operated here is...that every now and then they have a requirement to add just two tree fields.. and the manager would not approve spending on redesigning the DB, (never mind the FE App and everything that this could involve..and the longer we wait the more complicate it gets to perform it in the FE, and the more he will refuse to approve it..),
0
bfuchsAuthor Commented:
BTW,

I will be out of the office tom, will check on my emails & reply next week.

Thank you
Have a nice weekend!
0
Vadim RappCommented:
Change 2nd parameter of raiserror to 16:

 RAISERROR ('Please enter Title', 16, 1)

(this is response to the original question)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
@Vadim,

Great, this looks really like an immediate solve to the problem (I don't have to wait until user corrects data in order to apply indexes).
Just wondering are they any disadvantages of changing it from 0 to 16?
0
Vadim RappCommented:
> are they any disadvantages of changing it from 0 to 16?

No disadvantages. Error severities are described in https://msdn.microsoft.com/en-us/library/ms164086.aspx, and as you can see, severity 16 "Indicates general errors that can be corrected by the user", which is what you probably wanted. In programming, return code 0 traditionally meant "success", so Access developers probably decided that there's nothing to show.
0
bfuchsAuthor Commented:
Thanks Vadim,
Will change it & have users test it tom.
0
Vadim RappCommented:
Taking into account possibility of multiple records, better do it like this:

After Insert
if exists(select 1 from inserted where isnull(title,'')='' )
BEGIN
  RAISERROR ('Please enter Title', 16, 1)
  ROLLBACK TRANSACTION
END
0
bfuchsAuthor Commented:
Taking in consideration that multiple inserts are only used here when we do an import from an external source (like excel file), I think its better later on to have this check in the index or column property, otherwise I will end up not having any records imported in case one of them violates the rules..

However the main point here was to provide user with proper error message when entering (or importing) one record at a time, and this should serve the purpose.
0
Vadim RappCommented:
I'm pretty sure that import from external source would go one record at a time.

Even if not, the result would be the same: if you run an sql affecting multiple records, and one record violates check constraint or unique index, the whole transaction is rolled back.
0
bfuchsAuthor Commented:
@Vadim,

For some technical reasons, users were not able to test it today, hopefully in the next day or two will finalize it.

Thanks,
Ben
0
bfuchsAuthor Commented:
That finally worked,
Thanks to all participants!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.