How to retrieve SQL trigger error in VBA?

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

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

Select allOpen 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

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.

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

The tigger will allow wrong data to be inserted.

Thanks for pointing out that, If your solution will eliminate this, would be great!!

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?

@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

Select allOpen in new window

Caveat: As always, make backups first.

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

@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..),

BTW,

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

Thank you
Have a nice weekend!

@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?

> 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.

Thanks Vadim,
Will change it & have users test it tom.

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

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.

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.

@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

That finally worked,
Thanks to all participants!!