Changing the column from NULL to NOT NULL when some data is inserted
I have a column Col1 nvarchar(10) null
I have to write a check constraint or trigger (I think it's not possible with check constraint), that will change the Col1 from null to not null when and only when some data is entered into the field or, rather, it will deny the column to get a null value after some non-null value is entered into the field.
It's because of application that first checks if that field is null, and if it is then it adds some value to it. After that the field can not be changed back to null.
For now I have the following:
create trigger [TRG_Col1_NotNull] on my.Table
instead of update
as
begin
if exists (
select * from inserted as i
where i.Col1 is null
)
raiserror ('You can not change the value of Col1 to null', 16, 1)
rollback transaction
end
Is this the best (or even correct) way to do this or is there any better and easier solution for this (maybe check constraint somehow)?
OK! The update! Application works like this: It first save data to table in PK column, Col1, Col2, Col3 values 1, null, text, date . After that it checks if Col1 is null and reads the PK column and writes it's values to Col1 . So I get the 1, 1, text, data .
This could do what you asked (I know: it's after UPDATE, so actually, you'll change values two times, but I will not use AFTER/INSTEAD: what if other values should be updated?).
CREATE TABLE TES1 (ID INT, COL1 VARCHAR(10));
INSERT INTO TES1 VALUES (1,'X');
INSERT INTO TES1 VALUES (2,NULL);
CREATE TRIGGER TRG1 ON TES1
AFTER UPDATE
AS
BEGIN
UPDATE A SET COL1=CASE WHEN d.COL1 IS NULL THEN i.COL1 ELSE d.COL1 END
FROM TES1 A
INNER JOIN DELETED d ON A.ID = d.ID
INNER JOIN INSERTED i ON A.ID = i.ID;
END
Sample data
UPDATE TES1 SET COL1 = NULL WHERE ID=1;
SELECT * FROM TES1;
UPDATE TES1 SET COL1 = 'A' WHERE ID=1;
SELECT * FROM TES1;
UPDATE TES1 SET COL1 = 'B' WHERE ID=2;
SELECT * FROM TES1;
UPDATE TES1 SET COL1 = 'C' WHERE ID=2;
SELECT * FROM TES1;
You can create a CHECK constraint that will work only for new values.
ALTER TABLE [dbo].[Test] WITH NOCHECK ADD CONSTRAINT [CK_Test] CHECK (([Col1] IS NOT NULL))
GO
ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [CK_Test]
GO
WITH NOCHECK option means that constraint will be created successfully even if the table has NULL values.
But, after this constraint is created, an attempt to insert new NULL value or update existing value to NULL will fail.
上一篇: 如何将空值更新为零？