Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 1,036, Visits: 1,816
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: Thursday, September 1, 2016 7:08 AM
Points: 2,867, Visits: 3,347
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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 1,036, Visits: 1,816
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 @ 10:12 AM
Points: 10,793, Visits: 14,746
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
Post #1492842
Posted Tuesday, September 10, 2013 12:57 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 1,036, Visits: 1,816
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
Posted Tuesday, July 12, 2016 9:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:13 AM
Points: 1,072, Visits: 1,288
Wow, that trigger brings back memories! In SQL Server 6.0, all foreign keys had to be implemented by triggers. And after that in 6.5 and 7.0, cascade update and delete could only be performed by using a trigger instead of a foreign key. Since SQL 2000, all of that can be done by constraint.

This trigger has been redundant for years if you have not-null constraints on those columns and a foreign key to the other table. The constraints have to be satisfied before the trigger ever fires. But even in 6.5, all of what that trigger did could have been done with constraints.

So why is it there? My best guess is that the late-90's application that called it was not validating the data entry before executing SQL. If there were no constraints and just the trigger to enforce integrity, then those messages from the trigger (which are understandable to an end-user) would have been displayed by the application. Constraint errors are not so user-readable. That was one valid usage of triggers to enforce integrity when certain application development platforms made data validation especially awkward.
Post #1801196
Posted Thursday, August 18, 2016 5:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 18, 2016 5:45 AM
Points: 1, Visits: 0
شركة مكافحة حشرات بالرياض
شركة نقل عفش بالرياض
شركة كشف تسربات المياه باارياض
شركة تنظيف منازل بالرياض
شركة مكافحة حشرات بالرياض
شركة مكافحة حشرات بالدمام
شركة مكافحة النمل الابيض بالرياض
شركة مكافخة النمل الابيض بالرياض
شركة مكافحة حشرات بالرياض
شركة تنظيف مجالس بالرياض
شركة مكافحة فئران بالرياض
شركة تنظيف فلل بالرياض
شركة كشف تسربات المياه بالرياض
شركة تسليك مجاري بالرياض
شركة مكافحة حشرات بالدمام
شركة مكافحة حشرات بالاحساء
شركة مكافحة حشرات بالجبيل
شركة مكافحة حشرات بالرياض
شركة تنظيف مجالس بالرياض
شركة عزل خزانات بالرياض
شركة مكافحة الفئران بالرياض
تنظيف و مكافحة حشرات
شركات مكافحة الحشرات و التنظيف الشامل
تنظيف المنازل و مكافحة الحشرات و الخدمات الاخرى
تنظيف شامل و مكافحة حشرات
mine
مكافحة الحشرات و التنظيف الشامل
شركات تنظيف و صيانه و مكافحة حشرات
Post #1810454
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse