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

Question

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

Answer 1

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

Answer 2

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.