SQL trigger, check if certain value was entered

Question

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Now what to check:

If inserted value1 = null, change it to 0

How to do it via trigger? I googled for examples and I have never ever done a trigger, so it is rather confusing.

So far got only this:

CREATE TRIGGER testTrigger
ON myTable
AFTER INSERT, UPDATE, DELETE

score 14 · Accepted Answer · edited May 23 '17 at 12:01

14

You can add default value . This is how it's done for a new column. For existing one you should add constraint. Check Update 2

ALTER TABLE table_name 
ADD column1 int NOT NULL DEFAULT(0)

UPDATE:

To set default value, you should update NULL values at first.

UPDATE table_name 
SET column1 = 0
WHERE column1 IS NULL

UPDATE 2:

Try adding constraint

ALTER TABLE table_name 
ADD CONSTRAINT DF_column1 DEFAULT 0 FOR column1

edited May 23 '17 at 12:01

Community

answered Sep 06 '12 at 11:38

hgulyan

This adds column, can I change to columns default value? – Jaanus Sep 06 '12 at 11:42
You should update column1 values to 0 after that alter column. Check update. – hgulyan Sep 06 '12 at 11:45
`alter table myTable alter column myColumn varchar(40) not null default('0')`, gives INCORRECT SYNTAX NEAR THE KEYWORD 'DEFAULT' – Jaanus Sep 06 '12 at 12:03

Joe G Joseph · Answer 2 · 2012-09-06T11:44:45.193

12

You could write this in the trigger:

UPDATE T SET value1 =0
FROM   table_name T
JOIN   INSERTED I
ON     T.<id>=I.<id>
WHERE  I.value1 is null

INSERTED table which is accessible only within trigger will store the values that have inserted..

edited Sep 06 '12 at 11:44

answered Sep 06 '12 at 11:39

Joe G Joseph

Buzz · Answer 3 · 2012-09-07T03:48:37.983

2

use ISNULL on the INSERTED value

 SELECT ISNULL(INSERTED,0) FROM INSERTED

edited Sep 07 '12 at 03:48

answered Sep 06 '12 at 11:42

Buzz

3 Answers3