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.