September 5, 2013 at 1:33 pm
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.
September 5, 2013 at 11:57 pm
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.
September 6, 2013 at 7:55 am
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.
September 9, 2013 at 9:44 am
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
Consultant - Straight Path Solutions
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
October 6, 2016 at 5:51 am
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.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply