forum

home / developersection / forums / sql trigger, check if certain value was entered

Ask Question

SQL trigger, check if certain value was entered

Anonymous User 2799 17-Apr-2013
Hi Expert!

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

Advance Thanks! 
mssql server mssql server 
Updated on 17-Apr-2013
Anonymous User

Other

I am a content writter !

Message
Can you answer this question?
Answer

2 Answers

AVADHESH PATEL

AVADHESH PATEL

17-Apr-2013
Hi Jacob!

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)
Add column, with default value, to existing table in SQL Server

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 
AVADHESH PATEL

AVADHESH PATEL

17-Apr-2013
Hi Jacob!

You can try another way as following

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..

advertising
advertising

Liked By

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy