how to use a parameterized function for the Default Binding of a Sql Server column

I have a table that catalogs selected files from multiple sources. I want to record whether a file is a duplicate of a previously cataloged file at the time the new file is cataloged. I have a column in my table (“primary_duplicate”) to record each entry as ‘P’ (primary) or ‘D’ (duplicate). I would like to provide a Default Binding for this column that would check for other occurrences of this file (i.e. name, length, timestamp) at the time the new file is being recorded.

I have created a function that performs this check (see “GetPrimaryDuplicate” below). But I don’t know how to bind this function which requires three parameters to the table’s “primary_duplicate” column as its Default Binding.

I would like to avoid using a trigger. I currently have a stored procedure used to insert new records that performs this check. But I would like to ensure that the flag is set correctly if an insert is performed outside of this stored procedure.

How can I call this function with values from the row that is being inserted?

USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FileCatalog](
    [id] [uniqueidentifier] NOT NULL,
    [catalog_timestamp] [datetime] NOT NULL,
    [primary_duplicate] [nchar](1) NOT NULL,
    [name] [nvarchar](255) NULL,
    [length] [bigint] NULL,
    [timestamp] [datetime] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[FileCatalog] ADD  CONSTRAINT [DF_FileCatalog_id]  DEFAULT (newid()) FOR [id]
GO

ALTER TABLE [dbo].[FileCatalog] ADD  CONSTRAINT [DF_FileCatalog_catalog_timestamp]  DEFAULT (getdate()) FOR [catalog_开发者_开发技巧timestamp]
GO

ALTER TABLE [dbo].[FileCatalog] ADD  CONSTRAINT [DF_FileCatalog_primary_duplicate]  DEFAULT (N'GetPrimaryDuplicate(name, length, timestamp)') FOR [primary_duplicate]
GO


USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetPrimaryDuplicate] 
(
    @name nvarchar(255),
    @length bigint,
    @timestamp datetime
)
RETURNS nchar(1)
AS 
BEGIN

    DECLARE @c int

    SELECT @c = COUNT(*)
    FROM FileCatalog
    WHERE name=@name and length=@length and timestamp=@timestamp and primary_duplicate = 'P'

    IF @c > 0
        RETURN 'D' -- Duplicate

    RETURN 'P' -- Primary

END

GO

John, that isn't an answer to the question, and it's awfully presumptuous of you to assume that he should use a trigger. You don't know what he's trying to do or what reasons he might have for wanting to do it in default value.

If it's not possible, you might have said "That's not possible, so you should use a trigger instead" so he can actually learn something. I'm sure he knows as well as you do what triggers are and what they can be used for.

OP: Sorry, but I'm searching for the same information.

OK, I'm posting this 2.5 years after the question was first asked, but: have you considered using a calculated column for your primary_duplicate column, rather than a regular column with a default binding?

According to MSDN, "constant_expression in a DEFAULT definition cannot refer to another column in the table, or to other tables, views, or stored procedures."

A computed column, on the other hand, can.

Define your function as this:

CREATE FUNCTION [dbo].[GetPrimaryDuplicate] 
(
   @id  uniqueidentifier,
   @catalog_timestamp datetime,
   @name nvarchar(255),
   @length bigint,
   @timestamp datetime    
)
RETURNS nchar(1)
AS 
BEGIN

IF EXISTS (
    SELECT  1
    FROM    FileCatalog
    WHERE   name=@name and length=@length and timestamp=@timestamp 
        and catalog_timestamp < @catalog_timestamp
)
    RETURN 'D' -- Duplicate

  RETURN 'P' -- Primary

END

Then execute the following ALTER TABLE statement:

GO
ALTER TABLE [dbo].[FileCatalog] DROP   COLUMN primary_duplicate 
ALTER TABLE [dbo].[FileCatalog] ADD    primary_duplicate as dbo.GetPrimaryDuplicate(id, catalog_timestamp, name, length, timestamp)

You should use a trigger instead. The trigger will receive a copy of the inserted row.