Cannot ALTER 'dbo.udfMYUDF' because it is being referenced by object 'MyCheckConstraint '.
sloan
I have some code below.
First off, I KNOW what the issue is. And I know one solution is to "Drop
the Constraint" before doing the alter, and then re-adding the Constraint.
However, instead of dropping the constraint and readding it, I am wondering
if there is a way to "check for alterability".
Look for the line (that I have commented out):
--IF ( IsAlterable ( 'dbo.udfIsValidCodePerCodeCategory' > 0)
and you'll see what I'm getting at.
I don't think it exists, but thought I'd ask just to make sure.
Right now, I'm gonna have to check for the existence of a know
constraint...as in:
if NOT exists (select * from sysobjects where name like
'CK_Employee_MacroStatusCodeKey' and xtype = 'C') --//Check for KNOWN
Constraint Name
begin
print 'Try to Alter the UDF Here'
end
GO
But that seems .... deficient............
------------// START TSQL HERE
if exists (select * from dbo.sysobjects where id = object_id(N'[Employee]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Employee]
GO
if NOT exists (select * from sysobjects
where id = object_id('dbo.udfIsValidCodePerCodeCategory') and sysstat & 0xf
= 0)
BEGIN
print 'Creating the stubbed version of udfIsValidCodePerCodeCategory'
EXEC ( 'CREATE FUNCTION dbo.udfIsValidCodePerCodeCategory (@i as
uniqueidentifier , @j as int ) RETURNS bit AS BEGIN RETURN 0 END')
END
GO
ALTER FUNCTION dbo.udfIsValidCodePerCodeCategory (@CodeCategoryUUID
uniqueidentifier , @CodeKey int )
RETURNS bit AS
BEGIN
declare @isOK bit
select @isOK = 0
if @CodeKey > 0 -- << Simplified logic for this demo code
begin
select @isOK = 1
end
return @isOK
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[Employee]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Employee]
GO
CREATE TABLE [dbo].[Employee] (
[EmployeeUUID] [uniqueidentifier] NOT NULL ,
[EmployeeLastName] [varchar] (64) NOT NULL ,
EmployeeCodeKey smallint NULL
)
GO
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT PK_Employee_MQUUID PRIMARY KEY
CLUSTERED
( [EmployeeUUID] )
GO
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT
[EMPLOYEE_EmployeeLastName_UNIQUE] UNIQUE NONCLUSTERED
( [EmployeeLastName] )
GO
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [DF_Employee_MQUUID] DEFAULT
(newsequentialid()) FOR [EmployeeUUID]
GO
GRANT SELECT , INSERT, UPDATE, DELETE ON dbo.Employee TO public
GO
ALTER TABLE dbo.Employee
ADD CONSTRAINT [CK_Employee_MacroStatusCodeKey ] CHECK
([dbo].[udfIsValidCodePerCodeCategory]('00000000-0000-0000-0000-000000000000'
, [EmployeeCodeKey] ) != 0)
GO
--IF ( IsAlterable ( 'dbo.udfIsValidCodePerCodeCategory' > 0)
--BEGIN
ALTER FUNCTION dbo.udfIsValidCodePerCodeCategory (@CodeCategoryUUID
uniqueidentifier , @CodeKey int )
RETURNS bit AS
BEGIN
declare @isOK bit
select @isOK = 0
if @CodeKey < 0 -- << Simplified logic for this demo code
begin
select @isOK = 1
end
return @isOK
END
--END --//If Alterable
GO
Erland Sommarskog
>
> First off, I KNOW what the issue is. And I know one solution is to "Drop
> the Constraint" before doing the alter, and then re-adding the Constraint.
Another solution is not call UDF:s from constraints. :-)
> However, instead of dropping the constraint and readding it, I am
> wondering if there is a way to "check for alterability".
You should be able to check sys.sql_dependencies for the reference.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
sloan
them for production.
Sounds silly I guess, but it helped I.D. some troublespots...triggers might
have worked, but the UDF idea wasn't too bad.
The rules were a little too complex for an "inline" statement.
But thanks for the reminder about sys.sql_dependencies.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9BB91CD4...@127.0.0.1...
nor...@hotmail.com
Just wanted to introduce myself - I'm a long term reader of the website
but this is a first for me posting so be gentle!
I'm a busy mum of three, trying to fit it all in and pick my way
through the seeming unstoppable mess that surrounds me!
Peace out, Carol from 'Get Payday Loans Online'
(http://www.getpaydayloansonline.net)!
--
thomascarol
'No Fax Payday Loans'
(http://www.getpaydayloansonline.net/no-fax-payday-loans.html)
------------------------------------------------------------------------
thomascarol's Profile: http://microsoft-news.for-um.de/member.php?userid=561
View this thread: http://microsoft-news.for-um.de/showthread.php?t=79012
Powered by: http://poker-and-gambling-news.for-um.de/ | http://microsoft-news.for-um.de | http://ibm-news.for-um.de | http://www.for-um.de | http://www.uzenet.org