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
)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
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.
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
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...
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
AFTER INSERT, UPDATE
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
An alternative might be to use a computed column, example below
DROP TABLE IF EXISTS #WIBBLE
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)
) AS D(Code)
SELECT * FROM #WIBBLE;
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 (
from otherdatatable P (NOLOCK)
where code is not null