2

I want to create a trigger in SQL Server 2012 that activates when I insert a row into a specific table.

The trigger must insert a value in a specific column, in the same table and row I am inserting the new row into.

The trigger must only activate if a specific column in the row I am inserting does not have a value ie. null.

Let's say I am inserting into:

  • Table: car
  • Columns: car_make | car_price | car_img

If I provide all values in the insert:

car_make | car_price | car_img

the trigger must not activate

But, if I only insert values for 2 columns:

car_make | car_price

the car_img value is null, then trigger must activate and do the following:

  • Insert a predefined value in the column car_img in the same affected row

PS. I have to use a trigger, it's for an assignment. I cannot use a default value.

Improve this question
4
  • 1
    You need to set a default values for all the columns in your table not a trigger for this simple requirement. Commented Dec 1, 2013 at 20:46
  • @MuhammedAli I have to use a trigger, is mandatory. It's for an assignment. Commented Dec 1, 2013 at 20:47
  • 1
    Trigger for such a simple requirement is really an over kill but anyway see my answer. Commented Dec 1, 2013 at 20:56
  • 2
    Just a side-note: if you have an FOR INSERT trigger, it will fire always - you cannot selectively let it fire (or not) depending on your data. What you can do is check for these conditions in the trigger code and handle the situation as needed Commented Dec 1, 2013 at 21:09

1 Answer 1

Reset to default
1 
Create Trigger tr_ForInserts_car
ON dbo.car
FOR INSERT
AS
BEGIN 
  SET NOCOUNT ON;


    UPDATE Car
     SET car_make = /*Your Default Value*/
     WHERE car_make IS NULL

     UPDATE Car
     SET car_price = /*Your Default Value*/
     WHERE car_price IS NULL

     UPDATE Car
     SET car_img = /*Your Default Value*/
     WHERE car_img IS NULL
END

Edit

My previous suggestion will update all the rows where corresponding column is null, to update only the column for newly insert rows try the following please

Create Trigger tr_ForInserts_car
ON dbo.car
FOR INSERT
AS
BEGIN 
  SET NOCOUNT ON;


     UPDATE Car
     SET Car.car_make = /*Your Default Value*/
     FROM Car INNER JOIN inserted
     ON Car.PrimaryKey = inserted.PrimaryKey
     WHERE Car.car_make IS NULL

     UPDATE Car
     SET Car.car_price = /*Your Default Value*/
     FROM Car INNER JOIN inserted
     ON Car.PrimaryKey = inserted.PrimaryKey
     WHERE Car.car_price IS NULL

     UPDATE Car
     SET Car.car_img = /*Your Default Value*/
     FROM Car INNER JOIN inserted
     ON Car.PrimaryKey = inserted.PrimaryKey
     WHERE Car.car_img IS NULL

END
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

But that'll update the whole table - not just the row (or rows) that are being inserted ... you would probably want to join to the Inserted pseudo table in some way to restrict your update to just those rows being actually inserted right now ....
@marc_s yes this is right but this is what OP wants, strange requirement but what can you say :)
@MuhammedAli I know it's a bit weird, and it doesn't make any sense, but that's the requirement in the school assignment. They just want us to use a trigger for the sake of using a trigger.
@marc_s have a look now I have update my answer only to deal with newly inserted rows.
@MuhammedAli Well, i can't do it myself if I can't learn it somewhere. Stackoverflow gives me more reliable and screened answers, which is much better than the random stuff you find on the internet. Stackoverflow has community of experts constantly checking if the information is correct, :-) so i guess the quality of the information is higher than searching on the internets.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.