SQL TRIGGER, CHECK IF CERTAIN VALUE WAS ENTERED

jacob rasel

Total Post:88

Points:616
Posted by  jacob rasel
 1695  View(s)
Ratings:
Rate this:
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! 
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: SQL trigger, check if certain value was entered

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

      Modified On Apr-17-2013 09:20:19 AM
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: SQL trigger, check if certain value was entered

    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 

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!