Trigger to Update possible null column when inserting or updating

Transact-SQL
1

Hello
I want to avoid null or emty value in Column and set such by a defaut value
My idea is to set a trigger to do this on update or insert
But I'm not very familiar with trigger and I'm wondering if it is the best way to go

Here is my Table

CREATE TABLE [dbo].[ProductCodes](
[pkId] [int] IDENTITY(1,1) NOT NULL,

[CODE] varchar NULL,
[Name] varchar NULL,
PRIMARY KEY CLUSTERED
(
[pkId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

If the code is null or Empty ISNULL(Code,'') I want to set code to

Code='CODE-'+CAST(pkid AS Varchar)

Ho can I do this ?
Thanks for your help

2

Instead of resetting if a NULL value is passed - don't allow a NULL value...especially on a lookup table for Product Codes. This should require both the Code and the Name (Description?) to be populated.

Also - always specify a length for varchar/char data types. Not specifying the length will eventually cause you problems.

1 Like
3

I totaly agree with you but this table is just an example and in my specific environnement there might be business cases where some insert can be done without a required value

In this case I want to set the missing Value to a Known Prefix + PrimaryKey identity value

4

That doesn't make sense - if the value is required then it needs to be provided and that is the best way to handle this issue.

Direct inserts into the table shouldn't be allowed either - use a stored procedure to perform the insert and then you can 'generate' the values if needed.

Using a trigger is not the best approach...

1 Like
5

Thanks to providing comment but without solution ...

Anyway whatever it makes sense to you or not, I've found a solution
Now I agree that I need to analyse performance impact of this on "regular cases"

CREATE TRIGGER updModelHRK
ON [Model]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE [Model] SET HRK = 'HRK-'+RIGHT('0000000'+cast(i.id as varchar),7) FROM INSERTED AS i WHERE ISNULL(model.hrk,'')='' AND model.id = i.id
END

6

An alternative might be to use a computed column, example below

use tempdb
GO

DROP TABLE IF EXISTS #WIBBLE
GO

CREATE TABLE #WIBBLE (
	ID INTEGER NOT NULL IDENTITY(1,1)
	, CODE VARCHAR(20) NULL
	, DERIVEDCODE AS IIF(CODE IS NULL, CAST(ID AS VARCHAR(10)) + '-WIBBLE', CODE) PERSISTED
)


INSERT INTO #WIBBLE (CODE)
SELECT * 
FROM (
	VALUES
		('TEST')
		, (NULL)
		, ('TEST2')
		, (NULL)
) AS D(Code)

SELECT * FROM #WIBBLE;
7

you could put the table you want to import into a temp table and then do an update to match this table with an inner join like this for instance. You could put this update in a stored proc and populate the nulls as and when required ie weekly, daily or when you require.

update TABLE [dbo].[ProductCodes]
set code = tmp.newcode --
from #temp tp

	inner join (								
			select
                            p.data,
                             p.identifier 		
			from otherdatatable P (NOLOCK)		
			where code is not null
							
									
	)tmp

on tp.id=tmp.IDENTIFIER