在这种情况下如何改变功能或表格？ [英] How to alter the function or the table in this situation?

问题描述

你好。

我的数据库中有这个表：

Hello.
I have this table in my database :

CREATE TABLE [dbo].[Circulation](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Document_Id] [int] NULL,
	[Person_Id] [int] NULL,
	[Librarian_Id] [int] NULL,
	[StartDate] [datetime] NULL,
	[EndDate]  AS ([StartDate]+[Time]),
	[Time] [int] NULL,
	[Delay]  AS ([dbo].[CalculateDelay]([Id],[IsReturned])),
	[IsReturned] [bit] NULL,
 CONSTRAINT [PK_Circulation] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

以及此功能：

and also this function :

ALTER FUNCTION [dbo].[CalculateDelay]
    (
      @Id INT ,
      @IsReturned BIT
    )
	RETURNS INT
AS
    BEGIN
        IF ( @IsReturned = 0 )
            BEGIN
                DECLARE @delay INT = ( SELECT   ( DATEDIFF(minute,
                                                           DATEADD(day, [Time],
                                                              [StartDate]),
                                                           GETDATE()) )
                                       FROM     dbo.Circulation
                                       WHERE    Id = @Id
                                     )
                        RETURN @delay
            END
			ELSE
			BEGIN
			RETURN NULL
			END
			RETURN NULL
    END

我的问题是如果我想改变这个功能，会显示以下错误：



Msg 3729，Level 16，State 3，程序CalculateDelay，第1行

不能改变'dbo.CalculateDelay'，因为它被对象'Circulation'引用。



如果我想删除公式，则会显示以下错误：



'发行'表

- 无法修改表格。

超出最大存储过程，函数，触发器或视图嵌套级别（限制32）。





如何更改表格或更改功能？

my problem is that if I want to alter the function, the following error display :

"Msg 3729, Level 16, State 3, Procedure CalculateDelay, Line 1
Cannot ALTER 'dbo.CalculateDelay' because it is being referenced by object 'Circulation'."

and if I want to remove the formula, this error display :

"'Circulation' table
- Unable to modify table.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
"

How can I alter the table or alter the function ?

推荐答案

流通指的是 CalculateDelay 字段，因此你将无法改变这个字段。

Circulation is referring CalculateDelay field, thus you will not be able to alter this field.

从来没有rtied这个，而不是从中获取时间和开始日期表，尝试将它们作为参数发送到函数。例如：

Never rtied this but instead of fetching the time and startdate from the table, try sending them to the function as parameters. Something like:

ALTER FUNCTION [dbo].[CalculateDelay]
    (
      @IsReturned BIT,
      @StartDate DATETIME
      @Time INT      
    )
	RETURNS INT
AS
    BEGIN
        IF ( @IsReturned = 0 )
            BEGIN
                DECLARE @delay INT = ( SELECT   ( DATEDIFF(minute,
                                                           DATEADD(day, @Time,
                                                              @StartDate),
                                                           GETDATE()) )
                                     )
                        RETURN @delay
            END
			ELSE
			BEGIN
			RETURN NULL
			END
			RETURN NULL
    END

如果这没有帮助，我建议将计算移动到触发器。

If that doesn't help, I'd suggest to move the calculation to a trigger.

这篇关于在这种情况下如何改变功能或表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！