SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I remove these RAISERROR 44444?


Can I remove these RAISERROR 44444?

Author
Message
Rod
Rod
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2706 Visits: 2059
We're working on migrating our database from SQL 2005 to SQL 2012. One issue that's come up is triggers, on some of the tables. This database started it's life as a SQL Server 6.5 database, and it's got somethings in some triggers to enforce database constraints. e.g.:


USE [ClientData]
GO
/****** Object: Trigger [dbo].[Referral_ITrig] Script Date: 09/05/2013 13:27:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** Object: Trigger dbo.Referral_ITrig Script Date: 3/27/99 11:42:10 AM ******/
/****** Object: Trigger dbo.Referral_ITrig Script Date: 2/21/97 10:07:39 AM ******/
/* Last part modified by mm May 29, 03 and May 30, 03 */
/* Last part modified by mm July 1, 03 */

ALTER TRIGGER [dbo].[Referral_ITrig] ON [dbo].[Referral] FOR INSERT AS
/*
* Code added by Rod Falanga.
*/
DECLARE
@nCount smallint,
@lClientNumber int,
@nCaseNumber smallint,
@lfkAdmission int,
@nReferralStatus smallint,
@nProviderNumber smallint
/*
* PREVENT NULL VALUES IN 'ClientNumber'
*/
IF (SELECT Count(*) FROM inserted WHERE ClientNumber IS NULL) > 0
BEGIN
RAISERROR 44444 'Field ''ClientNumber'' cannot contain a null value.'
ROLLBACK TRANSACTION
END
ELSE
/*
* PREVENT NULL VALUES IN 'CaseNumber'
*/
IF (SELECT Count(*) FROM inserted WHERE CaseNumber IS NULL) > 0
BEGIN
RAISERROR 44444 'Field ''CaseNumber'' cannot contain a null value.'
ROLLBACK TRANSACTION
END
ELSE
/*
* PREVENT NULL VALUES IN 'ProviderNumber'
*/
IF (SELECT Count(*) FROM inserted WHERE ProviderNumber IS NULL) > 0
BEGIN
RAISERROR 44444 'Field ''ProviderNumber'' cannot contain a null value.'
ROLLBACK TRANSACTION
END
ELSE
/*
* PREVENT NULL VALUES IN 'ReferralStatus'
*/
IF (SELECT Count(*) FROM inserted WHERE ReferralStatus IS NULL) > 0
BEGIN
RAISERROR 44444 'Field ''ReferralStatus'' cannot contain a null value.'
ROLLBACK TRANSACTION
END
/*
* PREVENT INSERTS IF NO MATCHING KEY IN 'Providers'
*/
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM Providers, inserted WHERE (Providers.ProviderNumber = inserted.ProviderNumber AND Providers.Facility = inserted.Facility))
BEGIN
RAISERROR(777708, 16, 1)
ROLLBACK TRANSACTION
END



Now, I'm thinking that SQL 6.5 implemented database contraints using that
RAISERROR 44444 'Field ''ProviderNumber'' cannot contain a null value.'


syntax, and I'm guessing that SQL 2012 doesn't do it that way. In fact, I'm wondering if SQL 2008 even does it that way?

Anyway, can I remove all of that SQL code in the triggers, even in SQL 2005? Or do we have to leave it there, until we migrate it to SQL 2012?

Kindest Regards,Rod
Connect with me on LinkedIn.
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4371 Visits: 3668
The RAISERROR command in your trigger raises an error with a user defined number. This error is not a standard SQL error, but RAISERROR can still be done in SQL2012.
The trigger tests for the value of some inserted fields and an error is raised if a NULL value is entered. You could replace this functionality by adding constraints to the table. Just make the appropriate columns NOT NULLABLE. It depends on your application if it can handle the different approach on this NULL-value checking. You have to test this for yourself.
The last part of the trigger is checking for inserted values in another table. This could be replaced by defining a foreign-key relation between both tables.

So if you replace the functionality of the trigger by other ways (described above) you probably are able to completely remove the trigger. But this has to be tested to know for sure.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Rod
Rod
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2706 Visits: 2059
HanShi (9/5/2013)
The RAISERROR command in your trigger raises an error with a user defined number. This error is not a standard SQL error, but RAISERROR can still be done in SQL2012.
The trigger tests for the value of some inserted fields and an error is raised if a NULL value is entered. You could replace this functionality by adding constraints to the table. Just make the appropriate columns NOT NULLABLE. It depends on your application if it can handle the different approach on this NULL-value checking. You have to test this for yourself.
The last part of the trigger is checking for inserted values in another table. This could be replaced by defining a foreign-key relation between both tables.

So if you replace the functionality of the trigger by other ways (described above) you probably are able to completely remove the trigger. But this has to be tested to know for sure.


The weird thing is that all of the columns that this trigger checks for, already have a constraint prohibiting the value from being null. That's why I'm thinking that this was put in by some old version of SQL Server, and I'm guessing that the older version enforced that constraint using triggers. Nevertheless you've answered my question, I can replace those triggers with constraints. Thank you.

Kindest Regards,Rod
Connect with me on LinkedIn.
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18344 Visits: 14893
Rod at work (9/6/2013)
HanShi (9/5/2013)
The RAISERROR command in your trigger raises an error with a user defined number. This error is not a standard SQL error, but RAISERROR can still be done in SQL2012.
The trigger tests for the value of some inserted fields and an error is raised if a NULL value is entered. You could replace this functionality by adding constraints to the table. Just make the appropriate columns NOT NULLABLE. It depends on your application if it can handle the different approach on this NULL-value checking. You have to test this for yourself.
The last part of the trigger is checking for inserted values in another table. This could be replaced by defining a foreign-key relation between both tables.

So if you replace the functionality of the trigger by other ways (described above) you probably are able to completely remove the trigger. But this has to be tested to know for sure.


The weird thing is that all of the columns that this trigger checks for, already have a constraint prohibiting the value from being null. That's why I'm thinking that this was put in by some old version of SQL Server, and I'm guessing that the older version enforced that constraint using triggers. Nevertheless you've answered my question, I can replace those triggers with constraints. Thank you.


If the columns are already defined as NOT NULL then the code in the trigger is never doing anything anyway. The NOT NULL constraint will fire before the trigger for all those columns.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Rod
Rod
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2706 Visits: 2059
Thanks, Jack, I'll get rid of those things.

Kindest Regards,Rod
Connect with me on LinkedIn.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16073 Visits: 19534
justinricardo016 (10/6/2016)
This is the site that anyone who is interested in learning more about SQL server 2005 should care enough to visit. I admire the way that members of this forum are active in group discussions. This is something to be encouraged. Are you in need of the reliable Coursework Writers? If yes, then click on this link: Best Coursework Writers


Reported as spam - the other spam posts on this thread too.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search