Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can I remove these RAISERROR 44444? Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 1:33 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1491934
Posted Thursday, September 5, 2013 11:57 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:38 AM
Points: 2,224, Visits: 2,658
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’! **
Post #1492040
Posted Friday, September 6, 2013 7:55 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1492251
Posted Monday, September 9, 2013 9:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #1492842
Posted Tuesday, September 10, 2013 12:57 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
Thanks, Jack, I'll get rid of those things.

Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1493334
Posted Wednesday, December 4, 2013 4:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 9:02 PM
Points: 3, Visits: 3
Jack Corbett (9/9/2013)
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.

thx alot jack


شركه تنظيف منازل بالرياض تنظيف شقق شركة تنظيف مكافحة البق مكافحة البق المنزلي
مبيد حشرى للصراصيرشركات مكافحة البق في جدةمكافحة النمل الابيض في المنزل
Post #1519833
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse